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:
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.
Best Answer
Assuming your Skill columns are B through G (alter to suit), try this:
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.