Merging multiple columns together

[Saravanan13]

Hello,

I am working on PDF extract data in which "City or town, state or province2 & City or town, state or province" is not getting properly aligned. Currently the input data contains extract of two PDFs. The issue is "City or town, state or province2" is getting split into multiple columns randomly.Sometimes it is getting aligned properly but sometimes it is getting split in to more than on column.

I would need this column to reflect as shown in output. Basically if the column name contains "City or town, state or province" then I need all the values for the column in one column. Is there way to handle this dynamiically.

Input -

Row	Form	Permanent address	Affirmation	Revision	Citizenship	8 Date of birth	Name	City or town, state or province2	City or town, state or province
12345	W-8BEN	118 ABC	No	10-2021	India	1/1/1901	John	INDIA 111111
678910	W-8BEN	255 CDE	No	10-2021	Srilanka	1/1/1901	Mark		222222

Output - 

Row	Form	Permanent address	Affirmation	Revision	Citizenship	8 Date of birth	Name	City or town, state or province
12345	W-8BEN	118 ABC	No	10-2021	India	1/1/1901	John	INDIA 111111
678910	W-8BEN	255 CDE	No	10-2021	Srilanka	1/1/1901	Mark	222222

[binuacs]

@Saravanan13 you can try something like attached workflow

[Saravanan13]

Hello All,

This works for the current input, but "City or town, state or province" Can be duplicated multiple times and values will split based on the extract results.

[binuacs]

@Saravanan13 you can try this workflow with your use case, if still not working provide another input file with the above use case.

[Qiu]

@Saravanan13 
Maybe you can share some more sample input?

but I believe the flow from @binuacs  should work dynamicly given that he is using Transpose and CrossTab.

[flying008 · Accepted solution]

Hi, @Saravanan13 

Another way of  dynamic solution for you: (Even the field 'City or town, state or province' be repeat split more the 10+ columns, this is done !)

[Saravanan13]

Hello,

can you share the workflow, I need to look at the configuration. This looks like it will resolve my problem.

[caltang]

@flying008 can't share workflows due to their internal policies. You should be able to replicate the workflow by looking at the gif I suppose. Otherwise, @flying008 can you provide screenshots of each configuration of each tool? So at least @Saravanan13 can do it himself.

[Saravanan13 · Accepted solution]

I want to know the configuration of summarize tool, Dynamic replace and Dynamic select

[Saravanan13]

Also want to how this below output was derived

Exp	Min_name	Max_Name	Maz_Size