Match two columns when one column has fewer entries than the other in Excel

0
votes

I have a column, Column1, of block id numbers.

I have another column, Column2, of block id numbers.

The two columns don't completely match. There are fewer rows in Column2 than there are in Column1. Some numbers in Column2 aren't in Column1 and vice versa.

I want to match these columns. There are also other columns which correspond with Column1 that I want to "follow" Column1 when it matches to Column2. After the matching, I expect a lot of values from Column1 to be unmatched.

How can I match them this way? I want Column1 and Column2 to match and the other columns associated with Column1 to follow it when it matches to Column2.

Edit: For Example:

This is what I have right now:

Column1....Column2....Column3....Column4

......4................4...............B................Pie

......5................7...............C................Cake

......6................8...............Z................Cookie

......8................11.............G................Marsh

......11...............................D................Sky

......12...............................E................Le

This is what I want:

Column1....Column2....Column3....Column4

......4................4...............B................Pie

......5.................................C................Cake

......6.................................Z................Cookie

......8................8...............G................Marsh

......11..............11.............D................Sky

......12...............................E................Le

........................7..................................

So Column1 matches Column2, Column3 and Column4 "stay" with Column1, and any mismatches are given their own row or something. Note that Column2 is the only one that "moves". A simple sort will not do because Column2 has fewer entries than Column1 and some numbers in Column2 are not in Column1.

Does that help? Sorry for any confusion. Thank you for any help.

1
excelexcel-2010
Maybe clarify "match?" Possible by showing some sample data. Also, if you have Access or similar available you can likely do it easier there then native Excel. – Karl Kieninger
I made an edit to the original post. Thank you for pointing out the confusion. I hope this helps. – user3072998
Okay much clearer what you are asking. What's the scale? 100 rows or a 100k? Is this a one time thing or needing to be done frequently? – Karl Kieninger
A one-time task. Column1 has 3k rows. Column2 has 1.7k rows. Is there any hope for me? Someone suggested I use a pivot table but I don't know how I could use a pivot table for this task. – user3072998

1 Answers

0
votes

Here's a process. Not automated, but also not particularly complicated.

  1. Insert a new column after Column 2--we'll call it ColumnC. Place this formula in the column:

    =IFERROR(VLOOKUP(A1,B:B,1,FALSE),"")

    This will duplciate Column1 for any row that has a match in Column2.

  2. Insert another new column after ColumnC--we'll call it ColumnD. PLace this formula in the column:

    =IF(IFERROR(VLOOKUP(B1,A:A,1,FALSE),"")="",IF(B1="","",B1),"")

    This will duplicate Column2 for any row that has no match in Column1.

  3. Select all the cells in Column C and D. Copy them. Special paste the values back into the same cells.

  4. Select all the cells in ColumnD, copy them to clipboard, and paste them into ColumnC below all existing rows.
  5. Delete ColumnD
  6. Delete Column2 and rename ColumnC to Column1 (if you actually care about the column headings).
  7. Delete all blank rows.

Voila!

I am sure an Excel guru could do that better, but I think this is very straightforward for a 1 time or manually repeatable process.