CODEWITHSUNDEEP
excel
user2426353
user2426353

Reputation: 35

Comparing two columns in excel, inserting blank rows moving associated data

I have a large set of data in excel that needs to be matched by column. Specifically, the data currently looks like:

Column 1 Column 2 Column 3
1          1         aaaa
2          3         bbbb
3          4         cccc
4

Ideally, I want the data to look like:

Column 1 Column 2 Column 3
1         1        aaaa
2
3         3        bbbb
4         4        cccc

I've looked at different ways of comparing Column 1 and Column 2 (similar to this), but haven't found a good way to a) insert the blank space where column 1 and 2 don't match and b) also attach the data in column 3.

Any help would be greatly appreciated! Thanks!

Upvotes: 2

Views: 24693

Answers (1)

user3514930
user3514930

Reputation: 1717

If you want / can use directly Excel:

enter image description here

In the cells of Col "D" put this formula:

=IFERROR(MATCH(A2;$B$2:$B$8;);"")

In the cells of Col "E" put this formula:

=IF(D2<>"";INDEX($B$2:$C$8;D2;1);"")

In the cells of Col "F" put this formula:

=IF(D2<>"";INDEX($B$2:$C$8;D2;2);"")

Copy and paste for all the cells. The good think it's that it's autoupdated... Or when finish copy and paste with value.

Upvotes: 9

Related Questions