Search and Replace close matches

[shophoney]

Hi, I'm importing data from our credit card bills. I want to bring the data into our accounting system.

We have an issue when the names are not always the same. IE Shell 1234 and Shell 4354. I would like it to load it to the name Shell. Or Amazon charge of Amazon, Amazon Direct or Amazon Fulfillment. Should all go under Amzon.

But I dont want to change the uploaded data manually each time. And If someone goes to another Shell station #7878 . I want it always post it to Shell.

So I could make a list saying "Amazon" and then ask Excel to look for any names in list A and in list B change the names to match. IE Shell * = Shell, Amazon* = Amazon...

Thanks for your help.

 

[GR00007]

It sounds as though you are looking at sale point locations; Shell [store#], Amazon [type]....
Do you have a client# (cust#, whatever) that could be used? If not there are so many ways things can get bolloxed up (Amazon of Shellberry; Shell of Amazon Zone).
One option would be to look at values of that field over time and create a tab of those unique values assigning each to the 'name' you want them as - then do a lookup to get that value. This will take manual additions as new ones appear, but in the long run I believe it will be more accurate.

 

[shophoney]

Sorry, no.

I'm just importing data right off our credit card statement.

Looking to grab all the Amazon charges and variations there of, and just upload them as Amazon.

Thanks

 

[GR00007]

So, an ETL process (Extract, transform, load).
How do you want to use Excel? There are tools out there as well: Informatica, SQL SSIS, Talend, Alteryx, etc.