Extract and match the last part of string if matches write the abbreviation in column

[astha_gupta]

Hi everyone,
i am in learning phase of ui path and i am stuck here,can someone please suggest the solution for this?if_switch_pro.xlsx (14.4 KB)
Here is the scenario:(Attached in Excel)
I have a case in which one column in excel has to search for a particular value and then write its abbreviation instead of it.But since having multiple column values having similar names,contain does not work i tried them using switch and if statement and using contain,but it is giving miscellaneous values.
I want to extract the last part of designation like if its Senior analyst then in all columns SSE should be written BUT if its just Analyst then SE should be written.I am totally stuck since switch and if both are confusing with so many scenarios.please help!

[ppr (Peter)]

@astha_gupta
just for verifying our right understanding of your case

from the input worksheet the designation column value should be used to retrieve the corresponding Abbrevation

output is Name, Age, from input and the corresponding abbrevation within the Designation column

when readin the abbrevation worksheet with read range the merged cells will be unmerged and ideally the abbrevations will be in the first line from the merge group (SSE Row2)

After removing the empty lines the missing abbrevations from the grouped can be filled up by helper flow
then the filled up datatable can be used within a inner join (Join DataTable Activity)

[astha_gupta]

Hi, Sorry i attached only one file containing all sheetsif_switch_pro.xlsx (14.4 KB) ,abbreviation sheet is for understanding different columns…one is input sheet and one is output sheet(sheet3).i want my output to look like sheet 3 with designation column replaced with abbreviations.for reference i have renamed and attaching the input output in same excel file.

[supermanPunch (Arpan)]

@astha_gupta Check this Workflow :
Check the Excel file. Execute the Workflow. The output should be generated in the same Excel.
JoinDT.zip (11.1 KB)

Check the workflow for all types of data that you have.