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]

Roughly how many rows of data are you looking at on both sheets?

 

[excelos]

Roughly how many rows of data are you looking at on both sheets?

It should be around 1,000 on one and 1,000 on the other but it may change! Though no much more.

 

[Fluff]

What sort of data do you have in those columns? Could you post examples.

 

[excelos]

What sort of data do you have in those columns? Could you post examples.

It's 4-5 character codes like:

TRHERT
BSFNH
ERGHY
RRWOY
...

Two of these columns in two different worksheets. Some values are common between the two lists, some exist in the one and some exist only in the other column/list.

 

[Fluff]

Ok, with a max of 5 characters try

=UNIQUE(FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,UNIQUE(Sheet1!A2:A1000),UNIQUE(Sheet2!B2:B1000))&"</m></k>","//m"))

but if you extend much above 2000 cells in total, it may fail

 

[Eric W]

You could do something like this:

=UNIQUE(IF(SEQUENCE(COUNTA(Sheet6!B:B,Sheet8!A:A))<=COUNTA(Sheet6!B:B),INDEX(Sheet6!B:B,SEQUENCE(COUNTA(Sheet6!B:B,Sheet8!A:A))),INDEX(Sheet8!A:A,SEQUENCE(COUNTA(Sheet6!B:B,Sheet8!A:A))-COUNTA(Sheet6!B:B))))

Text length and number of cells shouldn't matter, but it requires no empty cells in the lists.

 

[excelos]

Thanks I will try these suggestions!

I also got another idea:

If I do an OR (lookup list1 to list2, lookup list2 to list1), will it work? Or something like that?

 

[Fluff]

I'd go with Eric's idea.

 

[excelos]

Thanks, I will use it but can you break down the steps please? how it works?

Also, I could also mirror list1 in one column, then mirror list2 in the next column but offset it so that it starts at the end of column one. Then, merge the two lists in the third column (if(A:A="",B:B,A:A), so that list2 is below list1 and apply the UNIQUE function there.

However, I am struggling with OFFSETing:
=OFFSET('2'!$C$4:OFFSET('2'!$C$4,COUNTA('2'!$C:$C)-2,0),COUNTA($B:$B),0)

Any idea?

The 2!C:C contains the list2, the B:B is the mirror of list1 but it does not offset properly! Any idea?