How to summarise a master pivot table

S

saraapple

Board Regular
Joined
Feb 3, 2020
Messages
165
Office Version
  1. 2010
Platform
  1. Windows
I have a master pivot table that covers the performance of over 70 suppliers.
Its shows items sent and received and flags any missing items......I now need to use this pivot to make a summary table (linked to the pivot) that shows sent and received by induvial supplier.
I am pretty sure this can be done with formulas but have only managed to count the supplier so far:


221121 2030hrs.xlsx
B
252
Sheet1
Cell Formulas
RangeFormula
B2B2=COUNTIF('MASTER DETAIL'!A:A,"ADD01")


221121 2030hrs.xlsx
ABCDEFGHIJKLM
1PlannedColTripSchedule211122
2PlannedColTripOwningDepotDHLSTK
3PlannedTrunkTripRouteCode(blank)
4
5Row LabelsCustomerNameManifestPlannedColTripPOConsignmentItemIdDuTypeStoreIdStoreNameReceivedScanDepotReceiptScanDepotMISSING
6AAAA1586574PAR-00998557300819194812945648100022122213OHPAL590WolverhamptonDHLSTK(blank)
7AAAA1586574PAR-00998557300819209012945649100022122164OCTN510WalsallDHLSTK(blank)
8AAAA1586574PAR-00998557300819260112945657100022122178OCTN134Chesterfield NEWDHLSTK(blank)
9AAAA1586574PAR-00998557300819260112945657100022122179OCTN134Chesterfield NEWDHLSTK(blank)
10AAAA1586574PAR-00998557300819260112945657100022122180OCTN134Chesterfield NEW(blank)(blank)MISSING
11AAAA1586574PAR-00998557300819260112945657100022122181OCTN134Chesterfield NEWDHLSTK(blank)MISSING
12AAAA1586574PAR-00998557300819260112945657100022122182OCTN134Chesterfield NEWDHLSTK(blank)MISSING
13AAAA1586574PAR-00998557810016656212948812100022122162PALLET9573STOKE 3DHLSTK(blank)
14AAAA1586574PAR-00998557810016656312948813100022122163PALLET9579CORBY iForce CGRDHLSTK(blank)
15
16BBBB1586588PAR-00998710300819041612944778100022122843CTN730Stockport TAIL LIFTDHLSTK(blank)
17BBBB1586588PAR-00998710300819066012945659100022122851CTN670BarnsleyDHLSTK(blank)
18BBBB1586588PAR-00998710300819068012945661100022122853CTN35Solihull TAIL LIFTDHLSTK(blank)
19BBBB1586588PAR-00998710300819212912945687100022122846CTN295PaisleyDHLSTK(blank)
20BBBB1586588PAR-00998710300819216512945688100022122847CTN111Kilner way TAIL LIFTDHLSTK(blank)
21BBBB1586588PAR-00998710300819245912945690100022122849CTN180RotherhamDHLSTK(blank)
22
23CCCC1586533PAR-00998705810016655112948817100022126795PALLET9573STOKE 3DHLSTK(blank)
24CCCC1586533PAR-00998705810016655112948817100022126796PALLET9573STOKE 3(blank)(blank)MISSING
25CCCC1586533PAR-00998705810016655112948817100022126797PALLET9573STOKE 3DHLSTK(blank)
26CCCC1586533PAR-00998705810016655112948817100022126798PALLET9573STOKE 3DHLSTK(blank)
27CCCC1586533PAR-00998705810016655212948818100022126799PALLET9573STOKE 3DHLSTK(blank)
28CCCC1586558PAR-00998705300819220012945814100022127685TOTE945St HelensDHLSTK(blank)
29CCCC1586558PAR-00998705300819220012945814100022127686TOTE945St HelensDHLSTK(blank)MISSING
30CCCC1586558PAR-00998705300819224712945819100022127687BAG215Blackburn TAIL LIFTDHLSTK(blank)
31CCCC1586558PAR-00998705300819224712945819100022127688TOTE215Blackburn TAIL LIFTDHLSTK(blank)
32
33DDDD1586565PAR-00998700300819079012945843100022121946TOTE615Isle of WightDHLSTK(blank)
34DDDD1586565PAR-00998700300819079012945843100022121947TOTE615Isle of WightDHLSTK(blank)
35DDDD1586567PAR-00998700300819046912944785100022122002TOTE885LlanelliDHLSTK(blank)
36DDDD1586567PAR-00998700300819046912944785100022122003TOTE885LlanelliDHLSTK(blank)
37DDDD1586568PAR-00998700810016656512948822100022122060PALLET9573STOKE 3DHLSTK(blank)
38DDDD1586568PAR-00998700810016656512948822100022122061PALLET9573STOKE 3DHLSTK(blank)
39
40EEEE1586524PAR-00998553300819068412945873100022121625GIRAFFE545Stafford TAIL LIFTDHLSTK(blank)MISSING
41EEEE1586524PAR-00998553300819091612945879100022121631GIRAFFE141Newcastle WolstantonDHLSTK(blank)MISSING
42EEEE1586524PAR-00998553300819119612945880100022121632GIRAFFE685Kidderminster - NDS - Tail liftDHLSTK(blank)MISSING
43EEEE1586524PAR-00998553300819157212945883100022121617GIRAFFE123CRIBBS CAUSEWAYDHLSTK(blank)MISSING
44EEEE1586524PAR-00998553300819162012945884100022121618GIRAFFE575FalkirkDHLSTK(blank)
45EEEE1586524PAR-00998553300819194412945886100022121620GIRAFFE590WolverhamptonDHLSTK(blank)
46EEEE1586524PAR-00998553300819259812945890100022121624GIRAFFE134Chesterfield NEWDHLSTK(blank)
47EEEE1586524PAR-00998553810016656612948823100022121616GIRAFFE9573STOKE 3DHLSTK(blank)
48
49FFFf1586849PAR-00998570300819142612945924100022129402TOTE585ExeterDHLSTK(blank)
50FFFf1586849PAR-00998570300819142612945924100022129403TOTE585ExeterDHLSTK(blank)
51FFFf1586849PAR-00998570300819155112945930100022129404TOTE350Weston-super-Mare TAIL LIFTDHLSTK(blank)
52FFFf1586849PAR-00998570300819155112945930100022129405TOTE350Weston-super-Mare TAIL LIFTDHLSTK(blank)MISSING
53FFFf1586849PAR-00998570300819155112945930100022129406TOTE350Weston-super-Mare TAIL LIFTDHLSTK(blank)MISSING
54FFFf1586849PAR-00998570300819162212945931100022129407TOTE123CRIBBS CAUSEWAYDHLSTK(blank)MISSING
55FFFf1586849PAR-00998570300819162212945931100022129408TOTE123CRIBBS CAUSEWAYDHLSTK(blank)MISSING
56FFFf1586849PAR-00998570300819162212945931100022129409TOTE123CRIBBS CAUSEWAYDHLSTK(blank)MISSING
57FFFf1586849PAR-00998570300819164112945933100022129423TOTE575FalkirkDHLSTK(blank)MISSING
58FFFf1586849PAR-00998570300819164112945933100022129424TOTE575FalkirkDHLSTK(blank)MISSING
59FFFf1586849PAR-00998570300819174712945934100022129425TOTE935WorcesterDHLSTK(blank)
60FFFf1586849PAR-00998570300819184712945936100022129426TOTE85DARLINGTON Tail liftDHLSTK(blank)
61FFFf1586849PAR-00998570300819184712945936100022129427TOTE85DARLINGTON Tail liftDHLSTK(blank)
62FFFf1586849PAR-00998570300819184712945936100022129428TOTE85DARLINGTON Tail liftDHLSTK(blank)
63FFFf1586849PAR-00998570300819184712945936100022129429TOTE85DARLINGTON Tail liftDHLSTK(blank)
64FFFf1586849PAR-00998570300819186812945940100022129430TOTE75Livingston TAIL LIFTDHLSTK(blank)MISSING
65FFFf1586849PAR-00998570300819186812945940100022129431TOTE75Livingston TAIL LIFTDHLSTK(blank)
66FFFf1586849PAR-00998570300819196012945942100022129432TOTE590WolverhamptonDHLSTK(blank)
67FFFf1586849PAR-00998570300819196012945942100022129433TOTE590WolverhamptonDHLSTK(blank)
68FFFf1586849PAR-00998570300819196012945942100022129434TOTE590WolverhamptonDHLSTK(blank)
69FFFf1586849PAR-00998570300819200512945943100022129435TOTE445HartlepoolDHLSTK(blank)
70FFFf1586849PAR-00998570300819200512945943100022129436TOTE445HartlepoolDHLSTK(blank)
71FFFf1586849PAR-00998570300819200512945943100022129437TOTE445HartlepoolDHLSTK(blank)
Sheet2


Is this possible? I know I could do it with individual pivots but don't like the idea of my workbook having so man pivots, and my work books always end up too big and slow!

Any ideas would be greatly appreciated.
Sara
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
You must log in or register to reply here.

Similar threads

M
  • Question
Excel pivot table query - retaining filters on pivot in absence of data
2
Replies
10
Views
1K
Alex Blakenburg
A
K
  • Question
Adding a custom or "non linked" text column to pivot table
Replies
1
Views
347
Keegan4123
K
1
  • Question
How can I create a pivot table pie chart to summarise each row that has more than one column that uses the same multiple choice list
Replies
0
Views
509
108monkeys
1
S
  • Question
Is there a way to link my summary table back to the individual pivots?
Replies
0
Views
264
saraapple
S
G
  • Question
Pivot Table to Report Confirmation (or not) Status
2
Replies
18
Views
2K
Greg Stough
G

Forum statistics

Threads
1,215,429
Messages
6,124,842
Members
449,193
Latest member
MikeVol

Share this page

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
Back
Top