Unique Values from two standalone lists

[cpadilla0024]

Hi,

I have been wrestling with this one for a few days and cannot seem to find an automated/dynamic way to handle this. I attached a basic sample of what I am trying to do. Basically, the form sheet (A & B columns) are filled out by our customers. The unique key is generated via a formula in column C. The data is sheet is from our system and needs to be in the background for reference.

What I ultimately need is a dynamic way to create a unique list (from form and data) on the unique sheet of the column C information. Duplicates are enabled on each sheet also so I did plug them in on row 20 of each sheet. I tried ways to do use the UNIQUE function with no luck. I tried using UNIQUE on separate sheet and leveraging Power Query with no luck. And unfortunately Excel does not carry the FLATTEN function that Google Sheets has. But I need this in an Excel file. I appreciate any help or insight on this one. It is eating me alive. Thanks!

 

[p45cal]

In the attached
1. A simple Power Query solution at cell A1 of the unique sheet.
2. A formula solution at cell E2, probably more convoluted that it need be because I half-inched it from a thread I recently posted at and yours only deals with single column source data (see https://chandoo.org/forum/threads/count-elements-in-array.47278/post-280729 ). If I get time to simplify it I'll post again here.
edit: since posting I noticed that the formula a cell E2 fails if the two source ranges are different sizes, so I've updated the file attached with another formula at cell G2 which copes with different size ranges. This isn't simpler, just different. I've left the table on the data sheet temporarily resized to demonstrate the fault with E2's formula.

 

[Peter Bartholomew]

The concatenation to form a primary key may be important to your overall process but it is not essential to the task of calculating the combined list. As an aggressively 'new' strategy it would be possible to define a Lambda function to stack the two tables and then sort and filter.

= SORT(UNIQUE(VSTACKλ(form, data)))

That is readable but hides the boring bit of defining the Lambda function in the first place:::

= LAMBDA(list1,list2,
    LET(
      n₁, ROWS(list1),
      n₂, ROWS(list2),
      c,  {1,2},
      k,  SEQUENCE(n₁+n₂),
      IF(k<=n₁,INDEX(list1,k,c),INDEX(list2,k-n₁,c))
    )
  )

Without Lambda, one would simply program the formula directly into the active cell, here based upon the key columns

= LET(
    nForm, COUNTA(formKey),
    nData, COUNTA(dataKey),
    k,     SEQUENCE(nForm+nData),
    stack, IF(k<=nForm, INDEX(formKey, k), INDEX(dataKey, k-nForm)),
    SORT(UNIQUE(stack))
  )