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/
Sheet 2 has the function that I used and the result.
=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/
Sheet 2 has the function that I used and the result.
the url for this https://docs.google.com/spreadsheets/d/1YDxIUnZzzYde9hcexPoDegv4HBuiUwk2wLKSXazu9hE/edit?usp=sharing got deleted. can you reshare the link again?
ReplyDelete