Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
transposing vertical data to horizontal with varying amount of data | Excel Discussion (Misc queries) | |||
Transposing data | New Users to Excel | |||
Transposing data | Excel Discussion (Misc queries) | |||
Transposing Data | Excel Worksheet Functions | |||
Transposing data | New Users to Excel |