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.

Improve this question
1
  • Welcome! To best "help us help you", please consider sharing your file (or a copy with some anonymized placeholder data). Make sure it's shared as "Anyone with the link can Edit." Not sure how? Here's Google's help topic or check out this quick video of the steps.
    – Joel Reid
    Mar 19, 2019 at 17:29

4 Answers 4

Reset to default
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

Improve this answer
3
  • If you think you'll end up using or adapting this answer, please mention anything about it you don't understand. Maintaining someone else's spaghetti formula when your requirements inevitably change is horrible.
    – Joel Reid
    Mar 19, 2019 at 18:01
  • I really enjoy this answer, because my brain definitely doesn't think to just delimit everything first and then remove extra characters with the replace functions. I've spent some time tearing apart the nesting to better understand the steps to modify the data. Something I'm noticing is that there's no visual difference between the results of the first regexreplace and the nested one. Is that first level just a good practice measure by you, or is there a necessary intent with the data I'm working with?
    – Steven D.
    Mar 19, 2019 at 19:25
  • The printed-first (executed-last) replace trims trailing semicolons ;$ (dollar sign means end of the searched string). To see it in action, have a blank Skill6. The printed-second (executed-earlier) replace collapses consecutive semicolons ; numbering from 2 and up {2,} and replaces them with just one semicolon. To see it in action, have consecutive blank Skill columns between 1 and 6. Both replaces are only needed since joins usually preserve blanks—csvs with lots of ,,,,,, sequences is normal, for example, but not what we want here.
    – Joel Reid
    Mar 19, 2019 at 23:05
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!

Improve this answer
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))
))
Improve this answer
2
  • Thanks! This used functions I was more familiar with in my own experience. While playing around with the formatting, I noticed that I could pull out the transposes without changing the output. Is this because the formula is still working with only rows or only columns, and not rows/columns at the same time?
    – Steven D.
    Mar 20, 2019 at 16:28
  • Pretty much, the Transpose thing is just how I got to the answer. The filter command can do Remove Columns or Rows but now both at the same time.
    – sageco
    Mar 21, 2019 at 5:10
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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.