change Data from column b into row based on value in column a

  • Thread starter Thread starter CmSant2
  • Start date Start date
C

CmSant2

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
 
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.
 
Back
Top