Array formula to get unique values from two sheets

[neodjandre]

I have two sheets where in column A there are references in the format:

REF02392
REF03381
REF09345P
REF02465O
REF08573P
REF03472P

Some of the references are duplicate in both sheets and some only exist in one of the sheets.

I am looking for an array formula that will extract only the unique reference values from both sheets in a combined list in a new 3rd sheet.

Is this possible?

many thanks in advance,
Andrew

 

[JW00]

This is a named range & formula solution to what I think you require.

Create named ranges for your 2 columns:

List_1
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)
List_2
=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A),1)

On your 3rd sheet use the following formula:

=SORT(UNIQUE(FILTER(IFERROR(INDEX(List_1,SEQUENCE(ROWS(List_1)+ROWS(List_2))),INDEX(List_2,SEQUENCE(ROWS(List_1)+ROWS(List_2))-ROWS(List_1))),IFERROR(INDEX(List_1,SEQUENCE(ROWS(List_1)+ROWS(List_2))),INDEX(List_2,SEQUENCE(ROWS(List_1)+ROWS(List_2))-ROWS(List_1)))<>0)))

This is dynamic so the list will increase automatically, one of the guru's maybe able to give you a more concise solution or a vba solution.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Tom.Jones]

Hi,

Create name range (see *JW00 answer) then use this formula:

=IFERROR(IFERROR(INDEX(list_1, MATCH(0, COUNTIF($A$1:A1,list_1), 0)), INDEX(list_2, MATCH(0, COUNTIF($A$1:A1, list_2), 0))),"")
it's an array formula

 

[neodjandre]

hello updated platform and office - thanks.. I will test the answers and report back

 

[neodjandre]

ok this works as expected using Tom.Jones answer. It seems that I have to add a 3rd sheet now (list_3) - can't get my head around the addition to the array formula. Would you mind helping me with this please? many thanks for the prompt and great answer.

 

[Fluff]

Try it like

=IFERROR(IFERROR(INDEX(list_1, MATCH(0, COUNTIF($A$1:A1,list_1), 0)), IFERROR(INDEX(list_2, MATCH(0, COUNTIF($A$1:A1, list_2), 0)),INDEX(list_3, MATCH(0, COUNTIF($A$1:A1, list_3), 0)))),"")

 

[Peter_SSs]

hello updated platform and office

Thanks for that.

With 2019, if you wanted to try without the named ranges and you will have less than, say (I'm guessing a bit here) about 1,500 references like the samples in total then you could try this.
As written I have allowed for up to 500 rows of data on each sheet.
One other slight difference with this method is that if there may be any blank cells among the data it should handle that, whereas the named ranges using COUNTA may result in some references missing from the final list.

neodjandre_1.xlsm
	A
1	Final List
2	REF02392
3	REF03381
4	REF09345P
5	REF02465O
6	REF08573P
7	REF03472P
8	REF03382
9	REF03383
10	REF03384
11	REF08573Q
12	REF9999
13
14
15
Sheet4

Cell Formulas
Range	Formula
A2:A15	A2	=IFNA(INDEX(FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,Sheet1!A$1:A$500,Sheet2!A$1:A$500,Sheet3!A$1:A$500)&"</c></p>","//c"),MATCH(0,COUNTIF(A$1:A1,FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,Sheet1!A$1:A$500,Sheet2!A$1:A$500,Sheet3!A$1:A$500)&"</c></p>","//c")),0)),"")