Rearranging Columns in Pivot Table

R

rokunation

New Member
Joined
Apr 17, 2022
Messages
35
Office Version
  1. 2013
Platform
  1. Windows
Hi I have the following data:

TESTMay 2022- Fill Rate COT FILE.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1CUSTOMER NAMEINVOICE NUMBERINVOICE DATELINE ABBREVPART NUMBERDESCRIPTIONQUANTITY SOLDNET PRICEEXTENDED PRICEUNIT COSTORIGINPO NUMBERATTENTIONActual DateSTATUSIdentifierPart Request IDRequest TimeReqApproveTime Diff (Req-REQApp)Time Diff (Operational Hours) (Req-REQApp)Approved TimeReady TimeClosed TimeRequest->Ready Calendar TimeTime Difference (Approve-->Ready)Time Diff (Operational Hours) (Approve->Ready)Fill/No Fill
2CITY OF TORONTO -DISCO94691151820222RU455538C1AIR TANK CABLE236.4272.8436.42NAPA ACCESS GENERATED194036 774198STOCK05/18/2022STOCK2RU455538C1194036 7741981519332022-05-18 1:28:17 PM2022-05-18 1:28:58 PM0:00:410:00:412022-05-18 1:29:40 PM2022-05-18 1:29:51 PM2022-05-18 1:40:48 PM0:01:340:00:110:00:11FILL
3CITY OF TORONTO -DISCO94691151820222RU3832996C1AIR TANK1249.22249.22249.22NAPA ACCESS GENERATED194036 774198STOCK05/18/2022STOCK2RU3832996C1194036 7741981518492022-05-18 7:19:35 AM2022-05-18 1:28:39 PM6:09:046:09:042022-05-18 1:29:40 PM2022-05-18 1:29:51 PM2022-05-18 1:40:47 PM6:10:160:00:110:00:11FILL
4CITY OF TORONTO -DISCO9469285202022NWB35220TRICO ICE WINTER BLAD212.324.612.3NAPA ACCESS GENERATED135181 773893STOCK05/20/2022STOCKNWB35220135181 7738931517352022-05-17 7:22:04 AM2022-05-17 7:54:01 AM0:31:570:31:572022-05-17 7:56:21 AM2022-05-17 7:56:36 AM2022-05-20 2:15:41 PM0:34:320:00:150:00:15FILL
5CITY OF TORONTO -DISCO9469015182022FLXBF31LBRAKE FLUID17.757.757.75NAPA ACCESS GENERATED156142 773907STOCK05/18/2022STOCKFLXBF31L156142 7739071517272022-05-17 6:28:56 AM2022-05-17 7:17:59 AM0:49:030:47:592022-05-17 7:56:21 AM2022-05-17 7:56:36 AM2022-05-18 8:53:15 AM1:27:400:00:150:00:15FILL
6CITY OF TORONTO -DISCO9469435252022NGF1748XDHD LUBE FILTER142.3442.3456.73NAPA ACCESS GENERATED188060 774791STOCK05/25/2022STOCKNGF1748XD188060 7747911525072022-05-25 10:24:59 AM2022-05-25 10:55:33 AM0:30:340:30:342022-05-25 11:37:00 AM2022-05-25 11:37:15 AM2022-05-25 2:14:21 PM1:12:160:00:150:00:15FILL
7CITY OF TORONTO -DISCO94694352520229KMT32KROWN T3216.076.076.07NAPA ACCESS GENERATED188060 774791STOCK05/25/2022STOCK9KMT32188060 7747911525092022-05-25 10:25:40 AM2022-05-25 11:05:14 AM0:39:340:39:342022-05-25 11:37:00 AM2022-05-25 11:37:15 AM2022-05-25 2:14:22 PM1:11:350:00:150:00:15FILL
8CITY OF TORONTO -DISCO9469435252022KFL837AIR INTAKE KLEEN16.016.016.01NAPA ACCESS GENERATED188060 774791STOCK05/25/2022STOCKKFL837188060 7747911525082022-05-25 10:25:40 AM2022-05-25 11:06:13 AM0:40:330:40:332022-05-25 11:37:00 AM2022-05-25 11:37:15 AM2022-05-25 2:14:21 PM1:11:350:00:150:00:15FILL
9CITY OF TORONTO -DISCO9469465252022NGF1748XDHD LUBE FILTER142.3442.3456.73NAPA ACCESS GENERATED176025 774383STOCK05/25/2022STOCKNGF1748XD176025 7743831520322022-05-19 8:18:21 AM2022-05-19 10:28:32 AM2:10:112:10:112022-05-19 10:54:54 AM2022-05-19 10:55:09 AM2022-05-25 2:18:04 PM2:36:480:00:150:00:15FILL
10CITY OF TORONTO -DISCO94694452520222TC7H3608NUT403.67146.83.67NAPA ACCESS GENERATED333017 771164STOCK05/25/2022STOCK2TC7H3608333017 7711641521562022-05-20 5:11:29 AM2022-05-25 11:34:36 AM126:23:0730:34:362022-05-25 11:37:00 AM2022-05-25 11:37:15 AM2022-05-25 2:15:56 PM126:25:460:00:150:00:15FILL
11CITY OF TORONTO -DISCO94694452520222TC2823655BOLT403.47138.83.47NAPA ACCESS GENERATED333017 771164STOCK05/25/2022STOCK2TC2823655333017 7711641521572022-05-20 5:11:58 AM2022-05-25 11:34:59 AM126:23:0130:34:592022-05-25 11:37:00 AM2022-05-25 11:37:15 AM2022-05-25 2:15:56 PM126:25:170:00:150:00:15FILL
12CITY OF TORONTO -DISCO94694452520222TC1984781WASHER18.758.758.75NAPA ACCESS GENERATED333017 771164STOCK05/25/2022STOCK2TC1984781333017 7711641520372022-05-19 8:24:11 AM2022-05-25 11:34:02 AM147:09:5137:09:512022-05-25 11:37:00 AM2022-05-25 11:37:15 AM2022-05-25 2:15:57 PM147:13:040:00:150:00:15FILL
13CITY OF TORONTO -DISCO94694452520222TC8T6381BOLT120.320.320.3NAPA ACCESS GENERATED333017 771164STOCK05/25/2022STOCK2TC8T6381333017 7711641520362022-05-19 8:21:39 AM2022-05-25 11:34:02 AM147:12:2337:12:232022-05-25 11:37:00 AM2022-05-25 11:37:15 AM2022-05-25 2:15:57 PM147:15:360:00:150:00:15FILL
14CITY OF TORONTO -DISCO94694452520222TC3012754HOSE178.7778.7778.77NAPA ACCESS GENERATED333017 771164STOCK05/25/2022STOCK2TC3012754333017 7711641519752022-05-19 5:14:03 AM2022-05-25 11:35:14 AM150:21:1139:05:142022-05-25 11:37:00 AM2022-05-25 11:37:15 AM2022-05-25 2:15:58 PM150:23:120:00:150:00:15FILL
15CITY OF TORONTO -DISCO9468585102022KFL472GASKET CLEAR SILICONE112.0212.0212.02NAPA ACCESS GENERATED135185 773559STOCK05/10/2022STOCKKFL472135185 7735591510912022-05-10 5:19:56 PM2022-05-10 5:23:34 PM0:03:380:03:382022-05-10 5:23:51 PM2022-05-10 5:24:09 PM2022-05-10 5:25:11 PM0:04:130:00:180:00:18FILL
16CITY OF TORONTO -DISCO94693652420221YSASH1154SHOCK294.71189.4294.71NAPA ACCESS GENERATED135181 773893652094805/24/2022NON STOCK1YSASH1154135181 7738931519962022-05-19 7:00:58 AM2022-05-24 1:30:25 PM126:29:2731:59:272022-05-24 1:38:00 PM2022-05-24 1:38:21 PM2022-05-24 2:10:12 PM126:37:230:00:210:00:21NO FILL
Disco
Cell Formulas
RangeFormula
P2:P16P2=CONCATENATE(D2,E2,L2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
Q:QCell ValueduplicatestextNO
G1:G358Cell Value<0textNO




I want to show in my pivot table this type of data:

Book3
CDEFG
5Fill RateMay 2022
6TOTALFILLNO FILLFILL/TOTAL
7Disco3572936482.07%
SUMMARY
Cell Formulas
RangeFormula
F7F7=D7-E7
G7G7=E7/D7



But I am getting this arrangement:

TESTMay 2022- Fill Rate COT FILE.xlsx
ABCD
3CUSTOMER NAMEFill/No FillCount of Fill/No Fill2Count of CUSTOMER NAME
4CITY OF TORONTO -DISCOFILL29382.07%
5NO FILL6417.93%
6Grand Total357100.00%
Sheet4




Please advise.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Look at this and see if it covers what you want:
 

Attachments

  • Pt_fill_nofill.JPG
    Pt_fill_nofill.JPG
    82 KB · Views: 9
Upvote 0
So total of fill, no fill and total of both then the fill rate which is just fill/total.
 
Upvote 0
Can this be done by just showing the totals. then adding a calculated field with fill/total to get the fill rate?
 
Upvote 0
I have 2 questions - why don't you just do this with formulas ... why do you have to use PivotTables? And, have you considered just hiding the columns of the PivotTable sheet that you do not want to see?
 
Upvote 0
rokunation said:
Can this be done by just showing the totals. then adding a calculated field with fill/total to get the fill rate?
Click to expand...
Cautiously, yes.
When you have a calculation that yields a percentage, at some totals/subtotals the numbers will not be accurate in certain groupings.
Some of the conversation would be better helped with an anonymised data set. It helps test and verify proposed solutions and keep terms you're using.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
311
awoohaw
awoohaw
J
  • Solved
Excel "Strava Best Effort" Calculation
2
Replies
13
Views
402
felixstraube
F
J
  • Solved
Excel Formula to highlight most recent record
Replies
3
Views
99
etaf
etaf
M
  • Solved
Returning results based on multiple criteria
Replies
5
Views
283
DanteAmor
DanteAmor
A
  • Solved
Macro getting stuck on average when no data is present
Replies
5
Views
216
amphead
A

Forum statistics

Threads
1,214,991
Messages
6,122,628
Members
449,095
Latest member
bsb1122

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