Active Member
July 22, 2023
Hi!
Thank you very much for all the information you share. It is incredibly valuable and I am very grateful.
I have a problem I don't know how to solve. I have a dataset with the sales of the different products in a market. It is a huge data set with thousands of products. I want to compare two specific brands. The products are categorised in category, subcategory, format, product type... and more fields.
I want to compare the products for each categorisation, and I will have different information for each product: volumen sales, value sales, growth, etc.
I have created a simplified data set to use as a example, with only a few products, with categories and subcategories.
I can create two pivot tables: one for each brand, but I want the different subcategories to be visually easy to compare. So I don't want the subcatories to get "mixed". If brand M has three products in the subcategory A1, and Bran C has only two products, in the third row brand M will have products of subcategory A1, but the third row for Brand C will have products of the next category.
Please, find attached the sample data set, where you can find the info in the DATA sheet, and the desired output in PIVOT TABLE.
As I mentioned, this is a sample dataset. My real dataset has thousands of lines and many brands. The info is actualised every month. I have the sales per week, and I use Power Pivot to create all the KPI I need.
I hope I have explained myself. I have office 365 in a PC.
Thank you very much for your help!
Moderators
January 31, 2022
Perhaps you have over-simplified the example. Would you really want to compare Product2 of Brand C with Product1 of Brand M? How would Excel know that these two are similar or perhaps even the same?
So, I assume that you will have products that are named the same for both brands and that you want to compare these.
Then, in stead of creating two separate pivot tables, each filtering on brand, put the Brand field in the Column area of the pt. I've done that in a pt in the DATA sheet. See attached, though I haven't gone far as to change the product names. You can do that yourself and refresh the pt. If that's not what you have in mind, please come back here.
Trusted Members
October 17, 2018
What Riny mentions is clear.
First, I would advice you when posting to tell us which version of Excel you're using.
My suggestion would be a table with a common product ID you use and a table with the products from the different providers with their codes and link these using your product ID
Active Member
July 22, 2023
Hello,
Thank you very much for your answers.
My excel version is 2408.
And yes, I don't have the same name for the products in the different brands, but I want to compare them anyway. Imagine we have a company in the Hair Care sector. The categories could be shampoo, conditioner and styling. Subcategories in shampoo could be coloured hair, anti dandruff, long hair and dry shampoo. I want to compare the performance of the products of the two main brands in this market. In each subcategory the two brands can have a different number of products, and even a brand can have no products for one of the subcategories. If brand A has 40 products and Brand B has 35 products, I dont want to have 75 rows with the information. For the different subcategories, I want similar products to share the row for easy comparison.
I have a similar example comparing the assortment of various retailers. If retailer A has 4 products for a subcategory, retailer B has 5 products and retailer C has 3 products, I would like to have the information in 5 rows, not in 12 rows. Eventhough the products are not the same in each retailer, for me they are comparable in the categorisation that I decide to use.
Thank you very much again!
Moderators
January 31, 2022
Understood. Then perhaps the attached file contains a solution that provides you with a partial solution. It summarises the 'scores' Category and Sub-category and lists the products by Brand by the number of their occurrence, not their name. This 'occurrence' number is calculated with COUNTIFS in an extra column of the data table. I called it "Prod". Though, it's not very pretty.
For instance, A-A1-C has one product, whereas A-A1-M has two, you will now see the first products lined up for both brands and the second product only for M on a new row.
Now, if you are interested in the actual product names of the products, press the expand button in the Prod column of the pivot table. Probably not ideal, as it still expands all Names of the same 'level'. That's the easiest I can come up with right now.
Active Member
July 22, 2023
Thank you again, but I really need the names of the product. In the real report, I even have the images of the products. This is a report that some people want printed.
I have tried reorganising the information with the functions: stack, tocol, wrapcol and those.... In the "pivot table" sheet, I make a new pivot table to count the products in the two brands. Then I get the max products for each subcategory.
I filter pivot table for brand C and use the expand function to add the empty rows I need, for each subcategory. (yes, I have found a use for the expand function ;)) Then I stack all the subcategories for brand C. I do the same for brand M.
Then in the sheet "Slow solution" I wrap the info as I need it, and apply the conditional formating.
I looks ok, but with my real data, my excel gets really really slow, and to make changes I have to deactivate automatic formulas and make them manual. Even like this, I keep getting the message "excel does not respond". Do this type of formulas use a lot of resources? Is there something that could be done? I get the data form another sheet and I connect it to the model.
Also the formula in T14: =HSTACK(T5#;T6#;T7#;T8#;T9#;T10#;T11#;T12#;T13#) I can only enter it manually, writing all the #... is there an easier way to do it? I have a couple of hundrens rows...
And when I get the new data next month, there are many things I have to revise manually to adjust all the formulas. Maybe there are more categories...
Would it be more efficient to do this with a macro?
I attach the new file.
Thank you again for all your help!
Moderators
January 31, 2022
Oh, but that makes it much more complicated with pictures, that is. I wish you hadn't simplified your example so much. Since you rarely get the correct answer if the reality is much more complex.
Thought about Power Query and got close to what you need, but PQ doesn't handle pictures and it's probably not suitable for the printing lay-out you need.
If you could share a more realistic file and show the end result you expect, perhaps there is a way. If it's VBA I'll pass, as that outside my comfort zone.
1 Guest(s)