INDEX COLLECT MIN (again)

LesGos

Had a follow up question to my previous question located here.

—

I was using the original sheets, which I fixed with the solution from that thread, as a template. Today, I copied them over to essentially create the same system for a different LOB. I checked the box that updated all formulas to the new references.

Now, in this list, I'm ending up with duplicates of the same Initiative.

List -

Master Sheet -

I think I need to include a way to use the Row ID with the INDEX formula, because for all of the Due Dates that have -10, it's just pulling the first one on the Master with that value (Master was reorganized slightly for this screenshot). Or maybe add DISTINCT in there somewhere?

Current formulas are:
=IFERROR(IF($[Column2]$1 = "All", INDEX(COLLECT({A&E Project Master - Initiative}, {A&E Project Master - Days to Due}, @cell = SMALL({A&E Project Master - Days to Due}, #)), 1), INDEX(COLLECT({A&E Project Master - Initiative}, {A&E Project Master - Days to Due}, SMALL(COLLECT({A&E Project Master - Days to Due}, {A&E Project Master - Year}, $[Column2]$1), #)), 1)), "")

With the only change being the range to collect. # is the number within the Top 10 list.

How can I add additional criteria to get it to pull the correct information?

jmyzk_cloudsmart_jp

Hi @LesGos

In your -List or my A&E Project Master sheet shown at the bottom, there are duplicate entries, like your 'Destination Landscape, 174' or my 'Initiative 1, -10'.

So, to collect only the representative initiatives, we can use the DISTINCT function to get the unique 'Days to Due.'

https://app.smartsheet.com/b/publish?EQBCT=90763e8226da432682315f6a92da88cd

=INDEX(
  COLLECT({A&E Project Master - Initiative}, 
    {A&E Project Master - Days to Due}, SMALL(DISTINCT({A&E Project Master - Days to Due}), [#]@row))
, 1)

https://app.smartsheet.com/b/publish?EQBCT=50333eed42904ce78f0ac17bf8af5c66