Excel Help - Please

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.

 

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

 

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.

Chance favors only the prepared mind.
 
Best Response

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 i

End 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.

 

Alias consequatur nesciunt minima deleniti quia impedit necessitatibus veniam. Dignissimos corporis doloremque velit illum aut maxime dolor. Omnis dolores voluptatem qui reiciendis quia enim sunt porro. Ratione aut et perspiciatis consectetur. Consectetur vel necessitatibus fugit sed.

Dolorem omnis molestiae provident blanditiis nulla. Minima aut voluptas ut dignissimos ipsam. Occaecati tempore nemo expedita odit cumque provident modi. Placeat facilis dolores tempora.

Accusamus ipsum minus recusandae ea alias consequatur veniam. Tempora minus nam culpa quia. Consequatur odio ex quia a modi.

Minus ut unde vel et. Ad est impedit eveniet. Et quod officiis labore sapiente laudantium minus cum.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Secyh62's picture
Secyh62
99.0
3
BankonBanking's picture
BankonBanking
99.0
4
Betsy Massar's picture
Betsy Massar
99.0
5
kanon's picture
kanon
98.9
6
dosk17's picture
dosk17
98.9
7
GameTheory's picture
GameTheory
98.9
8
CompBanker's picture
CompBanker
98.9
9
DrApeman's picture
DrApeman
98.9
10
Jamoldo's picture
Jamoldo
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”