2

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!

Community
  • 1
  • 1
user2426353
  • 35
  • 1
  • 1
  • 4

1 Answers1

9

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.

user3514930
  • 1,721
  • 1
  • 9
  • 7
  • This worked perfectly - exactly what I needed - thank you very much! (though I had to swap the semi colon's for commas in the formulas for my Excel 2007 worksheet) – DarrenNavitas Feb 12 '15 at 17:17
  • for excel beginners like me... IFERROR is just a way of supressing #N/A where no value is found. =IFERROR(MATCH(A2,$B$2:$B$8,0),"") -ie. search for the value in A2 in the list B2 to B8, 0 is an exact match. Column E Formula for column E =IF(D2<>"",INDEX($B$2:$C$8,D2,1),"") This translates to give me the nth item in the list in column B (skip blank entries). D2,1 search for D2 in column 1 i.e column B. Column F -- same except search in second column to return text column aaaa etc – JohnC Mar 17 '18 at 18:36