Find & Replace Tool

[Vandhana_Chandran]

The whole idea of building the macro is to identify the name changes of the supplier and new supplier creations within consecutive days on a daily basis. I have a master file (yesterday's file) which has to be compared with today's file. I have achieved to build the macro to identify the supplier name changes and new creations after comparing the master file with today's file. However, the master file has to be updated everyday in such  a way that we replace the supplier's old name with the latest names and append the newly created suppliers. Now, this updated master file should be used to compare with tomorrow's file. Once the name changes and new creations have been identified we have to update the master fil again to ensure that we have the old names replaced with new ones and add the newly created ones which will be ready to compare with tomorrow's report.

The columns in both the file contain : Ariba ID, Supplier Name, Street, City, State, Postal Code, Country, Approval status, Create Date, Creator-user, Count().

We use only the Ariba ID (unique/primary key) and supplier name to compare both files. However, the final output (Master file) should have all columns preferably.

If you could also let me know ways to automate this report in such a way that Alteryx picks the file from outlook (Except Outlook 365 Input tool since my organization wouldn't allow Alteryx to read my outlook) that reads and compares to give the desired out and send via email (Email bit is something I already know.)

Kindly help me with the last bit of the macro.

Thank you!

[HannahBrauer223 · Accepted solution]

To update the master file, I would have my flow create a column structure that has:

Original Name   |    New Name

Then I would use an IF statement to update [Original Name]:

IF !IsNull([New Name])

THEN [New Name]

ELSE [Original Name]

ENDIF

This will look to see if New Name is not null, then use it to update original name. 

To append the new companies to the master file, I would just use a union after the join tool to bring in the new entities.

Once all that has been updated, I would output back to the original input file of the master sheet to update it. I would consider using an output tool on the original unmodified master sheet as well to save it somewhere with today's date in the name so that you can reference old versions, if needed.

Hannah Brauer

Crowe LLP

[Vandhana_Chandran]

Hey Hannah, thank you so much!

That worked as expected.