Mechixx

Board Regular
Joined
Oct 15, 2015
Messages
59
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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

Board Regular
Joined
Oct 15, 2015
Messages
59
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

Board Regular
Joined
Oct 15, 2015
Messages
59
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,949
Messages
5,621,781
Members
415,856
Latest member
jimb2k

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top