Nov 25 2022 04:09 AM
Hi there,
I'm having trouble writing a formula, and wonder if anyone can point me in the right direction.
I want to produce a unique list of Names based on a fixed date (Year End) falling between two dates in a table. I think I can do this with Index, Match and Unique, but I'm struggling to get them all working together. I'd initially like them ordered alphabetically, but may want to add in an aggregate value from the table as well, but that's a secondary issue.
The Table, its called Table2, has 125 rows and columns A through to AE, so 31.
The look up date will be in Cell E3 on a separate worksheet
The text I want to return in the unique list is in column D or 5 (in terms of the column number) of the table
The start date is in Column Y or 25
The finish date is in Column Z or 26
and the column with the value I might want to aggregate is X or 24.
I keep getting a spill error and not sure how to debug it, I've likely got stuff in the wrong order.
Many thanks if someone can point me in the right direction.
Adam
Nov 25 2022 05:00 AM
Column D (4) or E (5) ?
Try a formula like this, using the field names from the table
=SORT(UNIQUE(FILTER(Table2[ColumnD], (Table2[ColumnY]<=E3)*(Table2[ColumnZ]>=E3))))
Nov 25 2022 10:58 AM
Nov 25 2022 11:14 AM
Thanks, I managed to work it out in the end. I did it in two steps producing an intermediary array which will be helpful to check certain data before the final list is created.
Turns out because of the wider dataset I didn't really need to filter by date as the data I wanted in terms of year to end was already in a particularly column (X) so X being greater than one, was the only bit of filtering I needed. Main List is the name of the workbook with the data in, its also a table, but this referencing seemed to work fine.
=SORT(FILTER(FILTER('Main List'!A:AE,'Main List'!X:X>1,),{1,0,0,0,1,0,1,0,0,0,1,1,1,0,0,0,0,0,0,0,0,0,0,1,1,1,0,0,0,0,0}),7,-1)
=UNIQUE(SORT(C7:C100))
C7 being the start of the list I wanted unique values for sorted alphabetically.
Thanks!