PC Review


Reply
Thread Tools Rate Thread

Advanced transposing in Excel

 
 
Neel
Guest
Posts: n/a
 
      3rd Jan 2008
I have a worksheet with 4 columns.
Column A = Product Family
Column B = SKU's
Column C = Attribute Metric
Column D = Attribute Value

Column B might have a SKU going down for 40 consecutive rows with 40
different attribute metrics e.g. manufacturer, height, width, depth,
UPC, color, finish etc. etc.
and the actual values for it in column D like Keter, 12", 36", 15",
0123456789, black, steel etc etc..
Not all SKU's have 40 metrics. Some might have just 5 to 10, while
others might have 55 to 60.

What I'm trying to do is have one row per SKU. The actual names for
the metrics will be the subsequent columns headings.

Column A = Prod Family
Column B = SKU
Column C = Manufacturer
Column D = Color
.... and so on and so forth

For each SKU the information will be tabulated by columns.

A pivot table would have been an excellent solution for my data
orientation problem but the pivot table will not allow any text
information in the data area.

I hope this explains the issue I'm facing. And, as always, any help is
appreciated.
 
Reply With Quote
 
 
 
 
Max
Guest
Posts: n/a
 
      4th Jan 2008
I've given a crack at this in your other post in .public.excel
Pl refrain from multi-posting
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


 
Reply With Quote
 
Joel
Guest
Posts: n/a
 
      4th Jan 2008
Try this code

Sub combinerows()

'if there is a header row delete 3 lines below
Rows(1).Insert
Range("A1") = "Product Family"
Range("B1") = "SKU"

RowCount = 2
NewCol = 3 ' column C
Do While Range("A" & RowCount) <> ""
AttrMetric = Range("C" & RowCount)
Range("C" & RowCount) = ""
AttrValue = Range("D" & RowCount)
Range("D" & RowCount) = ""
Call InsertData(AttrMetric, AttrValue, _
RowCount, NewCol)
Do While Range("A" & RowCount) = _
Range("A" & (RowCount + 1)) And _
Range("B" & RowCount) = _
Range("B" & (RowCount + 1))

AttrMetric = Range("C" & (RowCount + 1))
AttrValue = Range("D" & (RowCount + 1))
Rows(RowCount + 1).Delete
Call InsertData(AttrMetric, AttrValue, _
RowCount, NewCol)
Loop
RowCount = RowCount + 1
Loop

End Sub
Sub InsertData(ByVal AttrMetric, ByVal AttrValue, _
ByVal RowCount, ByRef NewCol)

Set c = Rows(1).Find(what:=AttrMetric, _
LookIn:=xlValues)
If c Is Nothing Then
Cells(1, NewCol) = AttrMetric
Cells(RowCount, NewCol) = AttrValue
NewCol = NewCol + 1
Else
Cells(RowCount, c.Column) = AttrValue
End If
End Sub


"Neel" wrote:

> I have a worksheet with 4 columns.
> Column A = Product Family
> Column B = SKU's
> Column C = Attribute Metric
> Column D = Attribute Value
>
> Column B might have a SKU going down for 40 consecutive rows with 40
> different attribute metrics e.g. manufacturer, height, width, depth,
> UPC, color, finish etc. etc.
> and the actual values for it in column D like Keter, 12", 36", 15",
> 0123456789, black, steel etc etc..
> Not all SKU's have 40 metrics. Some might have just 5 to 10, while
> others might have 55 to 60.
>
> What I'm trying to do is have one row per SKU. The actual names for
> the metrics will be the subsequent columns headings.
>
> Column A = Prod Family
> Column B = SKU
> Column C = Manufacturer
> Column D = Color
> .... and so on and so forth
>
> For each SKU the information will be tabulated by columns.
>
> A pivot table would have been an excellent solution for my data
> orientation problem but the pivot table will not allow any text
> information in the data area.
>
> I hope this explains the issue I'm facing. And, as always, any help is
> appreciated.
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Transposing Excel Formula Steveal Microsoft Excel Discussion 2 7th Feb 2011 08:48 PM
Transposing and importing from Excel MarianneZ Microsoft Access 4 11th May 2009 04:46 PM
Re: Transposing and importing from Excel ΠάΛ« Microsoft Access 0 6th May 2009 02:09 AM
Transposing excel column in to row Y2A Microsoft Excel Discussion 1 25th Aug 2008 02:30 PM
Advanced Transposing? =?Utf-8?B?U0hleGNlbHVzZXI=?= Microsoft Excel Worksheet Functions 0 12th Oct 2005 03:55 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 05:48 AM.