Hello

I have a spreadsheet with two columns - CLUSTER and ID NUMBER

Each cluster may have four or five product IDs associated with it...

CLUSTER PROD_ID
GL01 6867
GL01 6931
GL01 6961
GL01 6962
GL01 6966
GL01 6967
GL02 6012
GL02 6015
GL02 6025
GL03 5063
GL03 5064
GL03 5099
GL03 5123
GL04 3092
GL04 5059
GL04 5068
GL04 5081
GL04 5082
GL04 5150
GL04 5301
GL04 5358
GL04 5372
GL04 5420
GL04 5421


Is there are way to present this information, so that the column A has the
cluster number and the following columns have the product IDs for that
cluster:

CLUSTER PROD_1 PROD_2 PROD_3 PROD_4 PROD_5 PROD_6
GL01 6867 6931 6961 6962 6966 6967
GL02 6012 6015 6025
GL03 5063 5064 5099 5123


Thanks

Ben

Hi Ben

Assuming Cluster in A1:A25 and Prod_Id in B1:B25, and the unique
clusters listed in C2:C5 and you want to fill the prods in D2:I5 then
you can use the following formula:

=INDEX($B$2:$B$25,LARGE(($A$2:$A$25=$C2)*(ROW($A$2 :$A$25))-ROW($A
$2)+1,COLUMNS($D$1:D$1)))

which must be array entered (Ctrl+Shift+Enter) as it is an array
formula. So that you don't get error values (eg for clusters without
Prod_6 for example then:

=IF(ISERROR(INDEX($B$2:$B$25,LARGE(($A$2:$A$25=$C2 )*(ROW($A$2:$A$25))-
ROW($A$2)+1,COLUMNS($D$1:D$1)))),"",INDEX($B$2:$B$ 25,LARGE(($A$2:$A$25=
$C2)*(ROW($A$2:$A$25))-ROW($A$2)+1,COLUMNS($D$1:D$1))))

Hope this helps!

Richard



On 29 Jan, 10:19, Ben wrote:
Hello

I have a spreadsheet with two columns - CLUSTER and ID NUMBER

Each cluster may have four or five product IDs associated with it...

CLUSTER PROD_ID
GL01 6867
GL01 6931
GL01 6961
GL01 6962
GL01 6966
GL01 6967
GL02 6012
GL02 6015
GL02 6025
GL03 5063
GL03 5064
GL03 5099
GL03 5123
GL04 3092
GL04 5059
GL04 5068
GL04 5081
GL04 5082
GL04 5150
GL04 5301
GL04 5358
GL04 5372
GL04 5420
GL04 5421

Is there are way to present this information, so that the column A has the
cluster number and the following columns have the product IDs for that
cluster:

CLUSTER PROD_1 PROD_2 PROD_3 PROD_4 PROD_5 PROD_6
GL01 6867 6931 6961 6962 6966 6967
GL02 6012 6015 6025
GL03 5063 5064 5099 5123

Thanks

Ben

That works a treat - thanks!!!

Ben

"RichardSchollar" wrote:

Hi Ben

Assuming Cluster in A1:A25 and Prod_Id in B1:B25, and the unique
clusters listed in C2:C5 and you want to fill the prods in D2:I5 then
you can use the following formula:

=INDEX($B$2:$B$25,LARGE(($A$2:$A$25=$C2)*(ROW($A$2 :$A$25))-ROW($A
$2)+1,COLUMNS($D$1:D$1)))

which must be array entered (Ctrl+Shift+Enter) as it is an array
formula. So that you don't get error values (eg for clusters without
Prod_6 for example then:

=IF(ISERROR(INDEX($B$2:$B$25,LARGE(($A$2:$A$25=$C2 )*(ROW($A$2:$A$25))-
ROW($A$2)+1,COLUMNS($D$1:D$1)))),"",INDEX($B$2:$B$ 25,LARGE(($A$2:$A$25=
$C2)*(ROW($A$2:$A$25))-ROW($A$2)+1,COLUMNS($D$1:D$1))))

Hope this helps!

Richard



On 29 Jan, 10:19, Ben wrote:
Hello

I have a spreadsheet with two columns - CLUSTER and ID NUMBER

Each cluster may have four or five product IDs associated with it...

CLUSTER PROD_ID
GL01 6867
GL01 6931
GL01 6961
GL01 6962
GL01 6966
GL01 6967
GL02 6012
GL02 6015
GL02 6025
GL03 5063
GL03 5064
GL03 5099
GL03 5123
GL04 3092
GL04 5059
GL04 5068
GL04 5081
GL04 5082
GL04 5150
GL04 5301
GL04 5358
GL04 5372
GL04 5420
GL04 5421

Is there are way to present this information, so that the column A has the
cluster number and the following columns have the product IDs for that
cluster:

CLUSTER PROD_1 PROD_2 PROD_3 PROD_4 PROD_5 PROD_6
GL01 6867 6931 6961 6962 6966 6967
GL02 6012 6015 6025
GL03 5063 5064 5099 5123

Thanks

Ben