4

I'm working with two rows of values that are related to each other and trying to build a formula that will compile the data into one cell, with syntax that uses two delimiters.

If a cell in the active row contains a value, I want to pull the information in row 2 and 3, pair them together with delimiter |, and then use another delimiter ; until all of the values have been added. The output should end up as:

1

I've attempted textjoin, join, and ifs functions, but I can't find the right way to structure a formula to accomplish this because I'm not aware of a way to make the argument alternate the values in two rows until the end of the data. I've tried nesting another textjoin but this only appends the values/delimiters rather than alternating them.

Any suggestions on how I could structure this would be appreciated.

user0
  • 27,169
  • 10
  • 55
  • 81
Steven D.
  • 51
  • 4

4 Answers4

1

Assuming your Skill columns are B through G (alter to suit), try this:

=REGEXREPLACE(REGEXREPLACE(ARRAYFORMULA(JOIN(";",IF(NOT(LEN(B2:G2)),"",B$1:G$1&"|")&B2:G2)),";{2,}",";"),";$","")`

It does just what you said… check for non-blanks and return the found entries' pairs, then it uses regexreplaces to remove any consecutive or trailing semicolons.

0

user0
  • 27,169
  • 10
  • 55
  • 81
Joel Reid
  • 700
  • 4
  • 16
1

Answering my own question because I managed to come up with a solution separate from Joel. It requires me using a slightly different approach to the data, but it may be helpful for people that approach the answer differently.

I created a second row (row 35) below the Skill Level rows (row 34) in the above image. In that row I have the following formula:

=ifs(ISBLANK(C34),,C34, (join("|", C33:C34)))

This would reference the above cell for the value set for that skill. If the referenced cell is blank it treats the formula cell as a blank, otherwise, it uses join in order to link the skill ID to the skill level with | in between.

I then have the second formula to generate my final result:

=textjoin(";", TRUE, C35:H35)

Since textjoin's boolean will skip any cells that are identified as blank, this concatenates and delimits the combined skills/skill levels with ; as originally intended. If there's a simpler solution using this method, I'm not sure how to break it down from there yet!

user0
  • 27,169
  • 10
  • 55
  • 81
Steven D.
  • 51
  • 4
1

Presuming you don't want to add another row, because who does, the below should work:

=JOIN(";",FILTER(
ArrayFormula(TRANSPOSE($C$2:$G$2)&"|"&TRANSPOSE(C3:G3)),
NOT(ISBLANK(TRANSPOSE(C3:G3)))
))
  • $C$2:$G$2 is your Skill ID ROW Range
  • C3:G3 is your individual Skill ID ROW range

The gimmick here is that you just add a "|" to everything and use FILTER to remove those cells that are blank in any given row.

Transpose lets us treat the rows as columns, simplifying the problem. However, as noted in the comments, they aren't strictly needed:

=JOIN(";",FILTER(
ArrayFormula($C$2:$G$2&"|"&C3:G3),
NOT(ISBLANK(C3:G3))
))
sageco
  • 63
  • 6
0

  • one-cell solution:

    =ARRAYFORMULA(IF(LEN(C4:C&D4:D&E4:E&F4:F&G4:G&H4:H),
 REGEXREPLACE(REGEXREPLACE(TRANSPOSE(QUERY(TRANSPOSE(
 IF(C4:H<>"", C3:H3&"|", )&C4:H), , 50000)),
 " {5}| {4}| {3}| {2}| ", ";"), "^;|;$", ""), ))

    0

user0
  • 27,169
  • 10
  • 55
  • 81