Search code examples
excelpivot-table

Highest quarterly (or other indexed) sum for items in Excel

I have a table in Excel with Quarter, City, Item, and Sales:

Quarter City Item Sales
2023-Q1 Smallville Reticulated Spline 15
2023-Q1 Smallville Red Llama 13
2023-Q1 Townsburg Reticulated Spline 35
2023-Q1 Townsburg Red Llama 27
2023-Q2 Smallville Reticulated Spline 7
2023-Q2 Smallville Red Llama 11
2023-Q2 Townsburg Reticulated Spline 39
2023-Q2 Townsburg Red Llama 37

The real table has 10 cities, 20 items, and currently data for 18 quarters, but this should give you the idea.

I want, for each Item, to find the best total quarterly sales, e.g.

Item Best Quarterly Total Sales
Reticulated Spline 50
Red Llama 48

The best Quarter for the Reticulated Spline was 2023-Q1, and for the Red Llama was 2023-Q2, but I don't need to know that.

How can I achieve this in Excel? I have tried creating a pivot table, but I have not been able to find a solution.

The long-term goal is for a different sheet, a template where you enter current figures, to compare those figures with the quarterly best for each Item, and for each Item show by how much the quarterly best was increased (0 if less than quarterly all-time-high for that Item).

I inherited the Excel-sheet, which was quite poorly built, so I am free to redo it if some other way to organize the data is better suited.

Solution

  • Here is one way of doing this using Excel Formulas this assuming as per the tags there is no Excel Constraints as well:

    enter image description here

    • Formula used in cell F2

    =LET(
     _Data, A2:D13,
     _QtrIt, INDEX(_Data,,1)&"|"&INDEX(_Data,,3),
     _Uniq, UNIQUE(_QtrIt),
     _Item, TEXTAFTER(_Uniq,"|"),
     _Sorted, SORT(HSTACK(_Item, 
     MMULT(N(_Uniq=TOROW(_QtrIt)),TAKE(_Data,,-1))),2,-1),
     UNIQUE(HSTACK(_Item,VLOOKUP(_Item,_Sorted,2,0))))

    If you like using LAMBDA() helper functions then using BYROW()

    =LET(
     _Data, A2:D13,
     _QtrIt, INDEX(_Data,,1)&"|"&INDEX(_Data,,3),
     _Uniq, UNIQUE(_QtrIt),
     _Item, TEXTAFTER(_Uniq,"|"),
     _Sorted, SORT(HSTACK(_Item,
     BYROW(_Uniq,LAMBDA(x,SUM((x=_QtrIt)*TAKE(_Data,,-1))))),2,-1),
     UNIQUE(HSTACK(_Item,VLOOKUP(_Item,_Sorted,2,0))))

    BTW if you have access to GROUPBY() function, which is presently in Office Insiders could try the following as well:

    =LET(
     _Data, SORT(DROP(GROUPBY(HSTACK(A2:A13,C2:C13),D2:D13,SUM,,0),,1),2,-1),
     _Uniq, UNIQUE(C2:C13),
     HSTACK(_Uniq,VLOOKUP(_Uniq,_Data,2,0)))