Get UNIQUE values from two columns in different worksheets

[excelos]

Hello

I want to get/spill the unique values from two columns in different worksheets.

Unfortunately UNIQUE(A!A:A,B!B:B) does not seem to work.

I also tried TEXTJOIN to create an array of strings and it does not work either.

I checked CHOOSE({1,2},A!A:A,B!B:B) but I still get a 2D array or if I use CHOOSE({1;2},A!A:A,B!B:B) I get weird results.

I googled 'merge/append columns', 'convert 2D to 1D array' etc with no much luck.

Any idea?

Thanks!

 

[Fluff]

Personally I avoid using offset as much as possible as it's volatile & could slow your workbook down.

 

[Eric W]

The basic gist of my formula is that it combines the 2 lists into an internal array with a single column, as you've described wanting to do using a helper column on the sheet. So the formula figures out how many elements are in the first list using COUNTA(range1), and how many elements are in the second list using COUNTA(range2). It then creates an array from 1 to the total of both lists SEQUENCE(COUNTA(range1,range2)). If this sequence array is <= the number of elements in the first list, it does an INDEX(range1,sequence). If the sequence array is higher, it does an INDEX(range2,sequence-# of elements in range1), so essentially it starts at the top of list 2. Now that all the items are in a single column array, you can use UNIQUE.

This is probably better than your idea of using a helper column. I could create a helper column on the sheet using AGGREGATE and INDEX (best to avoid OFFSET, like Fluff said), but you'd end up with thousands of array formulas that would slow down your sheet. The biggest flaw with my formula is that it doesn't handle empty cells in the middle of your ranges. Would you have any of those?

 

[excelos]

It is quite certain that there won't be gaps in the lists because they are results of array formulas where blanks have been filtered out, thanks!

Could you tell me how to do it with a helper column and OFFSET and a helper column and AGGREGATE/INDEX please?

Thanks!

 

[Eric W]

What are the array formulas you're using?