Parse multifield

Question

How do I parse multiple field at once?

I have 2 datasets, and each one was updated randomly by previous users. I need to combine both of them together. It was about 74 columns long and I have merged 2 datasets together. All I need now is to parse the merged datasets to check if there is any same/different information in the dataset and update it accordingly.

I tried regex, but it only can parse once at a time. My parse delimiter is "/". and it is alphanumeric.

My plan is to parse the remaining and use multifield to check if the data is the same in both table, and keep only one to remove duplicates.

I am using excel vlookup at the moment in case I cannot finish this project on time.

Data as below (input)

SectorCodeCityOther/Other1586-U/1587-TTasmania/Tasmania

Output

SectorCodeCityOther1586-UTasmaniaOther1587-TTasmania

I am doing it in this way at the moment which is a really dumb way, as i will have to manually gone thru 74 settings, but its better than vlookup, vlookup crashes my file

I am using text to column to parse my field/column, then using formula to see if the text to column part is the same or not, if its the same then ill just take the 1st one, if not ill take the one with delimiters for outlier checking

Ben_H · Answer

Hi @terrellchong ,

As far as I'm aware the only way to do all of the parsing it with a single tool is to transpose it first so you can parse all the values at once in a single column, then cross-tab back to the original format.

I've attached an example.

Regards,

Ben

Transpose Sample.yxmd

messi007 · Answer

@terrellchong,

As I know you can't do it with only one tool to parse all columns.

Below how you can do :

Attached the worklfow,

Hope this helps!

Regards,

Parse20211220.yxmd

PorcedanDyedrops · Answer

Just updated my original post, please have a look