Excel Forum

Hi there,

I need your help how to write formula to extract only repeat customers with date

I attach a template and expected outputs in color cells.

Thx

Note i need both dynamic array and excel 2019

...

Need helper columns to avoid the complicated formulas.

H2

=IFERROR(SEARCH(";",SUBSTITUTE($B2,CHAR(160),""),G2+1),999)

Copy a formula to the right and down.


P2

=IFERROR(";"&MID(SUBSTITUTE($B2,CHAR(160),""),G2+1,H2-G2-1),"")

Copy a formula to the right and down.


Y2

=IF(SUMPRODUCT(1/COUNTIF(P2:W2,P2:W2))-1=COUNTA(P2:W2)-
COUNTBLANK(P2:W2),"",ROW(Y2))

Copy down a column


AA2

=IFERROR(INDEX($P2:$W2,AGGREGATE(15,6,{1,2,3,4,5,6,7,8}/(MATCH($P2:$W2,$P2:$W2,0)<>{1,2,3,4,5,6,7,8}),COLUMNS($AA2:AA2))),"")

Copy a formula to the right and down.


AG2

=MID(AA2&AB2&AC2&AD2&AE2&AF2,2,99)

Copy down a column


Output

E2

=IFERROR(INDEX(C:C,SMALL($Y$2:$Y$11,ROWS(E$2:E2))),"")

Copy down a column



F2 =IFERROR(INDEX(AG:AG,SMALL($Y$2:$Y$11,ROWS(F$2:F2))),"")

Copy down a column


=======

Excel365

** Still need a helper column to avoid the complicated Spill Array formula


Helper

H2

=CONCAT(SORT(IF(LET(t,TEXTSPLIT(SUBSTITUTE(B2,CHAR(160),""),";"),IFERROR(IF(SEARCH(";",B2),IF(XMATCH(t,t)={1,2,3,4,5,6,7,8},"",t),""),""))="","",";"&LET(t,TEXTSPLIT(SUBSTITUTE(B2,CHAR(160),""),";"),IFERROR(IF(SEARCH(";",B2),IF(XMATCH(t,t)={1,2,3,4,5,6,7,8},"",t),""),""))),,-1,1))
Copy down a column

Output

E2

=IFERROR(HSTACK(INDEX(C:C,SORT(ROW(H2:H7)/(H2:H7<>""))),MID(INDEX(H:H,SORT(ROW(H2:H7)/(H2:H7<>""))),2,99)),"")



Bigcat9Excel

Bangkok Thailand

...

thanks,it's always nice

is it possible without helper column even excel 365?

You can use this formula in Excel 365.

E2

=LET(a,MAP(B2:B7,LAMBDA(a,TEXTJOIN("; ",TRUE,FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(a,CHAR(160),""),";","</s><s>")&"</s></t>","//s[preceding::*=.]")))),FILTER(HSTACK(C2:C7,a),ISTEXT(a)))

Enter

Thanks ,always be helpful