CODEWITHSUNDEEP
listgoogle-sheetsgoogle-sheets-formula
sarisbaris
sarisbaris

Reputation: 11

How do I create a comma-separated list of values in Google Sheets

How do I create a comma-separated list of values which pulls the top value from each list using a common value in Google sheets. For example, if I have three lists and want to use a common value to pull the value of the top list into a comma-separated list (hope that makes sense):

Category 1

apples

oranges

pears

Category 2

apples

pears

grapes

Category 3

oranges

apples

celery

I'm trying to create lists that look like the following using the common value (oranges, apples, etc):

oranges: category 3, category 1

apples: category 1, category 2, category 3

celery: category 3

pears: category 1, category 2

grapes: category 1

So many thanks if someone could help me with this!

Upvotes: 1

Views: 1818

Answers (1)

rjmccallumbigl
rjmccallumbigl

Reputation: 385

If ColA - ColC has the following:

Category 1 Category 2 Category 3

apples apples oranges

oranges pears apples

pears grapes celery

Put the following formula in, say, E1:

={"Values";UNIQUE(TRANSPOSE({TRANSPOSE($A$2:$A$4),TRANSPOSE($B$2:$B$4),TRANSPOSE($C$2:$C$4)}))}

This will create an array with a header and transposes the unique values from the 3 ranges of categories (from rows 2 to 4, change if needed). This way each value is extracted but there are no repeats.

Then, put header text in F1 ("Categories"). Put this formula in F2 and drag down to match each result in ColE:

=TEXTJOIN(", ",TRUE,{{IFERROR(IF(SEARCH($E2,join("",$A$2:$A)),$A$1,""))},{IFERROR(IF(SEARCH($E2,join("",$B$2:$B)),$B$1,""),)},{IFERROR(IF(SEARCH($E2,join("",$C$2:$C)),$C$1,""),)}})

This formula will search each category in ColA - ColC for the unique fruit in ColE. If there's a match, it will return the Category. The Textjoin() function separates the results with a comma.

enter image description here

Upvotes: 1

Related Questions