Renaming row based on a previous row with no whitespace.

Hey!
I have a problem with my data and renaming of the data.

I have several rows with the same name. However, they are distinguished on a previous rows name. For example as seen in the screenshot:

There are several rows named Client Service Partners. I want to name them such that for example the first client service partners is called:

Host_Client service partners and so on, as otherwise when I transpose the next column based on the column KPI, they sum the fields with the same name

Can anybody help here?

Workflow

Regex

Tips and Tricks

Custom Tools

Transformation

Dynamic Processing

Accepted answers

Qiu

@sebastian123Han
I hope this will be helpful.

And would be better if you could provide data file rather than a picture. 😀

0914-sebastian123Han.yxmd

All comments

csmith11

Yes, do you mind sending over example data? Just a copy of the table you showed above will be fine.

My recommended approach would be:

1. Add a flag for the Rows with No Whitespace,(Flag Column = First word of such row)

2. Leverage the above Flag in a Multi-Row Formula Tool to Prefix your subsequent rows.

Qiu

@sebastian123Han
I hope this will be helpful.

And would be better if you could provide data file rather than a picture. 😀

0914-sebastian123Han.yxmd

sebastian123Han

Hey! Yeah of course. I have linked some dummy data now:

Dummy data.xlsx

csmith11

I put together some quick dummy data and gave it a whirl.

1.I flagged where KPI = trim(KPI). This test fails where there is white space. I used getWord() to extract the first word for the prefix.

2.Multi-Row Formula Tool was used to Prefix the Flag to the subsequent rows after trimming the whitespace. (This could have been down in the KPI column as well.)

Hope this Helps!

If this does help, please consider marking as the solution to help other community members.

Flag On WhiteSpace.yxmd

sebastian123Han

Hey Qiu! Thanks for the answer it helped a lot!

However, I'm pretty bad at the REGEX_replace. Is there a way to use 2 of the words, such that e.g, instead of "Home-client service partners" it says

"Home chargeable - client service partners" ?

Thanks in advance!

Qiu

@sebastian123Han
Use 2 of the words can also be done, but I am afraid I have to use Regex also.
The RegEx is very powerful and here is a site you can study.

https://regex101.com/

0914-sebastian123Han-r1.yxzp

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703