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

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