Pivot Table

[Mechixx]

Hello there everyone, i have a question about pivot tables, or i think thats the best way to do what i want to accomplish.

so i have a table of data that looks as follows:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Part #
[/TD]
[TD]Part Name
[/TD]
[TD]Dept.
[/TD]
[TD]Op
[/TD]
[TD]Description
[/TD]
[TD]Mag 1
[/TD]
[TD]Mag 1 Build up
[/TD]
[TD]Mag 2
[/TD]
[TD]Mag 2 Build up
[/TD]
[TD]Mag 3
[/TD]
[TD]Mag 3 Build up
[/TD]
[TD]Mag 4
[/TD]
[TD]Mag 4 Build up
[/TD]
[/TR]
[TR]
[TD]272
[/TD]
[TD]Sprocket
[/TD]
[TD]Auto
[/TD]
[TD]10
[/TD]
[TD]Full
[/TD]
[TD]PRB_2x10
[/TD]
[TD]Head>2x10
[/TD]
[TD]PRB_3x50
[/TD]
[TD]Head>3x50
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]536
[/TD]
[TD]Sprocket
[/TD]
[TD]Auto
[/TD]
[TD]10
[/TD]
[TD]Full
[/TD]
[TD]PRB_2x10
[/TD]
[TD]Head>2x10
[/TD]
[TD][/TD]
[TD][/TD]
[TD]PRB_1x27.5
[/TD]
[TD]Head>1x27.5
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]









Theres many more rows of this.

The table is called 'ProbeList'

so what i would like a pivot table to do is list data as follows:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Probe List
[/TD]
[TD]Mag 1
[/TD]
[TD]Mag 2
[/TD]
[TD]Mag 3
[/TD]
[TD]Mag 4
[/TD]
[TD]Totals
[/TD]
[/TR]
[TR]
[TD]PRB_1x27.5
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]PRB_2x10
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]PRB_3x50
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Totals
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]










how do i go about doing this? i cant seem for the life of me be able to get a pivot table to be able to do this for me

 

[Jerry Sullivan]

Re: Pivot Table Help

Hi Mechixx,

What you want to do is called "unpivotting" your existing dataset. If you have a version of Excel with Power Query installed, that is an easy way to do that.

For earlier versions of Excel, you can use VBA to unpivot data.

 

[Mechixx]

Re: Pivot Table Help

Hi Mechixx,

What you want to do is called "unpivotting" your existing dataset. If you have a version of Excel with Power Query installed, that is an easy way to do that.

For earlier versions of Excel, you can use VBA to unpivot data.

If i install power query and use it, will it still work if someone else without power query opens and uses this workbook?

and how would i go about unpivoting this data with a VBA?

 

[Mechixx]

Re: Pivot Table Help

Good news!

I was actually able to get what i wanted just by using a Pivot Table made with multiple consolidation ranges!
used my whole table for the data, then used label filters to filter out all the results i didn't want.

Thanks for your help though Jerry!