Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching Rows
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching Rows
On Fri, 25 Jul 2008 13:06:04 -0700, Blissfully Ignorant
wrote: 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)),"",INDE X(D$1:D$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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching Rows
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). -- John C "Blissfully Ignorant" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deduplicate rows matching three columns | Excel Worksheet Functions | |||
Find rows matching Max value | Excel Discussion (Misc queries) | |||
Sum Rows with a matching date | Excel Discussion (Misc queries) | |||
Matching table rows | Excel Discussion (Misc queries) | |||
delete all matching rows | Excel Discussion (Misc queries) |