Changing data orientation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Neel - 03 Jan 2008 22:24 GMT

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 to this Message

Max - 04 Jan 2008 03:38 GMT

Maybe using an array-entered dual criteria index/match would suffice

A sample to illustrate:
http://www.freefilehosting.net/download/3a1ee
Dual criteria index n match.xls

Source data assumed in Sheet1, cols A to D as posted
data in row2 down

In Sheet2,
with the unique SKUs listed in B2 down,
& the unique attribute metrics listed in C1 across
(you could use advanced filter > uniques to get the uniques separately)

Array-enter (press CTRL+SHIFT+ENTER) in C2:
=IF(ISNA(MATCH(1,(Sheet1!$B$2:$B$100=$B2)*(Sheet1!$C$2:$C$100=C$1),0)),"",
INDEX(Sheet1!$D$2:$D$100,MATCH(1,(Sheet1!$B$2:$B$100=$B2)*(Sheet1!$C$2:$C$100=C$1),0)))
Copy C2 across/fill down to populate the attribute values. Adapt the ranges
to suit.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

>I have a worksheet with 4 columns.
> Column A = Product Family
[quoted text clipped - 27 lines]
> I hope this explains the issue I'm facing. And, as always, any help is
> appreciated.

Reply to this Message