Lining up data with two colums of unequal length

[tdr0007]

Hello,

I have attached an excel file for clarity. Here is what I am trying to do:

I have serial numbers in columns C and E (keep in mind this is a sample sheet and actual data is over 600 entries). I want to match up the serial numbers in column C with column E. Also, when I do this I want the corresponding model/color info from column F and G to stay with the serial number.

I don't know if this matters but column E is longer than column C and therefore some numbers will not have a match. I don't know how this will affect things. There are no duplicate serial numbers.

I have provided a sample sheet to show what I am trying to do. Any help is needed! I have been trying to figure this out for a while so I am seeking help!

Thanks!

[JBeaucaire]

1) Remove the two rows so the headers are in row1, data starts in row2

2) Remove the second table down below so only the wanted data is there.

3) Over in H2 put this formula:

=IFERROR(INDEX(E:E, MATCH($C2,$E:$E, 0)), "")

......and copy that cell down and across through J7.


Now you have the wanted results on the right, you can copy them and PASTE SPECIAL > VALUES over the original columns D:F.

[tdr0007]

that worked. However, the data on the right that didn't match up has been deleted. How do I fix this? I want all the data to remain
even if there is not a match

[JBeaucaire]

Your example didn't show that. In that case I would not recommend copying the results over the original.

[tdr0007]

Ok. Does anyone have any suggestions?

Again, the column of serial numbers on the left is longer than the column on the right by about 25 numbers. The numbers that don't
match up, I still want them in the worksheet. Maybe just at the end?? I don't care where they are so much, but they must still be in
the data set.

The file I submitted was just an example- real data contains 600+ entries

[JBeaucaire]

My apologies, I am simply going by the exact representation from your second "What I want" table which does not show unmatched items. From a formula standpoint the solution given provides that.

So, a longer manual method:

1) Insert these formulas

H2: =IF(ISNUMBER(MATCH(E2,C:C, 0)), H1, H1+1) ...copied down
I2: =IFERROR(INDEX(E:E, MATCH($C2,$E:$E, 0)), "")...copied down and across through column K

M1: =MAX(H:H)
M2: =IF(ROW(A1)>$M$1, "", INDEX(E:E, MATCH(ROW(A1),$H:$H, 0))) ...copied across through O2, then copied DOWN until blanks appear


2) Copy M:O and PasteSpecial>Values over itself to remove the formulas and keep the results

3) Copy I2:K?? and PastSpecial>Values over the original values in E:G

4) Copy the Extra items listed in O:P and paste at the bottom of E:G

5) Clear the helper columns.

[tdr0007]

That worked!!

Thank you!