Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Wil
 
Posts: n/a
Default 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   Report Post  
Biff
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula to compare multiple rows values based on another column? Murph Excel Worksheet Functions 4 February 21st 05 03:44 AM
transpose a column into many rows GMed Excel Discussion (Misc queries) 1 January 21st 05 08:15 PM
repeated transpose from rows to columns with unequal groups kraymond Excel Discussion (Misc queries) 3 December 20th 04 03:39 PM
Formula to list unique values JaneC Excel Worksheet Functions 4 December 10th 04 01:25 AM
Identifying exact values in alternate rows YG Excel Worksheet Functions 1 November 3rd 04 12:25 AM


All times are GMT +1. The time now is 03:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"