Transposing data?

S

Sarah (OGI)

I have a list of 820 customer ID's in column A. Some of these are duplicates
though, because each customer can have up to four business categories, ME,
CL, PP, PB (which are shown in column B). For example:

Customer ID Policy Type
0001 ME
0001 CL
0002 CL
0002 PP
0002 PB
0003 ME
0003 CL

Is there any way of listing unique customer ID's and in the corresponding
cell(s) show the relevant business categories along the same row. I say
'cell(s)' because they can each go into separate cells along that row or be
grouped together in one cell. Results should look like the following:

Customer ID Policy Type
0001 ME CL
0002 CL PP PB
0003 ME CL

Thanks in advance.
 
D

Domenic

First, create a unique list of customer ID's by using the Advanced
Filter, and selecting 'Unique records only'. Alternatively, assuming
that A2:B8 contains the data, a unique list can be returned using the
following formulas...

D2:

=SUM(IF(FREQUENCY(IF(A2:A8<>"",MATCH("~"&A2:A8,A2:A8&"",0)),ROW(A2:A8)-RO
W(A2)+1),1))

....confirmed with CONTROL+SHIFT+ENTER

E2, copied down:

=IF(ROWS(E$2:E2)<=$D$2,INDEX($A$2:$A$8,SMALL(IF(FREQUENCY(IF($A$2:$A$8<>"
",MATCH("~"&$A$2:$A$8,$A$2:$A$8&"",0)),ROW($A$2:$A$8)-ROW($A$2)+1),ROW($A
$2:$A$8)-ROW($A$2)+1),ROWS(E$2:E2))),"")

....confirmed with CONTROL+SHIFT+ENTER

Then, the corresponding policy types for each customer ID can be
returned in separate cells by using the following formulas...

F2, copied down:

=IF(E2<>"",COUNTIF($A$2:$A$8,E2),"")

G2, copied across and down:

=IF(COLUMNS($G2:G2)<=$F2,INDEX($B$2:$B$8,SMALL(IF($A$2:$A$8=$E2,ROW($A$2:
$A$8)-ROW($A$2)+1),COLUMNS($G2:G2))),"")

....confirmed with CONTROL+SHIFT+ENTER

Hope this helps!

n article <[email protected]>,
 
T

Toppers

Assuming data is on Sheet1 and sorted by Customer ID

On Sheet2:

in A2: first customer number ("0001")

in B2:
=IF(COLUMN()-1<=COUNTIF(Sheet1!$A$2:$A$8,$A2),INDEX(Sheet1!$B$1:$B$8,MATCH($A2,Sheet1!$A$2:$A$8,0)+COLUMN()-1),"")

Copy across until you get blank

in A3:
=INDEX(Sheet1!$A$1:$A$8,MATCH($A2,Sheet1!$A$1:$A$8,0)+COUNTIF(Sheet1!$A$1:$A$8,$A2))

Copy B2 down to B3

Then copy A3 and B3 down

Change range of A to suit

in
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top