Change names of Values which are similar

[Kaish]

Hi All, I want to change the names of the 2 values for which the names are similar, providing an example, I want to change the value of the example given below, can someone help me with this ?

Data:

Region       Amount

EMEA           100

EMEA           100

NA                 200

NA                 100

ASIA              100

ASIA               200

TOTAL             6

TOTAL             800

Output:

Region       Amount

EMEA           100

EMEA           100

NA                 200

NA                 100

ASIA              100

ASIA               200

TOTAL#           6

TOTAL$           800

Just I have to add # and $ signs for the first and 2nd Total, everything remains the same. the data comes in the similar format, # comes first and $ comes 2nd how can I achieve this? 

[Mathias_Nielsen · Accepted solution]

Hi @Kaish,

You can try this multirow formula, it will achieve the desired result but it is based on the positioning of the "TOTAL" row, so it will always add a "#" to the first one and a "$" to the 2nd one.

Formula:

if [Row+1:Region] = "TOTAL" and [Region] = "TOTAL" then "TOTAL#" elseif [Row-1:Region] = "TOTAL#" then "TOTAL$" else [Region] endif

[Sebastian_Chaieb2 · Accepted solution]

Hi @Kaish 

Please check below attached workflow where you can find two possible solutions

Thanks.

Sebastian 

 

[flying008 · Accepted solution]

Hi, @Kaish 

Two ways for you.

1- With Multi-Field Formula: (need change output size to more.)

[_CurrentField_] + IIF(Trim([_CurrentField_]) != 'TOTAL', '', Switch([Tile_SequenceNum], '',1, '#', 2, '$'))

2- With Multi-Row Formula: (Group by [Region])

IF [Row+1:Region] = 'TOTAL' THEN 'TOTAL#'
ELSEIF [Row-1:Region]= 'TOTAL#'  THEN 'TOTAL$'   ELSE [Region] ENDIF

[Kaish]

Hi  @flying008 , thanks for the solution mate, worked for me.

[Kaish]

Hi @Mathias_Nielsen , thanks for the solution mate, worked for me.