Log In
Register
Home
New Member
October 1, 2019
Offline
Hi,
I have a table that has multiple columns listing store names, and multiple rows listing Product names, and the sales data in the table for models sold by each store. I have created a formula successfully to list out the highest to lowest sales for a defined model, but I am having trouble indexing the store to which each listed Qty was sold by.
My formula to find High to low QTY:
={IFERROR(IF(MATCH($P$1,$A:$A,0),LARGE(INDEX($A$1:$N$15,MATCH($P$1,$A:$A,0),$B$2:$N$15),ROW(1:1))),"")}
I have tried multiple formula's to list the corresponding store to the returned Qty Value (bearing in mind the sequence of stores returning the same value, and not just indexing the same store name for multiple same Qty's).
File is attached.
Any help is hugely appreciated.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Doug,
There is no easy answer with formulas, this usually happens when the data structure is not tabular.
If the data is properly structured, formulas are much simpler.
See the attached file for a solution, I converted the initial data structure to a tabular structure using power query, then I inserted a pivot table sorted descending to display top products by store. You can add a slicer for Product or store, this will simplify data visualization.
Reports are much more flexible and powerful using built in report tools than using formulas, if the structure is well formed.
New Member
October 1, 2019
Offline
Hi Catalin,
Thanks for the reply.
As the data i receive is a lot bigger than the sample section I supplied, and that I have it linked to several pre configured reports, I was trying to get the solution without having to add extra tables and manipulation to drill down.
Thanks for your effort.
Regards,
Doug.
VIP
Trusted Members
June 25, 2016
Offline
Hi Doug
Try this: In cell Q3 enter this array formula (CTRL+SHIFT+ENTER) and drag down to as many rows as needed.
=INDEX($B$1:$N$1,SMALL(IF(INDIRECT("B"&MATCH($P$1,A:A,0)&":N"&MATCH($P$1,A:A,0))=P3,COLUMN(INDIRECT("B"&MATCH($P$1,A:A,0)&":N"&MATCH($P$1,A:A,0)))-MIN(COLUMN(INDIRECT("B"&MATCH($P$1,A:A,0)&":N"&MATCH($P$1,A:A,0))))+1),COUNTIF($P$3:P3,P3)))
Hope this helps
Sunny
1 Guest(s)
