How to combine or calculate two multiple-select values

Is there an easy way to ‘calculate’ or combine Tags and Score, so I get row 1: “A7, B6, C5, D4, E3, F2, G1” and row 2: “C7, G6, F5, A4 E3, D2, B1”

Is {Score} always going to be 7,6,5,4,3,2,1?

If so, then you can ditch {Score} altogether and write a formula like:

IF(
  Tags,
  CONCATENATE(
    MID(Tags, 1, 1) & "7",
    MID(Tags, 2, 3) & "6",
    MID(Tags, 5, 3) & "5",
    MID(Tags, 8, 3) & "4",
    MID(Tags, 11, 3) & "3",
    MID(Tags, 14, 3) & "2",
    MID(Tags, 17, 3) & "1"
  )
)

^FYI there will be an issue with the above formula if less than 7 tags are selected.

You can clean that output up a little with another formula field that will cut off extra items at the end.

MID(
  Calc,
  1,
  (LEN(REGEX_REPLACE(Tags, "[^,]", "")) + 1) * 4 -2
)

Example:



Now, if not all records will have 7 tags (like the third row in the image above) and you need to count down from however many tags there are instead of always 7, that will require adjustments to the formulas given.

I would recommend having a formula field to calculate the number of tags selected:

(LEN(REGEX_REPLACE(Tags, "[^,]", "")) + 1)

Calc’s revised formula would then be:

IF(
  Tags,
  CONCATENATE(
    MID(Tags, 1, 1) & ({Count of Tags} - 0),
    MID(Tags, 2, 3) & ({Count of Tags} - 1),
    MID(Tags, 5, 3) & ({Count of Tags} - 2),
    MID(Tags, 8, 3) & ({Count of Tags} - 3),
    MID(Tags, 11, 3) & ({Count of Tags} - 4),
    MID(Tags, 14, 3) & ({Count of Tags} - 5),
    MID(Tags, 17, 3) & ({Count of Tags} - 6)
  )
)

And the final output would be:

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.