CODEWITHSUNDEEP
excelvba
user3555362
user3555362

Reputation: 49

Fill column B based on column A and B pairing

Each report in column A has a corresponding location in column B

Column B has locations to many of the reports, but not all.

I need to fill in locations in column B where blank.

    A    B     ->    A    B
    1    a           1    a
    1                1    a
    2    b           2    b
    2    b           2    b
    2                2    b
    3                3    
    3                3

I've tried =IF(ISERROR(MATCH(A1,$A:$B,0)),"",B1), but I'm definitely missing something. I also have 35000 rows, looking to process it all at once.

Upvotes: 0

Views: 386

Answers (2)

gtwebb
gtwebb

Reputation: 3011

One way you could do it is to use an index/match function. This needs to be entered using Ctrl+Shift+Enter

=INDEX($B$1:$B$7,MATCH(A1,IF($B$1:$B$7="","",$A$1:$A$7),0))

What match does is return the index number of the cell, so if it was found in the 3rd cell of a range it would return 3 not the cell value.

Inside the match we have an if statement that puts a blank in the array if the cell in B is blank and the value from cell A if its not.

We then match the value in cell A1 to this array which will return a filled column B with a matching column A if it exists.

The index allows you to pick a cell in a range based on the cell index (which match returns).

This formula is in cell C1 with the data in A1:B7.

Upvotes: 1

Pieter Geerkens
Pieter Geerkens

Reputation: 11883

It sounds like you are just doing it once, so no programming is necessary.

If so, first sort your data on Column_A,Column_B. Then off to the right, create a calculated column from Column B that overwirites any blank with the value form the preceding row exactly when the two rows have the same Column_A value.

Then Copy the column and Paste Special -> Values over top of Column_B.

Upvotes: 0

Related Questions