Creating pivot table using multiple criteria from large data sheet

Eskonn

New Member
Joined
Mar 28, 2018
Messages
18
I have a data sheet that I would like to make a pivot table first off of a specific customer number and then day of the week.
Below is an example of some of the data.

Column E is customer numbers. I would like to make a table using specific numbers from column E and then also only if they show on a specific day from Column C
For this small example customer 1000097338 and 1000097504 for Thu


A B C D E F G H I J K L M N O P Q
RegionDateDay of WeekOrder NumberLocation IDNameRoute IDP SeqA SeqTotal StopsDriver NameDriver IDTW1TW2A ArivalA DepartA Service Time
STL-SAP
1/2/2020​
Thu
8003852330​
1000102502​
WAL MART #7127 (POPLAR BLUFF)
10​
1​
1​
24​
MCCSPADDEN, BRANDEN
238​
05:00-10:0000:00-00:00
8:12​
9:19​
1:06​
STL-SAP
1/2/2020​
Thu
8004422524​
1000102502​
WAL MART #7127 (POPLAR BLUFF)
10​
1​
1​
24​
MCCSPADDEN, BRANDEN
238​
05:00-10:0000:00-00:00
8:12​
9:19​
1:06​
STL-SAP
1/2/2020​
Thu
8005181420​
1000102502​
WAL MART #7127 (POPLAR BLUFF)
10​
1​
1​
24​
MCCSPADDEN, BRANDEN
238​
05:00-10:0000:00-00:00
8:12​
9:19​
1:06​
STL-SAP
1/2/2020​
Thu
8005214546​
1000102502​
WAL MART #7127 (POPLAR BLUFF)
10​
1​
1​
24​
MCCSPADDEN, BRANDEN
238​
05:00-10:0000:00-00:00
8:12​
9:19​
1:06​
STL-SAP
1/2/2020​
Thu
8005383717​
1000102502​
WAL MART #7127 (POPLAR BLUFF)
10​
1​
1​
24​
MCCSPADDEN, BRANDEN
238​
05:00-10:0000:00-00:00
8:12​
9:19​
1:06​
STL-SAP
1/2/2020​
Thu
8005397570​
1000097338​
J D'S QUICK STOP
10​
2​
2​
24​
MCCSPADDEN, BRANDEN
238​
08:00-18:0000:00-00:00
9:26​
9:39​
0:13​
STL-SAP
1/2/2020​
Thu
8005423571​
1000097338​
J D'S QUICK STOP
10​
2​
2​
24​
MCCSPADDEN, BRANDEN
238​
08:00-18:0000:00-00:00
9:26​
9:39​
0:13​
STL-SAP
1/2/2020​
Thu
8005107989​
1000097504​
ALDI #34-POPLAR BLUFF
10​
3​
19​
24​
MCCSPADDEN, BRANDEN
238​
06:00-11:0011:01-18:00
20:09​
20:14​
0:05​
STL-SAP
1/2/2020​
Thu
8005401670​
1000097822​
CASTELLO'S RESTAURANT
10​
4​
3​
24​
MCCSPADDEN, BRANDEN
238​
10:30-18:0000:00-00:00
9:44​
9:54​
0:09​
STL-SAP
1/2/2020​
Thu
8005129512​
1000136307​
RHODES 101 STOP #260
10​
5​
4​
24​
MCCSPADDEN, BRANDEN
238​
06:00-18:0000:00-00:00
9:57​
10:02​
0:05​

Please let me know if additional information is needed.
My list of customer numbers will change as well of the day of the week that I need but the source data will always be the same from this one sheet.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

rachel06

Board Regular
Joined
Feb 3, 2016
Messages
52
Office Version
  1. 365
Platform
  1. Windows
Could you utilize filters for this? Then you could put the customer numbers and days of the week there?
 

Eskonn

New Member
Joined
Mar 28, 2018
Messages
18
Could you utilize filters for this? Then you could put the customer numbers and days of the week there?
I could use filter but was hoping for something more automated. Sometimes I need to find 150 customers at one time
 

rachel06

Board Regular
Joined
Feb 3, 2016
Messages
52
Office Version
  1. 365
Platform
  1. Windows
Do you get a list of the customers you need? Here's kind of an off-the-wall idea. You could add a column next to the customers, then do a vlookup to see which ones you need with like a YES or NO response and have that as a filter?
 

Eskonn

New Member
Joined
Mar 28, 2018
Messages
18
Do you get a list of the customers you need? Here's kind of an off-the-wall idea. You could add a column next to the customers, then do a vlookup to see which ones you need with like a YES or NO response and have that as a filter?
So using a vlookup works. It is still kind of a manual process but I am able to get what I need.

Here is what I did.

Sheet 1 has my source data, I added the column for a vlookup using a list of customers on sheet 2.
Then I copy all the YES anwers and put them on sheet 3 which I used to create a pivot table.

I can then switch the customer numbers on sheet 2 and transfer the new data to sheet 3 to refresh the pivot table.

Thanks for the suggestion.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,782
Messages
5,655,268
Members
418,183
Latest member
skaufman

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