Pivot table?

[Emm]

Hi all,

I'm new to Pivot Tables ... and not sure if this is what I should be trying to use.

To keep it simple, I have a List with two columns.
1. CAT
2. ITEM

CAT has Hardware, Timber, Paint, Glazing etc...
ITEM has many items using those Categories.

How can I Pivot the list around so my CAT become Table headers,
and ITEMS become the records under each CAT?

I'm currently using VBA to extract unique CAT items, paste them as headers, then filter the list on each CAT value, copy and paste under each CAT column.

It is VERY slow, and there must be a better way...

Thanks,

Keith

 

[istiasztalos]

Hi Emm,

Can you please show a sample of the the output you are expecting?

is it like this?

[TABLE="class: grid, width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]CAT1[/TD]
[TD="width: 64"]CAT2[/TD]
[TD="width: 64"]CAT3[/TD]
[/TR]
[TR]
[TD]ITEM1[/TD]
[TD]ITEM5[/TD]
[TD]ITEM9[/TD]
[/TR]
[TR]
[TD]ITEM2[/TD]
[TD]ITEM6[/TD]
[TD]ITEM10[/TD]
[/TR]
[TR]
[TD]ITEM3[/TD]
[TD]ITEM7[/TD]
[TD]ITEM11[/TD]
[/TR]
[TR]
[TD]ITEM4[/TD]
[TD]ITEM8[/TD]
[TD]ITEM12[/TD]
[/TR]
</tbody>[/TABLE]

If so, I believe you cannot do this with a pivot table

What you are doing with VBA and manually filtering you could do with formulas, you can have formulas in a row to always show the set of unique items from a dataset, and below that in each column have formulas that show unique items where CAT matches the header of that column. Let me know if this approach would help you...

With pivot you could produce either something like this
[TABLE="class: grid, width: 909"]
<colgroup><col><col span="9"></colgroup><tbody>[TR]
[TD][/TD]
[TD="colspan: 3"]CAT1[/TD]
[TD="colspan: 3"]CAT2[/TD]
[TD="colspan: 3"]CAT3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ITEM1[/TD]
[TD]ITEM4[/TD]
[TD]ITEM7[/TD]
[TD]ITEM2[/TD]
[TD]ITEM5[/TD]
[TD]ITEM8[/TD]
[TD]ITEM3[/TD]
[TD]ITEM6[/TD]
[TD]ITEM9[/TD]
[/TR]
</tbody>[/TABLE]

or this

[TABLE="class: grid, width: 171"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]category[/TD]
[TD]items[/TD]
[/TR]
[TR]
[TD]CAT1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ITEM1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ITEM4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ITEM7[/TD]
[/TR]
[TR]
[TD]CAT2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ITEM2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ITEM5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ITEM8[/TD]
[/TR]
[TR]
[TD]CAT3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ITEM3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ITEM6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ITEM9
[/TD]
[/TR]
</tbody>[/TABLE]

Or this:
[TABLE="class: grid, width: 278"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Count of items[/TD]
[TD]category[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]items[/TD]
[TD]CAT1[/TD]
[TD]CAT2[/TD]
[TD]CAT3[/TD]
[/TR]
[TR]
[TD]ITEM1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ITEM7[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ITEM4[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ITEM3[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ITEM9[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ITEM8[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ITEM2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ITEM6[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ITEM5[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Emm]

Hi Istiasztalos,

Thanks for your detailed response.
Yes, the first item is exactly what I am doing.
Basically, I’m creating validation lists for categories in a price list.
I have 55 categories, and about 7000 items.
I’m using unique extract on the CAT to create Headers across a sheet,
Then filtering on each CAT and copying the items under the new header.
It works, but it’s a case of running the code, then go and make a coffee and wait till it’s finished.
Thought there may have been a quicker way ..

Thanks again for your help.

Emm..