EXCEL: Return Unique Values from Two Different Columns in Two Different Sheets in One Column in Anot-CodePudding

I want to return all unique values from TWO separate columns in TWO different sheets into ONE column in another sheet. For example, I have three sheets: IN, OUT, and UNIQUE.

Sheet "IN" has a table called "IN_TABLE" with the following columns: PRODUCT ID, QTY, SUPPLIER ID, DATE, and TIME.
Sheet "OUT" has a table called "OUT_TABLE" with the following columns: PRODUCT ID, QTY, CUSTOMER ID, DATE, and TIME.
Sheet "UNIQUE" is where I want to return all UNIQUE Product ID from both Sheets "IN" and "OUT".

Hence, if my "IN" Sheet looks like the following:

And my "OUT" Sheet looks like the following:

I want my "UNIQUE" Sheet to look something like this:

In Google Sheets, you can return the unique values for each sheets in Column C and D of Sheet "UNIQUE" and then use the =UNIQUE(FLATTEN()) function to do something like this, but I don't know how I will achieve this using Excel 365.

CodePudding user response：

Interesting question, you can't put two column arrays together with a semicolon like you can in Google sheets. Here is a possible workaround:

=LET(count1,COUNTA(In!A:A)-1,count2,COUNTA(Out!A:A)-1,seq,SEQUENCE(count1 count2),
UNIQUE(IF(seq<=count1,INDEX(In!A:A,seq 1),INDEX(Out!A:A,seq-count1 1))))

OUT

Shake it all about:

Explanation

In my example, there are four rows of data in the first sheet and five in the second sheet, with two entries (C & D) that appear in both sheets. The method is to count the number of rows in the first sheet, excluding the header (count1) and in the second sheet (count2). Then create a single-column array using Sequence, containing numbers 1-9:

Then feed this into Index to pick out the four entries in the first sheet (where the sequence = 1 to 4, i.e. less than or equal to count1) and the five entries in the second sheet (where sequence = 5 to 9, i.e. greater than count 1), subtracting count1 so that the values 1 to 5 are used, but now applying to the second sheet. The 1 is just to exclude the header rows in both sheets. Fortunately, in Excel 365, Index behaves nicely and gives you an array of values for a range of indices, unlike in previous versions where you had to use the 'if ({1} n(indices))' syntax to coerce it into producing an array. Then finally use Unique to exclude the duplicate values from the array.

CodePudding user response：

A slight modification to

Formula in F2:

=UNIQUE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,Table1[PRODUCT_IN],Table2[PRODUCT_OUT])&"</s></t>","//s"))

Or, if TEXTJOIN()'s limits are too narrow, based on my own answer here, you could use LET():

=LET(X,CHOOSE({1,2},Table1[PRODUCT_IN],Table2[PRODUCT_OUT]),Y,SEQUENCE(ROWS(X)*2),Z,INDEX(IF(ISERROR(X),"",X),1 MOD(Y,ROWS(X)),ROUNDUP(Y/ROWS(X),0)),UNIQUE(FILTER(Z,Z<>"")))