Matching Rows

[Blissfully Ignorant]

I have 6 columns of info. The rows in the first three columns are grouped
together and lead off with Column A being ITEM ID #s. The next 3 columns are
grouped together with different info except for the lead off column D being
ITEM ID #s also. Column D has half of the number of rows than cloumn A. I
would like to know how to make columns D,E and F line up in the same row as
columns A, B and C when columns A and D have matching ID #s.

Thank You For Your Help

 

[Lars-Åke Aspelin]

I have 6 columns of info. The rows in the first three columns are grouped
together and lead off with Column A being ITEM ID #s. The next 3 columns are
grouped together with different info except for the lead off column D being
ITEM ID #s also. Column D has half of the number of rows than cloumn A. I
would like to know how to make columns D,E and F line up in the same row as
columns A, B and C when columns A and D have matching ID #s.

Thank You For Your Help

Assuming that the ID's are unique, that is there are not more than one
row with the same ID in column A.

Assuming that all ID's in column D are present in column A.

Assuming that your table starts on row 1.

Try the following formula in cell G1

=IF(ISNA(VLOOKUP($A1,$D$1:$D$100,1,FALSE)),"",INDEX(D$1$100,MATCH($A1,$D$1:$D$100,0)))

Drag this formula to H1 and I1
The drag G1:I1 down as far as needed, i.e. to the last row with data
in column A.

The three columns G, H, and I should now contain the same data as
columns D, E, and F only aligned ID-wise to the data in columns A, B,
and C.

You can now copy columns G, H, and I and Paste Special (Values only)
onto columns D, E, and F and finally remove columns G, H, I

Hope this helps / Lars-Åke

 

[John C]

Without moving them manually, and without a macro, you could do the
following, assuming your data starts in row 3
G3: =IF(COUNTIF($D$3:$D$100,A3)=1,A3,"")
H3: =IF(G3="","",VLOOKUP(G3,$D$3:$F$100,2,FALSE))
I3: =IF(G3="","",VLOOKUP(G3,$D$3:$F$100,3,FALSE))

Then you have a choice of hiding columns D-E if you still want it there, or
if this was a 1 time conversion, highlight your data in columns G through I,
copy-->paste special-->value. Then delete columns D-E. (Note: If you do this,
cells that reference values in D through E will error).