Find max counts in subcategory with condition by every category

[subhajits11]

Hi Trixians,

I am stuck in this very simple thing but somehow not able to solve,Any help!

Question is , which are the shops have maximum subcategory sales are candles?

Data:

Shop name	sold item	No of item sold
shop1	candle	12
shop1	fish oil	11
shop1	buddha banner	10
shop1	pillow	3
shop1	shirt	5
shop2	candle	3
shop2	fish oil	4
shop2	buddha banner	12
shop2	pillow	45
shop2	shirt	3
shop3	candle	12
shop3	fish oil	19
shop3	buddha banner	24
shop3	pillow	10
shop3	shirt	9
shop4	candle	12
shop4	fish oil	19
shop4	buddha banner	24
shop4	pillow	10
shop4	shirt	9
shop5	candle	12
shop5	fish oil	19
shop5	buddha banner	24
shop5	pillow	10
shop5	shirt	9

Thanks for the input,in advance.

[jdunkerley79 · Accepted solution]

I would suggest:

First sort the data so it is by Shop and then Descending within it

You can then use a sample tool to pick the first item, grouped by Shop

Finally a filter to pick just candles

Sample attached