In Google Sheets, how can I consolidate a table with partially duplicated entries to only return the most relevant information?

Question

I have a spreadsheet (see attached) in which I want to indicate whether various criteria I am checking for are met. Each entry into the list will have a category and subcategory, and there may be multiple entries with the same category/subcategory. If an entry meets the criteria, it will be ranked 1, 2, or 3, with 1 being better than 2, and 2 being better than 3. See attached link, or the first sheet, titled "All Entries".

On the "Summarized Entries" sheet I'm attempting to consolidate all of these different criteria such that only the highest-ranked values for each criteria are shown i.e.

if any of the entries for the given category and subcategory have a filled-in cr1 with a value of 1, consolidated value is 1;
else if any of the entries for the given category and subcategory have a filled-in cr1 with a value of 2, consolidated value is 2;
else if [...] cr1 has a value of 3, consolidated value is 3;
else consolidated value is "N", as no entry for the given category and subcategory met the given criteria.

for the given spreadsheet, this should look like this:

to accomplish this, I attempted the following formula:

=ARRAYFORMULA(
IF('All Entries'!$A3:$A&'All Entries'!$B3:$B&'All Entries'!C3:C=($A4:$A&$B4:$B&"1"),"1", 
if('All Entries'!$A3:$A&'All Entries'!$B3:$B&'All Entries'!C3:C=($A4:$A&$B4:$B&"2"),"2", 
if('All Entries'!$A3:$A&'All Entries'!$B3:$B&'All Entries'!C3:C=($A4:$A&$B4:$B&"3"),"3",
if($A4:$A="",                                                                       "",
                                                                                    "N")))))

however, it's giving me the following result:

it appears that as soon as there is a 2nd entry in the original table that has the same category and subcategory, it stops reading anything past that. I'm relatively new to array formulas in Google sheets so I assume I'm missing something simple but just haven't been able to figure it out and my stack overflowing hasn't been successful.

can you explain "most relevant values for each criteria are shown" in more detail ? — player0
sorry, that wasn't very well described. There are various rankings for each of the criteria i.e. a ranking of 1 is more important than a ranking of 2, which is more important than a ranking of 3, which is more important than not fulfilling the criteria at all. I want the summarized entry to show only the most important ranking for each criteria, for each combination of category and subcategory. Hopefully this clarifies, and I'll edit the main question to hopefully make this part of the question more clear. — codydegen

player0 player0 · Accepted Answer · 2021-07-02T21:17:19

try:

=ARRAYFORMULA(IF(A4:A="",,IFNA(IF(IFNA(IF(
 VLOOKUP(A4:A&B4:B, SORTN(SORT({'All Entries'!A3:A&'All Entries'!B3:B, 'All Entries'!C3:G, ROW('All Entries'!C3:G)}, 7, ), 9^9, 2, 1, ), {2,3,4,5,6}, )>
 VLOOKUP(A4:A&B4:B, SORTN(QUERY({'All Entries'!A3:A&'All Entries'!B3:B, 'All Entries'!C3:G, ROW('All Entries'!C3:G)}, 
 "where not Col7 matches '"&TEXTJOIN("|", 1, INDEX(SORTN(SORT({'All Entries'!A3:A&'All Entries'!B3:B, 'All Entries'!C3:G, ROW('All Entries'!C3:G)}, 7, ), 9^9, 2, 1, 1),,7))&"'
  order by Col7 desc"), 9^9, 2, 1, 1), {2,3,4,5,6}, ),
 VLOOKUP(A4:A&B4:B, SORTN(QUERY({'All Entries'!A3:A&'All Entries'!B3:B, 'All Entries'!C3:G, ROW('All Entries'!C3:G)}, 
 "where not Col7 matches '"&TEXTJOIN("|", 1, INDEX(SORTN(SORT({'All Entries'!A3:A&'All Entries'!B3:B, 'All Entries'!C3:G, ROW('All Entries'!C3:G)}, 7, ), 9^9, 2, 1, 1),,7))&"' 
  order by Col7 desc"), 9^9, 2, 1, 1), {2,3,4,5,6}, ),
 VLOOKUP(A4:A&B4:B, SORTN(SORT({'All Entries'!A3:A&'All Entries'!B3:B, 'All Entries'!C3:G, ROW('All Entries'!C3:G)}, 7, ), 9^9, 2, 1, ), {2,3,4,5,6}, )))="",IF(
 VLOOKUP(A4:A&B4:B, SORTN(SORT({'All Entries'!A3:A&'All Entries'!B3:B, 'All Entries'!C3:G, ROW('All Entries'!C3:G)}, 7, ), 9^9, 2, 1, ), {2,3,4,5,6}, )="",
 VLOOKUP(A4:A&B4:B, SORTN(QUERY({'All Entries'!A3:A&'All Entries'!B3:B, 'All Entries'!C3:G, ROW('All Entries'!C3:G)}, 
 "where not Col7 matches '"&TEXTJOIN("|", 1, INDEX(SORTN(SORT({'All Entries'!A3:A&'All Entries'!B3:B, 'All Entries'!C3:G, ROW('All Entries'!C3:G)}, 7, ), 9^9, 2, 1, 1),,7))&"'
  order by Col7 desc"), 9^9, 2, 1, 1), {2,3,4,5,6}, ), 
 VLOOKUP(A4:A&B4:B, SORTN(SORT({'All Entries'!A3:A&'All Entries'!B3:B, 'All Entries'!C3:G, ROW('All Entries'!C3:G)}, 7, ), 9^9, 2, 1, ), {2,3,4,5,6}, )),IF(
 VLOOKUP(A4:A&B4:B, SORTN(SORT({'All Entries'!A3:A&'All Entries'!B3:B, 'All Entries'!C3:G, ROW('All Entries'!C3:G)}, 7, ), 9^9, 2, 1, ), {2,3,4,5,6}, )>
 VLOOKUP(A4:A&B4:B, SORTN(QUERY({'All Entries'!A3:A&'All Entries'!B3:B, 'All Entries'!C3:G, ROW('All Entries'!C3:G)}, 
 "where not Col7 matches '"&TEXTJOIN("|", 1, INDEX(SORTN(SORT({'All Entries'!A3:A&'All Entries'!B3:B, 'All Entries'!C3:G, ROW('All Entries'!C3:G)}, 7, ), 9^9, 2, 1, 1),,7))&"'
  order by Col7 desc"), 9^9, 2, 1, 1), {2,3,4,5,6}, ),
 VLOOKUP(A4:A&B4:B, SORTN(QUERY({'All Entries'!A3:A&'All Entries'!B3:B, 'All Entries'!C3:G, ROW('All Entries'!C3:G)}, 
 "where not Col7 matches '"&TEXTJOIN("|", 1, INDEX(SORTN(SORT({'All Entries'!A3:A&'All Entries'!B3:B, 'All Entries'!C3:G, ROW('All Entries'!C3:G)}, 7, ), 9^9, 2, 1, 1),,7))&"'
  order by Col7 desc"), 9^9, 2, 1, 1), {2,3,4,5,6}, ),
 VLOOKUP(A4:A&B4:B, SORTN(SORT({'All Entries'!A3:A&'All Entries'!B3:B, 'All Entries'!C3:G, ROW('All Entries'!C3:G)}, 7, ), 9^9, 2, 1, ), {2,3,4,5,6}, ))), "N")))

In Google Sheets, how can I consolidate a table with partially duplicated entries to only return the most relevant information?

1 Answers