Help

How to combine or calculate two multiple-select values

Topic Labels: Formulas
470 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Cunwalker
4 - Data Explorer
4 - Data Explorer

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”

Screenshot 2022-05-03 at 14.22.31

1 Reply 1

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:
image



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:
image