Thursday, May 18, 2017

yogi_For Specified Words Pull Unique Values For FIELD1 And FIELD2 And Present As Comma Separated Values

Google Spreadsheet   Post  #2164
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-18-2017
question by ai tis:
Query Combining duplicate matches while excluding copies
I've been using the following function:

    =query(Sheet1!A2:D," select A, B, C where A matches '"&JOIN("|", A2:A)&"' and D matches 'yes'")

This function returns 3 rows with the same string in column A along with some other rows which have different strings in column A.  For the case where column A has the same string for various rows, I want to combine the columns selected (a, b, c) into new columns a, b, and c.
In other words, I want every row that starts with a match will be added a comma separated list in which each column occupies one cell with no duplicates as shown in sheet3.

https://docs.google.com/spreadsheets/d/1YDxIUnZzzYde9hcexPoDegv4HBuiUwk2wLKSXazu9hE/edit?usp=sharing

Sheet 2 has the function that I used and the result.