Excel - General Question on Pivot Table - Expert Solution

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

I have a pivot table in which per order (on the rows) stands how much products they ordered per size (on the columns). I want to determine which combinations of quantities of sizes people order. And I want to count these combinations.
Solved by E. W. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 19/06/2018 - 11:54
Hello and welcome to Got it! :)
User 19/06/2018 - 11:54
Excelchat Expert 19/06/2018 - 11:55
I've read your request, and you seem to need help regarding setting up pivot tables. I somehow can't picture though how you wish for it to be formatted. Will it be okay if you share the file you're working on? Thanks! :)
User 19/06/2018 - 11:56
I can not share the file because it is confidential, but I can show you on the excel file
Excelchat Expert 19/06/2018 - 11:56
Oh okay, sure then. Please do. :)
User 19/06/2018 - 11:57
I want to make the combinations in the table F3:H6
User 19/06/2018 - 11:57
from the privottable A1:D7
User 19/06/2018 - 11:57
and then count how many times every one occurs
User 19/06/2018 - 11:58
but it must also count that 2,2,2 is twice 1,1,1
User 19/06/2018 - 12:00
and that 3,4,5 fits 3 times in 1,1,1 and 2 times in 2,2,2
User 19/06/2018 - 12:00
*once in 2,2,2
Excelchat Expert 19/06/2018 - 12:00
Is the data shown in the left table supposed to depict the data in the right table? :) I mean, if we reformat left table, it should display the right? :)
User 19/06/2018 - 12:01
no the table right shows just the combinations, it doesn't matter from which customer
Excelchat Expert 19/06/2018 - 12:03
Hmmm. I still can't picture it somehow. Can you perhaps provide the sample reference data from which we could come up with the table on the right? :)
Excelchat Expert 19/06/2018 - 12:05
Maybe we don't really need the pivot table on the left to create the one on the right. Or are you perhaps saying that the data from the pivot table on the left will be the reference data of the table on the right? :)
User 19/06/2018 - 12:06
i will show you what the original table looks like, maybe it is possible to create the table on the right from that
User 19/06/2018 - 12:07
that's what the original table looks like
User 19/06/2018 - 12:07
it seemed easier to create a pivot table and work from that
Excelchat Expert 19/06/2018 - 12:10
Wow, hmm. I still can't picture how we can arrive with the right table based on the data set you provided. I'm really sorry on this. Could you perhaps guide me on how we arrived on the some values on the right table using the data set you provided?
Excelchat Expert 19/06/2018 - 12:13
User 19/06/2018 - 12:13
the tabel on the right shows each combination of sizes: customer 1 orders 1 s, 1 m and 1 l, this is the first combination. customer 2 orders 2 s, 2 m and 2 l, this is a different combination, so it should also be in the table on the right. customer 3 order 3 s, 4 m and 5 l, this combination also goes in the table, because it is new. customer 4 orders 1 s, 1 m and 1 l, this combination is also ordered by customer one, so this one should be skipped.. and so on
Excelchat Expert 19/06/2018 - 12:16
Yes, I think I get it now. Apologies on this, but hopefully what I got is right. Basically, on the table to the right, you wish to write down all possible combinations from the pivot table on the left. After which, you want to count how many times that combination occurred from the pivot table, correct? :)
User 19/06/2018 - 12:16
User 19/06/2018 - 12:16
but not just count it exactly, it must also count that 2,2,2 is twice 1,1,1
Excelchat Expert 19/06/2018 - 12:17
Nice then, okay. For this, we will need the help of a helper column from which we will reference our combinations.
User 19/06/2018 - 12:17
I think I already tried that, combining the colums and then count, but then I can not count the way I want
Excelchat Expert 19/06/2018 - 12:18
Oh, that would be pretty tricky. But let's try doing that as well, after we do the fetching of combinations. :)
User 19/06/2018 - 12:18
Excelchat Expert 19/06/2018 - 12:18
Oh, what I'll be doing may be different though. :)
Excelchat Expert 19/06/2018 - 12:18
Okay, let me try to work on it for a sec. :)
User 19/06/2018 - 12:18
Excelchat Expert 19/06/2018 - 12:25
Okay, so if you look at the solution, the first step is to combine the s,m,l data from the pivot table in helper a.
Excelchat Expert 19/06/2018 - 12:25
Next, in helper b, we make use of a formula to fetch the unique combinations from helper a, and not leave the duplicate combinations.
Excelchat Expert 19/06/2018 - 12:26
Then, in our right table, we make use of VLOOKUP referenced to helper b to search for the equivalent values. :)
Excelchat Expert 19/06/2018 - 12:27
The hard part on this one will be counting the no. of combinations as how you've explained it. It will be a pretty long equation, but we can manage to do it. :)
User 19/06/2018 - 12:27
User 19/06/2018 - 12:28
I already came this far, but I thought that this way it wouldn't be possible to count
Excelchat Expert 19/06/2018 - 12:29
It's possible, but it will take long to set up actually. :) I believe though that you'll be able to get the logic of how we are to count after I draft the count on the first combination. :)
User 19/06/2018 - 12:29
it should also count that 3,4,5 is three times in 1,1,1, tot exactly, but that doesn't matter
User 19/06/2018 - 12:29
Excelchat Expert 19/06/2018 - 12:30
Yes. Noted on that. :)
User 19/06/2018 - 12:31
it is by the way also possible that the quantity per size is bigger than 10 or 100, wouldn't that be a problem
User 19/06/2018 - 12:31
for the separation
Excelchat Expert 19/06/2018 - 12:33
Now that we have commas on the helper a, that wouldn't be a problem.
User 19/06/2018 - 12:33
okay, I see
Excelchat Expert 19/06/2018 - 12:37
Excelchat Expert 19/06/2018 - 12:37
Nearly there. :)
User 19/06/2018 - 12:43
is it maybe also possible to do something like this in VBA
Excelchat Expert 19/06/2018 - 12:44
Yes, it's pretty easy to do it in VBA. Unfortunately, we're not allowed to offer VBA solutions in here. :(
User 19/06/2018 - 12:44
ooh okay
User 19/06/2018 - 12:46
because I need to do this twenty times, and it is quite some work like this
Excelchat Expert 19/06/2018 - 12:47
Yes, indeed. By using the helpers a and b, it will be easy to recreate the table from the right. However, the "counting" part will be the harder part to do.
Excelchat Expert 19/06/2018 - 12:53
Okay, I believe that would probably do it. Unfortunately, we're lacking out of time. I would have wanted to test this out further, but I'm confident with the solution ;)_
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.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc