How to pull a single instance of duplicates into another sheet?

shrobert

I have a base data table that looks like this:

I need to create a summary table that looks like this:

When I use JOIN(COLLECT in the summary table, it pulls in "AL K-5" five times. I need a way to just pull in the first instance of any duplicate from the Concat column, when e.g. Year = 2022 and Discipline = "Reading"

I have tried to create helper columns where I use COUNTIF and RANKEQ to identify which instances are the first and which are duplicates, but I can't figure out a function that will pull in the instance to another sheet if the rank is 1.

I feel like this should be really simple, but I'm stumped. Any ideas?

ericncarr

Hi @shrobert ,

The easiest way to do this is to use an index(collect()). You create an index of the collected values and specify that you just wanted the first instance in the index.

=INDEX(COLLECT({Concat Column reference}, {Year Col Reference}, =[Column2]1, {Program Column Reference}, =[Column1]2 ), 1)

This collects anything in the Concat column on the other sheet by comparing the reference to the year column on the other sheet with the year in the first field of column 2 and the Program (Reading, Literature, etc.) with the program you have in Column 1. The 1 in the column specifies you only want the first instance. I believe you can drag this formula down to Literature and Math and across to column 3 to get 2023 as well.