Search Multiple Fields for Supplier Names

[awithing]

I am trying to clean up a database of equipment assets that we have in inventory.  I need to search multiple fields for a supplier name and populate a new column "Supplier" (the database has many more columns and rows than shown here).  The supplier name can show up in multiple fields and is often misspelled or the supplier name has changed since it was entered.  I created an Excel file with all of the suppliers and common ways that they can be misspelled or changed from.  I have tried doing multiple Find & Replace steps, one for each column I want to search, and then merging the findings back into a single column, but that gets messy rather quickly.  I also tried Transposing the data into a single column, but that is rather cumbersome to merge back into the list with a single supplier column as well.

Is there a way to search an entire row of data and populate a single output column?

[TheOC]

hi @awithing 

Are you able to provide these datasets, at least anonymised? 

I can try to give this a go for you!

Cheers,
TheOC

[awithing]

Here is the Excel file with two tabs for the Dataset and the Lookup Table.

[TheOC]

perfect!
I think you may need to change some of your 'find/replace' terms, but this workflow works I believe as you wish:

and the output:

As you can see Platypus has turned to ACME inc as it has not replaced the inc

all that needs changing there is one of the Platypus to 'Platypus inc'.


I also added a second workstream, that replaces the whole field - so removes these issues, if theres a match. This may be more fitting

I hope this helps! Please mark as solution if it fixes your problem!
TheOC

[awithing]

TheOC,

   Thank you very much for helping me with this, much appreciated.  This gets me about halfway there, now I just need the corrected names output to a single field (it should look like the "Desired Output" column I added), along with the original fields.

[TheOC]

hey @awithing 

Not a problem! Glad I could help. If you need any help with that let me know.

I think all you would need to do is supply a join tool at the end:

Assuming tag number is a unique identifier, joining on that:

And then you can select what fields you need from the original dataset, and which from the updated. You can also rename them in this tool:

I've attached this, but you may need to change the join configuration to how you need it. 
If this has solved your problem please mark is as a solution to help future users! 

Cheers,
TheOC

[awithing]

When I do that, it only picks up the data that was in the "Manufacturer" field, it does not pick up the "Model Number", "Vendor", or "Remarks" fields.  I need to collect all of the supplier data from all of the fields and merge it into a single field.

I apologize if I am missing something obvious, I am a bit of a newbie here.

[TheOC]

hey @awithing 

The join tool allows you to select what fields you want to keep from each side, 

Here you can see you are dropping the model number, vendor, and remarks field on the 'right' input of the tool. you need to hit the checkbox on the left side for this.

This may help too:
https://help.alteryx.com/current/designer/join-tool

Cheers,
TheOC

[awithing]

Yes, I can turn those on, but it leaves the info spread over four columns.  What I need is to have it all in a single column.

[awithing]

I think i found a way to merge the Suppliers back into a single output Field.  I used a Formula tool to create a new column for "Suppliers Found" and used that to Concatenate all of the suppliers into a single field/row for each item, then I did a second search of that field to replace the multiple concatenated values with a single value and a sort to rename the field and omit the old fields.

This ends up with multiple Find & Replace tools, but seems to get me the results I am looking for.  Does anyone see a better way to do this?