Question description:
This user has given permission to use the problem statement for this
blog.
I need to create a pivot table that sums visits per day by provider:
Date...Visits....Provider So, I can ask...how many visits did provider X have on day Y?
Solved by F. J. in 34 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
20/09/2018 - 05:26
Hello, I understand that you need help in creating a pivot table that will show you the number of times the of visit per day, right?
User
20/09/2018 - 05:28
Yes, please. When I go to create it I have to split the date.
User
20/09/2018 - 05:28
I need to keep the date whole.
Excelchat Expert
20/09/2018 - 05:28
Okay, May I know the relevance of the Dx 1 Dx2 etc?
User
20/09/2018 - 05:28
The Dx at the top are the different codes I'm working with.
Excelchat Expert
20/09/2018 - 05:29
So they are not necessary in the Pivot Table?
User
20/09/2018 - 05:29
I receive raw data like I've created for you.
User
20/09/2018 - 05:29
Then I have to sort it by date.
User
20/09/2018 - 05:29
They I need to create a pivot table.
Excelchat Expert
20/09/2018 - 05:30
In the Example you provided, the Pivot Table will result to 1 visit per day, right?
User
20/09/2018 - 05:30
Except when I go to create it the pivot table makes me split the date into three columns.
Excelchat Expert
20/09/2018 - 05:30
Okay, I'll help you with this one. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
Excelchat Expert
20/09/2018 - 05:30
I may have a few quick question to help me fully understand your requirement.
User
20/09/2018 - 05:30
I need to be able to know on June 7, 2018 how many appointments (time) provide D had.
Excelchat Expert
20/09/2018 - 05:31
Yes and in the example you've provided, Provider D only had one appointment on June 7, 2018, right?
User
20/09/2018 - 05:31
This is a free trial for me and I believe I have one question. Do I not?
Excelchat Expert
20/09/2018 - 05:32
Don't worry about that, it's a standard reminder we give.
User
20/09/2018 - 05:32
Yes, I receive a thousand rows of data so a manual sort isn't a good idea.
User
20/09/2018 - 05:32
Would you like me to add more data?
Excelchat Expert
20/09/2018 - 05:32
And yes, your question is about creating the Pivot Table to count the number of visit per provider so I'll help you with this one.
Excelchat Expert
20/09/2018 - 05:33
Yes please, adding more data would really help show you the result of what I'll work on.
Excelchat Expert
20/09/2018 - 05:33
Thank you. What do you want to see in the Pivot Table, just the date and the Provider and the number of visits?
User
20/09/2018 - 05:36
i would like to pull by date how many visits each provider has done. For example: Provider A did 10 visit on June 7, 2018 while Provider B did 7. Make sense?
Excelchat Expert
20/09/2018 - 05:37
Yes. I'll be working on this offline and prepare the steps. Please give me a few minutes.
Excelchat Expert
20/09/2018 - 05:37
Can you complete the sample data please?
Excelchat Expert
20/09/2018 - 05:37
Providers are incomplete starting row 36
User
20/09/2018 - 05:37
Working on it!
Excelchat Expert
20/09/2018 - 05:38
Oh, data on top is also missing a lot. Looks like we'll need more time since the sample data isn't completed yet. Please extend the session when you get a prompt.
Excelchat Expert
20/09/2018 - 05:41
That should be enough, the DX columns shouldn't matter at all so we can add random stuff in there.
User
20/09/2018 - 05:41
Ok....sorry...I didn't know I was going to need to create a sample or I would have done it prior.
Excelchat Expert
20/09/2018 - 05:45
I'm working on this locally on my Excel. Please give me a few minutes.
User
20/09/2018 - 05:45
Ok. Thank you.
Excelchat Expert
20/09/2018 - 05:48
Please download this file.
[Uploaded an Excel file]
User
20/09/2018 - 05:48
Downloading...
Excelchat Expert
20/09/2018 - 05:48
In this file I created 2 Pivot Table that both shows what you want to see but they are formatted a bit differently.
Excelchat Expert
20/09/2018 - 05:49
Let me know which one you prefer so I can tell you how it's done.
User
20/09/2018 - 05:50
This is awesome! I will so use you again. Let me think...I think the one with the grand totals please.
Excelchat Expert
20/09/2018 - 05:50
The left one or the right one?
Excelchat Expert
20/09/2018 - 05:50
Both have Grand Totals. :)
User
20/09/2018 - 05:51
The one on the right that has date...a,b,c,d...and grand total.
Excelchat Expert
20/09/2018 - 05:51
Okay, let me prepare the steps.
User
20/09/2018 - 05:51
The one on the right has grand total across and down. That one please.
Excelchat Expert
20/09/2018 - 05:51
You are familiar with creating a Pivot Table, right? It's just the right settings that you need help with.
User
20/09/2018 - 05:51
Yes, I know how to insert a pivot table.
Excelchat Expert
20/09/2018 - 05:51
Okay, I'll skip that part.
User
20/09/2018 - 05:53
After you help me, and I try it on my own, can I come back (understanding I need to pay) and get help with you again?
Excelchat Expert
20/09/2018 - 05:54
Of course but I can't guarantee that you will be able to work with me since you may be assigned with another expert.
Excelchat Expert
20/09/2018 - 05:54
All of us are equally able to help you though so don't worry. Just make sure the description is accurate so the right expert will be assigned to you.
User
20/09/2018 - 05:55
And do I reference the problem number: 45426?
Excelchat Expert
20/09/2018 - 05:55
Anyway, please go to the INSTRUCTIONS tab in the sheet to the right.
Excelchat Expert
20/09/2018 - 05:55
No, just type a description again.
Excelchat Expert
20/09/2018 - 05:56
Are you in the INSTRUCTIONS Tab?
User
20/09/2018 - 05:56
Yes, I know this.
User
20/09/2018 - 05:56
I have a 2 pm call.
Excelchat Expert
20/09/2018 - 05:56
Okay, that's all you really need to do to recreate the Pivot Table like the one in the file I sent you.
User
20/09/2018 - 05:57
The date...it doesn't like the date when I do the instructions you provided.
Excelchat Expert
20/09/2018 - 05:57
Alright, save the instructions and try it on your file later.
Excelchat Expert
20/09/2018 - 05:57
Did you UNGROUP?
User
20/09/2018 - 05:57
No, I didn't do ungroup. I think you're right....that must be it. Let me try it and if it doesn't work I'll come back. You have been most kind. Thank you.
Excelchat Expert
20/09/2018 - 05:58
When dragging the Date under ROWS, Excel may group the date so it will show Year QTR and Month instead.
Excelchat Expert
20/09/2018 - 05:58
By Ungrouping you will be able to view individual dates.
User
20/09/2018 - 05:58
Ah, that must be the problem then. You are awesome!
Excelchat Expert
20/09/2018 - 05:58
Alright, I know you will have to attend a meeting soon so would there be anything else that I can help you with regards to the original question?
User
20/09/2018 - 05:59
No, thank you. I will try it. Have a lovely day..
Excelchat Expert
20/09/2018 - 05:59
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert
20/09/2018 - 05:59
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert
20/09/2018 - 05:59
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
