Excel - General Question on Pivot Table - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I want to only count unique instances of an array in a pivot table. For example client A has been invoiced twice for Product B and 3 times for Product C. I want to count the fact that Client A has used Product B and Product C ( ie a value of 1 for each) and not 2 for A and 3 for B
Solved by G. U. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 23/05/2018 - 09:58
Hello, I understand that you just want to count the unique values in your Pivot table, right?
User 23/05/2018 - 09:58
Hi. Yes. the datasource is huge and confidential so don't want to attach if that's ok.
Excelchat Expert 23/05/2018 - 09:58
Alright, it's going to be a lot tougher without the actual data but we can try.
Excelchat Expert 23/05/2018 - 09:58
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.
User 23/05/2018 - 09:59
ok
Excelchat Expert 23/05/2018 - 09:59
Can you supply a sample data using the document to the right?
User 23/05/2018 - 09:59
ok
Excelchat Expert 23/05/2018 - 09:59
Try to be as accurate as possible with the headers, columns and sheet names.
Excelchat Expert 23/05/2018 - 10:00
Are you trying to recreate the Pivot table or the actual data source?
User 23/05/2018 - 10:01
hold on 2 secs
Excelchat Expert 23/05/2018 - 10:08
Hello I don't mean to rush you, we have a very limited amount of time to solve this and if we don't act soon, I can no longer guarantee a solution due to the time constraints.
User 23/05/2018 - 10:09
I'm sorry - My ceo has come in. Can I dial in again in 30 mins
Excelchat Expert 23/05/2018 - 10:10
Oh sure, you can leave this window open and I'll give you the steps on how to count distinct instead.
Excelchat Expert 23/05/2018 - 10:10
I'll also ask our support team to send you the instructions but for further assistance, you can simply contact us back with a prepared sample data.
Excelchat Expert 23/05/2018 - 10:10
This session will automatically end in 6 minutes.
Excelchat Expert 23/05/2018 - 10:10
Let me prepare the steps.
Excelchat Expert 23/05/2018 - 10:13
I've prepared the steps for you to follow.
[Uploaded an Excel file]
Excelchat Expert 23/05/2018 - 10:14
The session will automatically end soon and there's nothing I can do to stop that. I'll also be asking our suppor team to send the steps to you via email.
User 23/05/2018 - 10:17
Thank you. I dont have distinct count?
Excelchat Expert 23/05/2018 - 10:18
Hello, you've extended the session but are not replying. May I ask that we end the session for now if you don't have the time yet as we have plenty of other users in line?
Excelchat Expert 23/05/2018 - 10:18
Oh, can you complete the sample data so I can apply it on my end?
Excelchat Expert 23/05/2018 - 10:19
I need the format of your data, not the pivot table itself.
Excelchat Expert 23/05/2018 - 10:19
Your raw data.
Excelchat Expert 23/05/2018 - 10:20
I understand what you need. But what I need is how your data source looks like.
Excelchat Expert 23/05/2018 - 10:21
Without me knowing how your datasource looks like I won't be able to accurately provide you the information you need.
User 23/05/2018 - 10:21
the data source is an excel sheet with field headers and rows of sales transactions. I'll copy some in now
Excelchat Expert 23/05/2018 - 10:22
Yes, that's exactly what I need. You can replace the sensitive data for now.
User 23/05/2018 - 10:22
see the excel.
Excelchat Expert 23/05/2018 - 10:22
Okay, which column do you want to count?
User 23/05/2018 - 10:23
Campaign Client Name
Excelchat Expert 23/05/2018 - 10:23
What is the equivalent of Panel A and Panel B?
User 23/05/2018 - 10:24
Digital Product Name is Panel A
User 23/05/2018 - 10:25
Campaign Client Name is Customer
Excelchat Expert 23/05/2018 - 10:25
Okay, what about Panel B?
User 23/05/2018 - 10:26
Same thing. We have lots of panels, A, B, C
User 23/05/2018 - 10:28
I want to show panels along the top of the table and for each one a count of the customers who advertise on them. If customer 1 advertises 10 times on panel A, I just want to show that customer 1 uses Panel 1 by putting a 1 in the field
Excelchat Expert 23/05/2018 - 10:28
The data you provided isn't enough to make a valid pivot Table but I was able to add the distinct count on it. I'll show you in a second.
Excelchat Expert 23/05/2018 - 10:31
Please download this file and then add more data to the bottom of Sheet1 and then finally, refresh the Pivot Table.
[Uploaded an Excel file]
Excelchat Expert 23/05/2018 - 10:31
The data you provided only has 2 records but it should be returning the right result with the pivot table I gave you. It's counting EDF > The Eye as only 1 which is what you are expecting.
Excelchat Expert 23/05/2018 - 10:34
Have you downloaded the file?
User 23/05/2018 - 10:34
will try now
User 23/05/2018 - 10:34
yes
Excelchat Expert 23/05/2018 - 10:34
Okay, just a heads up, without access to the file I won't be able to tell you exactly what is wrong if you encounter an error.
Excelchat Expert 23/05/2018 - 10:34
But as you can see, with the sample you provided, it works.
User 23/05/2018 - 10:35
I cant see the pivot table set up. the tabel is hard coded
Excelchat Expert 23/05/2018 - 10:35
Not sure I understand. The file I sent you does not have any hard coding in it.
User 23/05/2018 - 10:36
may be a problem opening it.
User 23/05/2018 - 10:36
the pivot table set up is not showing
User 23/05/2018 - 10:36
can you put it on the sheet to the right
Excelchat Expert 23/05/2018 - 10:36
[Uploaded an Excel file]
Excelchat Expert 23/05/2018 - 10:37
Here's a screenshot of the file on my end where it clearly shows it is a working pivot table.
User 23/05/2018 - 10:37
That is what I'm expecting but not what I get
Excelchat Expert 23/05/2018 - 10:37
And, unfortunately, I can't place the pivot table to the document to the right as that is a Google Sheet and is different from an Excel Pivot table.
User 23/05/2018 - 10:37
Adding the data to the table has just crashed my system. Let me rety
Excelchat Expert 23/05/2018 - 10:38
I didn't really do anything special to the pivot table, I just followed the steps I sent you earlier.
Excelchat Expert 23/05/2018 - 10:38
You can try to follow that again and make sure you don't miss anything especially the checkbox that you need to check.
User 23/05/2018 - 10:38
I can't see the unique option in my pivot table
Excelchat Expert 23/05/2018 - 10:39
When you followed the steps, did you put a check on this box?
[Uploaded an Excel file]
User 23/05/2018 - 10:40
dont think so. hang on
Excelchat Expert 23/05/2018 - 10:40
That's the most important step.
User 23/05/2018 - 10:42
Got it. Works. Brilliant. Thank you....
Excelchat Expert 23/05/2018 - 10:42
That's great!
Excelchat Expert 23/05/2018 - 10:42
I'm glad I was of help.
User 23/05/2018 - 10:42
I'm a CFO and have a business with 1000's of finance professionals. Is there a link I can send out and get referrals. This will be well received?
Excelchat Expert 23/05/2018 - 10:42
Would there be anything else that I can help you with regards to the original question?
User 23/05/2018 - 10:43
Btw, are you human or a bot? I never know these days?
Excelchat Expert 23/05/2018 - 10:43
Lol. I'm 100% human.
Excelchat Expert 23/05/2018 - 10:43
I wish I was a bot so I don't get tired haha
User 23/05/2018 - 10:43
lol
Excelchat Expert 23/05/2018 - 10:44
As for referral, I'm afraid I have nothing to share regarding that but feel free to send our support team an email.
Excelchat Expert 23/05/2018 - 10:44
sheets.support@gotitapp.co
Excelchat Expert 23/05/2018 - 10:44
Contact our support team and they'll be very happy to communicate with you and your company.
User 23/05/2018 - 10:45
Will do
Excelchat Expert 23/05/2018 - 10:45
You can say that you've been referred by Expert 173. :)
Excelchat Expert 23/05/2018 - 10:46
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 23/05/2018 - 10:46
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 23/05/2018 - 10:46
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.
Excelchat Expert 23/05/2018 - 10:50
Hi, I have not received a reply from you for quite some time. I'm afraid the session will end automatically soon. I sincerely hope we were able to address your concern.

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.