How to get unique values from 2 columns of the same data type and output in a single column?

[helpneeded2]

I am trying to create a sheet that will look at the ID numbers used on two different tables and will display a list of all ID's used. I am just outputting the list in a single cell via:

=SUBSTITUTE(TEXTJOIN(",",TRUE,value_idUsed#),",0","")

For obtaining the data from a single table I am using this:

=SORT(UNIQUE(table_jDataRaw[ID Number],FALSE),1)

The second column of data is in table_vDataRaw[ID Number]

Is there any way of having the UNIQUE range source be from 2 different columns, but output in a single column?

 

[Fluff]

Are the two tables on the same sheet?

 

[helpneeded2]

Are the two tables on the same sheet?

Yes they are, on different tabs.

 

[Fluff]

Ok, how about

=LET(a,table_jDataRaw[ID Number']],b,table_vDataRaw[ID Number],ra,ROWS(a),s,SEQUENCE(ra+ROWS(b)),SORT(UNIQUE(IF(s<=ra,INDEX(a,s),INDEX(b,s-ra)))))

 

[helpneeded2]

Ok, how about

=LET(a,table_jDataRaw[ID Number']],b,table_vDataRaw[ID Number],ra,ROWS(a),s,SEQUENCE(ra+ROWS(b)),SORT(UNIQUE(IF(s<=ra,INDEX(a,s),INDEX(b,s-ra)))))

Thanks a lot for this! While I understand the functions involved in this formula, unfortunately it is a little beyond the cognitive processing ability of my old brain. How could I modify the sort, so that it only uses values that are greater than 0 from either source table?

 

[Fluff]

How about

=LET(a,table_jDataRaw[ID Number']],b,table_vDataRaw[ID Number],ra,ROWS(a),s,SEQUENCE(ra+ROWS(b)),u,SORT(UNIQUE(IF(s<=ra,INDEX(a,s),INDEX(b,s-ra)))),FILTER(u,u>0))

 

[helpneeded2]

Excellent. Thank you again for your help. I'm certainly learning that the ability to assign variables and formulas via LET really seems to be one of Excel 365's more powerful advantages.

 

[Fluff]

You're welcome & thanks for the feedback.