Renaming row based on a previous row with no whitespace.

[sebastian123Han]

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?

[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 · Accepted solution]

@sebastian123Han 
I hope this will be helpful.

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

[sebastian123Han]

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

[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.

[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/