Tuesday, August 1, 2017

yogi_Return multiple unique comma separated values for a given search value

Google Spreadsheet   Post  #2213

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-02-2017
question by NewToSheets2212:
https://productforums.google.com/forum/#!topic/docs/x9OtBFSznFw;context-place=mydiscussions
yogi_Return multiple unique comma separated values for a given search value
This is the Google Sheet with representational data.


The tables are for maintaining an inventory of Products. I have a TABLE1 that lists products along with units of that product (Product no.) and whom it was sold to.

Product NameProduct No.Sold to
A1Alex
A2Alex
B1Jack
B2Mary
C1Alex
C2Alex
C3Jack


I have used the following TEXTJOIN formula in columns 2 and 3 of TABLE2. But in sold to column, the comma separated values are duplicated. 

=ArrayFormula(TEXTJOIN(",",TRUE,IF(G6=C6:C10,D6:D10,"")))
=ArrayFormula(TEXTJOIN(",",TRUE,IF(G6=C6:C10,E6:E10,"")))


ProductProduct No.Sold to
A1,2Alex,AlexDuplicates
B1,2Jack,Mary
C1,2,3Alex,Alex,JackDuplicates

I was thinking if there is something like textjoin(unique(vlookup)) -- if vlookup can be modified to return multiple values, I take unique of those and then join them to get 1 row of csv for each "Product".

The actual data will have 2500+ rows so need a solution that won't slow down the sheet operations.

Thanks!