Dynamic Unique values for Dashboard

Raul F Mtz

Goal: I want to dynamically extract "Top 5 Customers by Spend".

Context:
-Sales Dept. uses a sheet to register future projects. Important columns: "Customer Name" and "Spend".

Considerations:
- "Customer Name" could appear more than 1.
-In rare occasions, two "Customer" can have the same spend, so both should be considered in "Top 5".

So far:
I tried having a helper sheet with:
a) Having a HELPER COLUMN to count COLUMN NAMES
b) Having a 2HELPER COLUMN to SUMIF to add SPEND by COLUMN NAMES

…but I cannot do it both DYNAMICALLY and COLUMN FORMULA.


I almost lost all hope. I just want to double check if someone found another solution.

Paul Newcome

There are a number of solutions for this. Are you able to provide some screenshots for context?

Raul F Mtz

This is a dummy sample, please note:
- SupplierD & H have the same value
- Suppliers can be repeated

I have not figure out an option that allows:
- Update range automatically
- Sort Top 5 dynamically

Paul Newcome

I can't see the column name that contains the numbers 1 - 5, so in this example I will just call it "Rank".

In the [Column6], you would use this formula:

=LARGE(Spend:Spend, Rank@row)

Then in [Column5] you would use:

=INDEX(COLLECT([Supplier Name]:[Supplier Name], Spend:Spend, @cell = [Column6]@row), COUNTIFS(Spend:Spend, @cell = [Column6]@row, Rank:Rank, @cell <= Rank@row))

Raul F Mtz

This is not the correct approach for the following:
a) SUMIFS: The goal is to dynamically extract TOP SUPPLIERS BY SPEND (overall). Then, it must be SUMIFS instead of LARGE.
b) Unique values: I tried various approaches but nothing that can handle NEW VALUES AUTOMATICALLY (cannot make them column formulas) or figure out a helper solution with no manual interaction.


In Excel terms it is something like "MATCH + INDEX with multiple results" or "Unique values + Xlookup" or even the result obtained with "Pivot Table > Organize top first". Anyway, thanks for trying.

Note: I manage various workspaces for different departments. No backup. I was trying to have simple dashboards per team. Showing "Top X" updated 24/7 is a challenge if you need to manually drag a formula, or manually apply filters.

Paul Newcome

Did you try my solution?

Raul F Mtz

Yes, is not the correct approach.
Thank you for the effort. Maybe I didn't explain it correctly.

SUM not LARGE

This screenshot shows the desired result. For 'SupplierA' shows the TOTAL SUM (5+8+2) = 15.
The solution proposed uses LARGE, not SUMIF/SUMIFs. Hence the second part of the solution doesn't work.

a) Applying LARGE instead of SUMIF

It leads to errors.

The objective:
- Extract top values (as summary)
- Extract names (related to previous step)
- Dynamically.

Personally, I almost achieve the desired goal using various column helpers but can't solve:

=COUNTIF([Customer Name]$1:[Customer Name]@row, [Customer Name]@row)
I cannot think of a workaround for this formula that is suitable for 'Convert to Column Formula'.

**I use this helper step to extract UNIQUE NAMES … > then COLLECT values > then Top VALUES > then Reorder Highest first > then MATCH values with UNIQUE NAMES.

I guess I'll continue to drag formulas, or using Excel.
Thanks for trying.

Paul Newcome

Getting the COUNTIF for row number as a column formula requires an auto-number column. Then the column formula for outputting the row numbers is

=MATCH([Auto-Number]@row, [Auto-Number]:[Auto-Number], 0)