EXCEL 365 Method -----------------------------
UNIQUE treats each row or column as a "tuple" and then compares each tuple, so when you put a matrix into it such as A1:C3, it looks either row-wise (e.g. {A1,B1,C1} vs {A2,B2,C2} ) or columns-wise (e.g. {A1;A2;A3} vs {B1;B2;B3} ) to determine if a tuple is unique. It does not look at each of the cells, so you have to multiplex the cells into a single column (or row) and then apply UNIQUE to that multiplexed range.
Here is a formula that you can put into L2 that will deliver a dynamic array of unique dates based on your XLS (thanks for sharing - that made this a lot easier to understand):
=LET( range, E3:I9,
Cols, COLUMNS( range ),
Rows, ROWS( range ),
iSeq, SEQUENCE( Rows * Cols,,0 ),
RowIndex, iSeq / Cols + 1,
ColIndex, MOD( iSeq, Cols ) + 1,
rawList, UNIQUE( INDEX( range, RowIndex, ColIndex ) ),
SORT( FILTER( rawList, ( rawList <> "" ) * ( rawList > 0 ) ) )
)
I set range = E3:I9 so that it includes up to Belgium in your output - not sure if that is what you are trying to achieve.
This LET formula takes in range as a variable and then it measures its dimension.
Modulation & Multiplexing
It then prepares the modulation so that range can be multiplexed into a single column. iSeq is the sequence that counts each cell in the range and that will be modulated vertically into RowIndex and horizontally into ColIndex. These are used to multiplex range by applying INDEX( range, RowIndex, ColIndex ).
Applying UNIQUE and Filtering
rawList applies UNIQUE to the multiplexed column to arrive at the unique values but... these will contain some undesirable outputs such as blank and 0 that need to be filtered, so the result is delivered as the filter of rawList. The final output is then sorted.
Also, with this formula, you can change your count of unique dates
formula in N2 to =COUNTA(L2#).
EXCEL 2013 Method (Plumhoff-Bartholomew) ----------------------------
Your MDHolidays is now going to become a set of helper cells using the same name that you have given it (let's not rename it for now or it will get confusing). We are going to use two more helpers to:
- Generate a set of unique dates
- that are free of zero-value dates
First, create two new named ranges:
k is an old-school sequence generator based on row
= ROW( MDHolidays ) - 1
unique.idx is boolean mask of k that eliminates duplicates, blanks and zero-dates:
= IF( ISNUMBER( MDHolidays ),
IF( ( MATCH( MDHolidays, MDHolidays , 0 ) = k ) * ( MDHolidays <> 0 ),
k ) )
These two can be created as named ranges without placing them anywhere. Just open Name Manager and create them as new names with the formulas above.
Now, you can create your output. You can test this in cell M2. It will be:
= IFERROR( INDEX( IF( MDHolidays > 0, MDHolidays ), SMALL( unique.idx, k ) ), "" )
If you like the result, then let's do a final clean-up. Rename MDHolidays to MCHolidays (merged, consolidated holidays) and then open Name Manager and replace your current dyn range formula for MDHolidays: =OFFSET(Calc!$L$2,0,0,COUNTA(Calc!$L:$L)-1) with your current (and very good) formula in L2 =IFERROR(SMALL(SelectedHolidays,ROW(INDIRECT("1:"&COUNT(SelectedHolidays)))),). This will virtualize those values so that they don't take up any cell-space.
Now you can reuse your old MDHolidays name by opening Name Manager, creating a new MDHolidays with the formula that is currently in M2 above. To complete the clean-up, change the formula in L2 to simply = MDHolidays and delete the formula in M2.
This method of teasing out UNIQUE was developed by Bernd Plumhoff and Peter Bartholomew.
