CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulas
Wes Foster
Wes Foster

Reputation: 8900

JOIN header row values across a row based on non-blank values in cells

So I have two rows:

ID TagDog TagCat TagChair TagArm Grouped Tags (need help with this)
1 TRUE TRUE TagDog,TagArm

Row 1 consists mainly of Tags, while rows 2+ are entries. This data ties ENTRIES to TAGS.

What I'm needing to do is concatenate/join the tag names per entry. For example, look at the last column above.

I suspect we could write a formula that would:

But I am unsure how to write the formula, or if this is even the best approach.

Upvotes: 0

Views: 621

Answers (2)

kishkin
kishkin

Reputation: 5325

Here's the formula:

={
  "Grouped Tags (need help with this)";
  ARRAYFORMULA(
    REGEXREPLACE(TRIM(
      TRANSPOSE(QUERY(TRANSPOSE(
        IF(NOT(B2:E11),, B1:E1)
      ),, COLUMNS(B1:E1)))
    ), "\s+", ",")
  )
}

enter image description here

The trick used is called vertical query smash. That's the part:

TRANSPOSE(QUERY(TRANSPOSE(...),, Nnumber_of_columns))

You can find a brief description of this one and his friends here.

Upvotes: 1

Wes Foster
Wes Foster

Reputation: 8900

I wasn't able to create a single formula that would do this for me, so instead, I utilized a formula inside of Sheets' Find/Replace tool, and it worked like a charm!

I did a find/replace, replacing all instances of TRUE with the following formula:

=INDIRECT(SUBSTITUTE(LEFT(ADDRESS(ROW(),COLUMN()),3),"$","")&"$1")

What this formula does is it finds the cell's letter, then gets the first row of the cell using INDIRECT.

Breaking down the formula:

  • ADDRESS(ROW(),COLUMN()) returns the direct reference: $H$1
  • LEFT("$H$1",3) returns $H$
  • SUBSTITUBE("$H$","$","") replaces the dollar signs ($) and returns H
  • INDIRECT(H&"$1") references the exact cell H$1

Now, I can replace all instances of TRUE with that formula and the magic happens!

Here is a video explanation: https://youtu.be/SXXlv4JHDA8

Hopefully, that helps someone -- however, I would still be interested in seeing what the formula is for this solution.

Upvotes: 0

Related Questions