Transpose unique values in one column/mult. rows into a single row

[Wil]

What is the best way to accomplish this using Excel functions? I have a file
with records that have a four different attributes under one column. For each
product, there are four rows, all the same, with the exception of the
attribute (color, length, width, height). Here is what the fiel looks like
today

Part Number; Part Name; Attribute
101; Part 101; color black
101; Part 101; length 6 inches
101; Part 101; width 1 inch
101; Part 101; height 4 inches
102; Part 102; color white
102; Part 102; length 10 inches
102; Part 102; width 3 inch
102; Part 102; height 6 inches


and so on. How I want to format the data in Excel is like this:

Part Number; Part Name; Color Attribute; Length Attribute; Width Attribute;
Height Attribute
101; Part 101; color black; 6 inches; 1 inch; 4 inches;
102; Part 102; color white; 10 inches; 3 inches; 6 inches

....so that I can see al the unique attributes for that part instead of
reviewing it in a single column. There is one caveat - some of the rows do
not have all 4 attributes.

Is there a way for a function to check the part number and return the
attributes that are currently in a single column; into a single row in
separate cells/columns (preferable on a separate worksheet)?

Any advice will be greatly appreciated. Wil

 

[Biff]

Hi!

Here's one way:

Use an advanced filter and copy the unique part numbers to a different
sheet, say, Sheet2. Copy those to column A starting in A2.

In sheet2 enter these headers in A1:F1 - Part Number, Part Name, Color,
Length, Width, Height

In A2:An you will have the unique part numbers listed.

Enter this formula in B2:

=INDEX(Sheet1!B$2:B$9,MATCH(LEFT(B$1,FIND(" ",B$1))&$A2,Sheet1!B$2:B$9,0))

Enter this formula in C2 with the key combo of CTRL,SHIFT,ENTER and copy
across to F2:

=INDEX(Sheet1!$C$2:$C$9,MATCH(1,(Sheet1!$A$2:$A$9=$A2)*(LEFT(Sheet1!$C$2:$C$9,FIND("
",Sheet1!$C$2:$C$9)-1)=C$1),0))

Select the range C2:F2 and copy down as needed.

Biff