CODEWITHSUNDEEP
excel-formula
SoftTimur
SoftTimur

Reputation: 5520

One formula to combine 2 lists (2021)

I have one list starting from B1 (=UNIQUE(A1:A8)), and another list starting from D1 (=UNIQUE(C1:C8)). Thus =B1# and =D1# in other cells both spill.

enter image description here

Now, I would like to know if we could find one formula to combine List B1# and List D1# (extract only unique values) by dynamic array functions, LAMBDA, LET, etc.

I don't want to move the position of the two lists. Does anyone have any good idea?

Upvotes: 2

Views: 125

Answers (2)

mark fitzpatrick
mark fitzpatrick

Reputation: 3322

I may not be following what you want the shape to be, but here are two shapes:

Side-by-Side

=CHOOSE({1,2},B1#,D1#)

If you want it to take the original A and C columns as input and do all the work, then:

=CHOOSE({1,2},UNIQUE(FILTER(A:A,NOT(ISBLANK(A:A)))),UNIQUE(FILTER(C:C,NOT(ISBLANK(C:C)))))

or a LET version of the same which does not require retyping the inputs:

=LET( Ltrs, A:A,
      Nmbrs, C:C,
       CHOOSE( {1,2},
                UNIQUE(FILTER(Ltrs,NOT(ISBLANK(Ltrs)))),
                UNIQUE(FILTER(Nmbrs,NOT(ISBLANK(Nmbrs)))) ) )

End-on-End

=LET( uLtrs, B1#,
      uNmbrs, D1#,
       ltrCt, ROWS(uLtrs),
       idx, SEQUENCE( ltrCt + ROWS(uNmbrs) ),
       IF( idx <= ltrCt, uLtrs, INDEX( uNmbrs, idx-ltrCt ) ) )

Similar as above, if you want it to take the original A and C columns as input and do all the work, then:

=LET( Ltrs, A:A,
      Nmbrs, C:C,
       uLtrs, UNIQUE(FILTER(Ltrs,NOT(ISBLANK(Ltrs)))),
       uNmbrs, UNIQUE(FILTER(Nmbrs,NOT(ISBLANK(Nmbrs)))),
       ltrCt, ROWS(uLtrs),
       idx, SEQUENCE( ltrCt + ROWS(uNmbrs) ),
       IF( idx <= ltrCt, uLtrs, INDEX( uNmbrs, idx-ltrCt ) ) )

Both spill the results. results

Upvotes: 1

James
James

Reputation: 123

Place the following code into cell F2 and drag formula downwards to F14. This will give you a unique list of both Column A and Column D

=IF(IFERROR(IF(INDEX($A$1:$A$99999,MATCH(0,COUNTIF($F$1:F1,$A$1:$A$99999),0))=0,NA(),INDEX($A$1:$A$99999,MATCH(0,COUNTIF($F$1:F1,$A$1:$A$99999),0))),INDEX($C$1:$C$99999,MATCH(0,COUNTIF($F$1:F1,$C$1:$C$99999),0)))=0,NA(),IFERROR(IF(INDEX($A$1:$A$99999,MATCH(0,COUNTIF($F$1:F1,$A$1:$A$99999),0))=0,NA(),INDEX($A$1:$A$99999,MATCH(0,COUNTIF($F$1:F1,$A$1:$A$99999),0))),INDEX($C$1:$C$99999,MATCH(0,COUNTIF($F$1:F1,$C$1:$C$99999),0))))

Let me know if you need it to behave differently.

Upvotes: 1

Related Questions