Forum Discussion

Mmhoyle's avatar
Mmhoyle
Copper Contributor
Jan 03, 2024

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:B65,‘April 2024’!A3:B65, ‘May 2024’!A3:B65, ‘June 2024’!A3:B65, ‘July 2024’!A3:B65, ‘August 2024’!A3:B65, ‘September 2024’!A3:B65, ‘October 2024’!A3:B65, ‘November 2024’!A3:B65, ‘December 2024’!A3:B65))

 

It returns a list with a 0 in it. How would I get the 0 to not show up? I tried: 

 

=UNIQUE(FILTER(VSTACK(‘January 2024’!A3:B65,‘February 2024’!A3:B65,‘March 2024’!A3:B65,‘April 2024’!A3:B65, ‘May 2024’!A3:B65, ‘June 2024’!A3:B65, ‘July 2024’!A3:B65, ‘August 2024’!A3:B65, ‘September 2024’!A3:B65, ‘October 2024’!A3:B65, ‘November 2024’!A3:B65, ‘December 2024’!A3:B65),VSTACK(‘January 2024’!A3:B65,‘February 2024’!A3:B65,‘March 2024’!A3:B65,‘April 2024’!A3:B65, ‘May 2024’!A3:B65, ‘June 2024’!A3:B65, ‘July 2024’!A3:B65, ‘August 2024’!A3:B65, ‘September 2024’!A3:B65, ‘October 2024’!A3:B65, ‘November 2024’!A3:B65, ‘December 2024’!A3:B65)<>””)))

 

but it gave me #VALUE

excel
Formulas and Functions

3 Replies

Sort By
  • djclements's avatar
    djclements
    Bronze Contributor
    Jan 03, 2024

    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.

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jan 03, 2024

    = IF (     UNIQUE(VSTACK(‘January 2024’!A3:B65,‘February 2024’!A3:B65,‘March 2024’!A3:B65,‘April 2024’!A3:B65, ‘May 2024’!A3:B65, ‘June 2024’!A3:B65, ‘July 2024’!A3:B65, ‘August 2024’!A3:B65, ‘September 2024’!A3:B65, ‘October 2024’!A3:B65, ‘November 2024’!A3:B65, ‘December 2024’!A3:B65))     =   "" ,

    "",

    UNIQUE(VSTACK(‘January 2024’!A3:B65,‘February 2024’!A3:B65,‘March 2024’!A3:B65,‘April 2024’!A3:B65, ‘May 2024’!A3:B65, ‘June 2024’!A3:B65, ‘July 2024’!A3:B65, ‘August 2024’!A3:B65, ‘September 2024’!A3:B65, ‘October 2024’!A3:B65, ‘November 2024’!A3:B65, ‘December 2024’!A3:B65))

    )

     

    This works accordingly in my small example.

Resources