0

excel form photo

I got an excel with data that shows every client and how many times has downloaded a certain product. Some clients have downloaded multiple products separated by comma. I would like to find, which product has the most downloads and how i ll sort em descending so i can separately see something like -> :

 abc -s9  450
 dds -s3  397

etc.

i m going to get this kind of report every month so every month i need to do the same process so i can see the top products. Any help would appreciate. TY

i have tried to separate the downloads column when separated by comma but it creates more problems than it solves.

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
nkow
  • 1
  • 1
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Aug 18 '23 at 15:10

1 Answers1

1

Hi I understand that in column "Downloads", you are looking to split the 2 products twyt-s2=9 & abc-s9=68. I suggest you to use power query and split this column, initially every time that the comma is appearing and then second time with the left "(" (As long as you do not have in the product code any "(" or ",").

Then, you could unpivot the columns in order to have in one column the product code and in the other column the amount of downloads. Finally, to group them to find the total and descending them. So, the idea is to have a table where you will save the new data and with just a refresh to have the result instantly. The above is just a summary, you need more steps to reach the result.

If you are not familiar with power query, I suggest you read some articles or add a sample of the table here to go through.

enter image description here

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
nik
  • 11
  • 3