In the attached spreadsheet, I have a column on the "DATA PROCESSED" sheet called "Sites Visited" (col BT). In this column I have concatenated the "Site Name" columns from entries 1-10 but I would like to only return the first instance if the site name in entries 1-10 is duplicated.
In cell BU4 I tried this formula: =TEXTJOIN(", ",1,UNIQUE({D4,J4,Q4,X4,AE4,AL4,AS4,AZ4,BG4,BN4})) but it is still giving me duplicates (Office, Sheraton, Sheraton). I would like it to just return: "Office, Sheraton"
Thanks in advance!!
the issue is you have a trailing space for one of the Sheraton (Cell_J4 for example) which is causing the duplicate(s) although you are trying to unique it:
Alternatively you can use this single array-style formula:
=byrow(D3:BN,lambda(Σ,if(counta(Σ)=0,,textjoin(", ",1,unique(filter(trim(Σ),D2:BN2="Site Name"),1)))))