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?

CAT1	CAT2	CAT3
ITEM1	ITEM5	ITEM9
ITEM2	ITEM6	ITEM10
ITEM3	ITEM7	ITEM11
ITEM4	ITEM8	ITEM12

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

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

	CAT1	CAT2	CAT3
	ITEM1	ITEM4	ITEM7	ITEM2	ITEM5	ITEM8	ITEM3	ITEM6	ITEM9

<colgroup><col><col span="9"></colgroup><tbody>
</tbody>

or this

category	items
CAT1
	ITEM1
	ITEM4
	ITEM7
CAT2
	ITEM2
	ITEM5
	ITEM8
CAT3
	ITEM3
	ITEM6
	ITEM9

<colgroup><col><col></colgroup><tbody>
</tbody>

Or this:

Count of items	category
items	CAT1	CAT2	CAT3
ITEM1	1
ITEM7	1
ITEM4	1
ITEM3			1
ITEM9			1
ITEM8		1
ITEM2		1
ITEM6			1
ITEM5		1

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

 

[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..