0

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:

  • Create an array of non-empty cells in the row. (IE: [2,4])
  • Return it with the header row A (IE: [A2,A4])
  • Then join them together by a comma

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

Wes Foster
  • 8,770
  • 5
  • 42
  • 62
  • 1
    I have to apologize for my poor English skill. I would like to confirm whether my understanding of your goal is correct. In your table, when the cell of `TagDog,TagArm` is "F2", the sample formula of `=JOIN(",",FILTER($B$1:$E$1,B2:E2=TRUE))` is the result you expect? If I misunderstood your question, I apologize. – Tanaike Jun 11 '21 at 01:38

2 Answers2

1

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.

kishkin
  • 5,152
  • 1
  • 26
  • 40
0

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.

Wes Foster
  • 8,770
  • 5
  • 42
  • 62
  • 1
    There is another approach if it is ok to copy the formula down: `=TEXTJOIN(",", 1, ARRAYFORMULA(IF(NOT(B2:E2),, $B$1:$E$1)))` ([screen](https://i.imgur.com/KEgb40U.png)) – kishkin Jun 11 '21 at 07:39
  • 1
    And in find and replace you could replace it with `=OFFSET(A1, 0, COLUMN() - 1, 1, 1)` – kishkin Jun 11 '21 at 07:45