Displaying all columns following the first instance of a row value

Scott Hively

Hello all, I have a tricky question to ask and I'll do my best to explain it. I have a sheet that will continue to grow in length and I need to build a summarization sheet or report from it:

I'd like a new sheet or report to provide some of a row's column data associated with just the FIRST instance of a value from the ISA field. Said another way, the new view would show only one row for Tammy Faye, one for Gus Johnson, and so on.

I assume there's a way to get this done but I don't have the skillset. :)

Scott Hively

Hey I figured out a way to make it work. COLLECT got me started down a path that worked. Appreciate the look at this.

Dale Murphy

@Scott Hively Review the use of the Distinct function, and how to cross reference from the summary sheet to the detailed sheet. Using Distinct can help you catch just one instance of the contents of a certain column (ISA in your case). Here is how I use it in one situation, to help with the syntax:

=IFERROR(INDEX(DISTINCT(COLLECT({LOMDelivery Range 1}, {LOMDelivery Range 2}, <>"")), Check@row), "")

dm

Scott Hively

Thanks @Dale Murphy. I'm unfamiliar with DISTINCT but it seems to retrieve unique instances of values. In my case, I want to identify the first instance of a value appearing, regardless of whether it is unique or not.

I could very well be missing something though. Sorry.

Scott Hively

Hey I figured out a way to make it work. COLLECT got me started down a path that worked. Appreciate the look at this.