Dropping in records into a column in Excel that associates with a string value. Create a Table or Formula function to replace column information

I have a small spreadsheet by most standards with Hospital information in it. Someone left out the mailing addresses of the hospitals. Since it is only about 15 hospitals but they are in the spreadsheet over 500 times. I thought I could create another Sheet or table which would cause me to make a code for each hospital. Then I would have to place a code in a column for each hospital that relates to it.

I guess that leaves me with two options. Can anyone identify how to do either?
1. Drop values in to columns where the Hospital Name matches.
So to me in my limited knowledge. It is
IF {CELL.WhereHospital}="Actual Hospital Name" then  Print in Cell "Address1" In Column W, Print "City" in Column X, Print "State" in Column Y .....Zip etc.
Creating a small Table and then doing basically the same thing as above but finding the Hospital Name and assigning a Hospital Code?
Your thoughts talents and expertise appreciated. Thank you.
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
RartemassService Desk AnalystCommented:
You would have a lot of redundant information if you repeated the full address every time the hospital is mentioned.
This redundancy would increase the size of the file unnecessarily.
I think the simplest option would be to have the hospital addresses in a table on a separate sheet or beside the data if enough room.
Then assign a code for the hospital.

Dropping the code in can be done in a couple of ways.
With 15 hospitals it would be quicker to do a Find/Replace that a formula.
Find "hospital 1" replace with "code hospital 1". Replace all. Repeat for hospitals 2 to 15.

A drawback of this is that if you just want to search on the code you will be limited.

Another option is to use the Autofilter. Create a column next to the name column for the code to go in. Filter on the column of the hospital name. Select a single hospital. Enter the code for each entry when they are side by side. Repeat for each hospital.
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
That was perfect thank you. Did not even think about find replace. How simple. I was thinking complicated formula. Your idea much better. Thank you sir.
RartemassService Desk AnalystCommented:
Glad to help
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.