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
Hello
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
Ohhh,,,
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
yes
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
great
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
oke
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
great
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
*not
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
Ooopz
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 ;)_
