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.
