Transpose and Index

[jpsocal]

I have a table with thousands of base items and each one comes in multiple colors. I need to have the vertical list turned into horizontal layout and ordered by base item followed by the colors - one in each column. Tried it with match, index, hlookup and mixtures of it, but could not get all the variables out of it to have the wished result as here bellow.

Original Data		-->	Wished Result with the colours in columns and the base porduct in new rows
Product			Product	Color 1	Color 2	Color 3
123456	black		123456	black	white	orange
123456	white		987654	black	green
123456	orange		147258	white	black
987654	black
987654	green
147258	white
147258	black

<tbody>
</tbody>

Thank you for your help!

 

[J.Ty.]

Hi,

A few questions at the beginning:

1) How many colors can there be for a single product?
2) Is the initial data sorted by product number?
3) As far as I can see, you do not insist that the same color is always in the same column - please confirm

Best,

J.Ty.

 

[jpsocal]

Hi J.Ty.,

Thank you for your answer.

To your question there can be up to 8 colors for each base products in my list.
The initial data is sorted by base product and then in each row following a color if available, plase see here an example spreeadsheet: https://docs.google.com/spreadsheet/ccc?key=0Apc01y9ZibTDdGpLZUY5cnF4SXBZeXo5R2JaUFFzLWc#gid=0
No the colors do not need to be in the same column in the wished result. I only need to know for each base products what colors are vailable and this in one row.

Cheers

 

[J.Ty.]

Here is the manual of use of the solution below:

0) Copy and paste your initial data below the "Product" header
1) Mark this column and choose Data->Remove duplicates from the ribbon (Excel will ask if you want to extend this range - keep it unchanged).
2) Mark cell E13 and fill to the right creating as many additional columns as necessary - as per your information 8 should suffice.
3) Mark the cells from the first row below the headers and fill down for the whole length of data in column D.
4) You may want to copy those cells with information about colors and paste "as values" in the same location (this removes the formulas, keeping the data).

J.Ty.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Original Data			Wished Result with the colours in columns
2
3	Product	Colours	-->	Product	Color 1	Color 2	Color 3	Color 4	Color 5	Color 6	Color 7
4	123456	black		123456	black	white	orange
5	123456	white		987654	green flash	white
6	123456	orange		147258	white	grey
7	987654	black		369852	blue	green flash	atomic orange	red	black	white	rose
8	987654	green flash
9	147258	white
10	147258	grey		Computed result
11	369852	blue
12	369852	green flash		Product	Color 1	Color 2	Color 3	Color 4	Color 5	Color 6	Color 7
13	369852	atomic orange		123456	black
14	369852	red		987654
15	369852	black		147258
16	369852	white		369852
17	369852	rose
Sheet1

Cell Formulas
Range	Formula
E13	=IFERROR(IF(INDEX($A$4:$A$17,MATCH($D13,$A$4:$A$17,0)+COLUMNS($D13:D13)-1)=$D13,INDEX($B$4:$B$17,MATCH($D13,$A$4:$A$17,0)+COLUMNS($D13:D13)-1),""),"")

 

[ramananhrm]

This is an alternative.

=IFERROR(INDEX($B$2:$B$8,SMALL(IF($A$2:$A$8=$C2,ROW($A$2:$A$8)-ROW($A$2)+1),COLUMNS($D2:D2))),"")

 

[jpsocal]

Thank you very much J.Ty. your formula works perfec for my problem!

@Ramanan, I also tried yours but could not get it to work. Thank you though too for your help.

Cheers,
Jan

 

[J.Ty.]

You are welcome!

J.Ty.