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.
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?
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.
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.
