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.
Soluta sint ex commodi in. Minus tempora consequatur voluptatem delectus rerum. Quaerat facilis perferendis eaque reiciendis ipsam rerum. Cumque non provident vitae. Consectetur quidem aperiam deserunt rerum voluptatibus doloremque est. Eum et qui aliquid iusto ducimus debitis et.
Dolores nesciunt qui esse harum. Esse quia exercitationem inventore ex enim ut velit. Id temporibus quis tenetur et unde eos sint.
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...