Forum Discussion
Using UNIQUE, FILTER, and VSTACK together
Hey everyone, I’m trying to combine data from 12 different sheets on the same workbook and ignore blank cells. If I use: =UNIQUE(VSTACK(‘January 2024’!A3:B65,‘February 2024’!A3:B65,‘March 2024’!A3:B6...
Mmhoyle The #VALUE! error is returned because you are attempting to use the <>"" criteria with a two-dimensional array (2 columns). You need to apply the criteria to either one column or the other (or both separately). The TAKE function can handle this quite nicely. For example:
=LET(
arr, UNIQUE(VSTACK('January 2024:December 2024'!A3:B65)),
FILTER(arr, TAKE(arr,, 1)<>"")
)
- OR -
=LET(
arr, UNIQUE(VSTACK('January 2024:December 2024'!A3:B65)),
FILTER(arr, TAKE(arr,, -1)<>"")
)
- OR -
=LET(
arr, UNIQUE(VSTACK('January 2024:December 2024'!A3:B65)),
FILTER(arr, NOT((TAKE(arr,, 1)="")*(TAKE(arr,, -1)="")))
)
Note: the use of 'January 2024:December 2024'!A3:B65 to create a 3-dimensional range assumes your monthly worksheets are arranged in order from left to right, without any additional non-sequential worksheets in between.