Transposing data?
 

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.

Transposing data?
 

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(FR EQUENCY(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 ,
Sarah (OGI) wrote:

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.

Transposing data?
 

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
"Domenic" wrote:

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(FR EQUENCY(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 ,
Sarah (OGI) wrote:

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.

Transposing data?
 

Try this:
Data PivotTable and PivotChart Report...

Regards,
Ryan---


--
RyGuy


"Sarah (OGI)" wrote:

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.