Remember Me?

#1
May 22nd 05, 04:15 AM
 Wil Posts: n/a
Transpose unique values in one column/mult. rows into a single row

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

#2
May 22nd 05, 08:52 AM
 Biff Posts: n/a

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

"Wil" wrote in message
...
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Murph Excel Worksheet Functions 4 February 21st 05 02:44 AM GMed Excel Discussion (Misc queries) 1 January 21st 05 07:15 PM kraymond Excel Discussion (Misc queries) 3 December 20th 04 02:39 PM JaneC Excel Worksheet Functions 4 December 10th 04 12:25 AM YG Excel Worksheet Functions 1 November 2nd 04 11:25 PM

All times are GMT +1. The time now is 12:06 PM.