Data formatting

[reginawhelan]

Within the attached are two examples. Sheet1 is an example of the flow output, Sheet2 provides the desired results. 

This data will be uploaded into the system and the template for the upload doesn't accept duplicate information for some columns.

For example, within the attached (sheet1) the data is unique in columns Data#3 and Data#6 but the same in Data#1 and Data#2. 

Data#1	Data#2	Data#3	Data#6
Data#1014	Data#2P-F	NGL	0.95
Data#1014	Data#2P-F	MLY	0.95
Data#1014	Data#2P-F	PFM	0.95

The desired results would be removing the duplicate data in columns Data#1 and Data#2. 

Data#1	Data#2	Data#3	Data#6
Data#1014	Data#2P-F	NGL	0.95
		MLY	0.95
		PFM	0.95

I would want the data in column Data#6 to remain even though it's duplicates.

The number of rows would increase/decrease depending on the customer.

Does anyone have any ideas on how to solve for this?

Thanks,

Regina

[SamDesk · Accepted solution]

Hello @reginawhelan,

So this is something we can do pretty easily with the Multi-Row Formula tool. It's a little bit trickier than usual as you're wanting to change two fields at the same time but as shown in the attached workflow, this is achievable by giving each record a unique identifier first.

 

Hope this helps!

Sam 🙂

[CharlieS]

Hi @reginawhelan 

The Multi-Row Formula tool is helpful for this. I use two of them (one for each field) and a Select tool to rename some fields. The Multi-Row Formula tool can determine if the current value is the same as the row above (aka "Row-1") and if so, remove the value. The trouble is that if we replace the existing field, then the null replacement would complicate things, So I recommend creating two new fields (each with a Multi-Row Formula tool), then rearranging the fields with a Select tool.

Check out the attached example to see what I'm talking about in action.