I have the attached file, where there are 2 “animals” tabs that I would like to extract unique lists from.

https://docs.google.com/spreadsheets/d/1rycpqCRCFJdqaWea624_BHvJnntB9WWZ/edit?gid=163716931#gid=163716931

As you can see, these tabs have dynamic row and column sizes (and users could add rows or add columns as well). As such I want to formula to be able to accommodate dynamic ranges across both (rahter than a fixed range).

I want the rules to stack horizontally, ignore blank columns and rows, and filter for uniques.

—

I’ve been down about every rabbit hold trying to figure this out (unique, filter, sort, vstack, hstack, index, match, tocol, torow, etc.) and can’t get all of the above conditions met with a single formula.

Please help!

Tried things like:

=FILTER(HSTACK(‘Land Animals’!E2:2,’Sea Animals’!E2:2),HSTACK(‘Land Animals’!E2:2,’Sea Animals’!E2:2)<>””)

But only gives me the header rows.

=unique({(‘Land Animals’!E2:P);(‘Sea Animals’!E2:P)})

But stacks on top of each other (instead of alongside each other), and does not filter for blanks.

—

See “Desired Results” in the attached for what I’m expecting/what I want.