Google sheets - Collecting unique values from several sheets?

1
votes

I'm in the need of collecting unique values from a specific range in several sheets. Is it possible to combine functions in order to do this?

All sheets look the same regarding column structure.

As of now, my function collects from one sheet and it looks like this:

=unique(filter('Sheet1'!C4:C1000,'Sheet1'!C4:C1000<>""))

This collects unique values from Sheet1 from C4 to C1000 and excludes empty cells. This works awesomely, but I have more sheets that I'd like to merge values from. Any idea?

1
google-sheets

1 Answers

1
votes

  1. Basic idea is to combine data, first with help of {}:

    = {sheet1C4:C1000;sheet2C4:C1000;sheet3C4:C1000}

  2. The next step is to get rid of empty cells. To do it only once, use query:

    = query({sheet1C4:C1000;sheet2C4:C1000;sheet3C4:C1000}, "select Col1 where Col1 <> ''")

  3. And then grab uniques/ The final formula will look like:

= unique (query({sheet1C4:C1000;sheet2C4:C1000;sheet3C4:C1000}, "select Col1 where Col1 <> ''"))

By the way, query string may be shortened to this "where Col1 <> ''" will also work