Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change Data from column b into row based on value in column a
I have a list in excel with suppliers in column A (over 1000 rows) and
details about that supplier in column B. however, for some of the suppliers there are multiple lines because there are multiple details. How do I change the multiple rows into one row per supplier with the details spread out from Column B on? Ex. Current Setup Supplier Detail A 1 A 2 A 3 Needed Setup Supplier Detail A 1 2 3 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change Data from column b into row based on value in column a
Assume current set up is in Sheet1 cols A and B, data from row2 down
In Sheet1, List the suppliers in D1 across, eg; A, B, C, etc Place this in D2: =IF($A2="","",IF($A2=D$1,ROW(),"")) Copy D2 across/fill down to cover the extent of source data Then in another sheet, Put in A2: =IF(INDEX(Sheet1!$D$1:$IV$1,ROWS($1:1))=0,"",INDEX (Sheet1!$D$1:$IV$1,ROWS($1:1))) Copy A2 down as far as required to "transpose" list the suppliers from Sheet1's D1:IV1 Put in B2: =IF(COLUMNS($A:A)COUNT(OFFSET(Sheet1!$C:$C,,MATCH ($A2,Sheet1!$D$1:$IV$1,0))),"",INDEX(Sheet1!$B:$B, SMALL(OFFSET(Sheet1!$C:$C,,MATCH($A2,Sheet1!$D$1:$ IV$1,0)),COLUMNS($A:A)))) Copy B2 across as far as required, then fill down to the extent done in col A. This will return the exact results that you seek. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "CmSant2" wrote: I have a list in excel with suppliers in column A (over 1000 rows) and details about that supplier in column B. however, for some of the suppliers there are multiple lines because there are multiple details. How do I change the multiple rows into one row per supplier with the details spread out from Column B on? Ex. Current Setup Supplier Detail A 1 A 2 A 3 Needed Setup Supplier Detail A 1 2 3 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
making a new column based on data in an existing column | Excel Discussion (Misc queries) | |||
Need to write function that will change column width based on a condition | Excel Discussion (Misc queries) | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
How do I change column data based on dynamic (rolling) dates? | Excel Discussion (Misc queries) | |||
Resetting values based on change in cells of other column | Excel Discussion (Misc queries) |