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:

Part #

Part Name

Dept.

Op

Description

Mag 1

Mag 1 Build up

Mag 2

Mag 2 Build up

Mag 3

Mag 3 Build up

Mag 4

Mag 4 Build up

272

Sprocket

Auto

10

Full

PRB_2x10

Head>2x10

PRB_3x50

Head>3x50

536

Sprocket

Auto

10

Full

PRB_2x10

Head>2x10

PRB_1x27.5

Head>1x27.5

<tbody>
</tbody>

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:

Probe List	Mag 1	Mag 2	Mag 3	Mag 4	Totals
PRB_1x27.5			1		1
PRB_2x10	2				2
PRB_3x50		1			1
Totals	2	1	1	0	4

<tbody>
</tbody>

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!