Hey,
I have an excel sheet of roughly 1000 numbers. This sheet will come to me monthly, most the time with the same numbers, but different information. Each number corresponds to something. I am wondering how to create an excel sheet or template so that when I open this excel sheet, the 1000 numbers will automatically switch to a name.
For example,
2331098 corresponds to Johnson Surface Air. I would like to set a macro so that whenever the excel sheet sees 2331098, it will automatically switch the name to Johnson Surface Air.
Any ideas?
Thanks.
V Look Up, H Look up
I don't know much about macros (in fact I've been looking for good materials to learn more about them in the last days), but what you are looking for can be easily done with the vlookup function. And given that you need to do it just monthly anyway, it shouldn't be a huge drag to repeat the process everytime.
If you don't know the function, here's a short tutorial: http://www.contextures.com/xlfunctions02.html
edit: damn ninjas
wow you must be a pre-analyst or an intern's intern...
.
Ahahaha, nice one.
Just kidding...if you do a Google search there are some pretty good sites I have found including forums that could explain v-lookup etc. better than I can. Might be worth it to download a copy of an Excel manual for reference, too.
Just make sure to copy paste values before you close the spreadsheet you're referencing from
You can use the following macro.
Sub replace_company_names()
find_numbers = Array("234", "567", "3") Replace_companies = Array("First Company", "Second Company", "Third Company") Columns("a:a").Select Range("a:a").Activate array_size = UBound(find_numbers) For i = 0 To array_size Selection.Replace What:=find_numbers(i), Replacement:=Replace_companies(i), LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Next iEnd Sub
Or you can use index(match)
Index + match is better than hlookup and vlookup imo because it's not column or row dependent. You can move in any dimension.
Impedit debitis omnis ut nobis et velit. Labore dignissimos unde corporis eius voluptatum aut explicabo. Provident corporis voluptas molestiae suscipit et. Sint autem vero qui id soluta sed voluptatum.
Rerum fuga ab enim voluptatem assumenda dolor. Consequatur ipsa laudantium non sed sit quos doloremque. Quia odio sunt eveniet soluta. Eligendi corrupti iusto totam odit.
Quo enim dignissimos aut consequuntur magni sit. Et cupiditate aut non quia. Officia exercitationem et numquam eligendi esse non fugiat. Voluptate voluptatem voluptatem ut laudantium atque deserunt. Odio aperiam occaecati voluptas voluptas. Et quo reprehenderit labore quia.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...