CODEWITHSUNDEEP
google-sheetspivot-tabletabular
Luke
Luke

Reputation: 1830

Using "Max" summary in pivot table for data organized by non numerical hierarchy (Google Sheets)

In SheetA I've got a set of rows that have a "Rating Of Thing" column. A snapshot of SheetA. (The "Range Order" column is not part of the table in questions, but is used for the purposes of illustration to show the hierarchy of the things in the "Rating of Thing" column

The rating of thing column assigns ratings that belong to a non-numerical hierarchy. On a separate sheet (SheetB), using a pivot table, I'd like to display certain items in that sheet based on their position in that hierarchy. However, since the "Rating Of Thing" list is non-numerical, I'm getting results like the following. I get zero's for the ratings that are non-numeerical

I'd like to know how to create some sort of "on the go" mapping when the pivot table handles the "Rating of Thing" column so that I can find the max or highest rating for each month and display it in the table.

Link to the live sheet is here

Upvotes: 1

Views: 141

Answers (1)

player0
player0

Reputation: 1

try:

=INDEX(REGEXREPLACE(""&SORTN(SORT({TEXT(MONTH(A2:A&1), "00")&"×"&A2:A, C2:C}, 
 MATCH(C2:C, E2:E5, 0), 1), 9^9, 2, 1, 1), ".*×", ))

enter image description here

Upvotes: 3

Related Questions