Rearranging Columns in Pivot Table

R

rokunation

New Member
Joined
Apr 17, 2022
Messages
37
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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: 10
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

S
  • Solved
Formula to split date and time and work out energy usage trend
Replies
6
Views
460
Sundance_Kid
S
A
  • Solved
Count entry by time
Replies
3
Views
194
admvlad
A
J
  • Solved
Sorting a Lot of data into multiple sheets (up to 200k lines)
Replies
4
Views
335
JMWh1t3
J
L
  • Solved
Help Please
2 3
Replies
21
Views
489
Asbestos_Jen
Asbestos_Jen
SFCChase
  • Question Question
Use Dropdown to search other tabs
Replies
4
Views
139
SFCChase
SFCChase

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

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