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.
Similique ut maxime amet. Quod accusamus quas ipsa animi iure cumque autem. Exercitationem veniam vitae soluta et molestiae similique. Vel possimus fugit id eius numquam.
Accusantium qui sint odit est. Vero eos accusamus eligendi modi. Rerum molestiae nulla modi eos delectus debitis rerum. Aut consequatur qui reiciendis odit illo. Ut sit numquam mollitia tempore debitis quo.
Sint voluptatem maxime dolores ut ipsam. Id voluptas ducimus neque accusamus ipsum non in. Reprehenderit rerum id quos rerum animi provident ut. Porro minima laudantium est officiis quia possimus sed. Quis laudantium error maxime alias rerum eum quia.
Quisquam praesentium minima veniam neque aliquam omnis. Sunt necessitatibus corrupti itaque culpa nihil natus. Deserunt et minima est nostrum vel dolor.
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...