Macro to compare Exce columns and copy result

January 28, 2011 at 12:57:20
Specs: Windows XP
I have two spreadsheets. Sheet 1 has a list of names in Column A, and information in Column B (text and numerical) that corresponds with the name of the same row.

e.g. "Bob" corresponds with the information in Column C.

A       B        C         D
Bob            123

The second spreadsheet has a long list of names (in no particular order) in a single column. I would like the macro to search the 1st sheet for every name on the list in the 2nd sheet, and copy the information specified from 1st sheet and paste it into the 2nd sheet.

So, if the 2nd sheet had "Bob" first on its list, it would search the 1st sheet for "Bob" until it found it, and copy "123" into the corresponding cell on the 2nd sheet. It would then go to the next name on the 2nd sheet, and continue until the end of the list.

Basically, I am stuck manually searching for each name, copying the information, and pasting it into the 2nd sheet. I have hundreds of names, so this is painstaking.

Any help would be greatly appreciated, thanks!


See More: Macro to compare Exce columns and copy result

Report •

#1
January 28, 2011 at 17:55:50
Have you look at the VLOOKUP function in the Excel help files?

It sounds like that is exactly what you need.

Read up on it in the Help files (and maybe via Google too) and come on back if you need some more help.

Giving it a try on your own will be a good learning exercise.

Good luck!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 31, 2011 at 09:21:48
It is definitely helping some. Due to the nature of how I need the information to be formatted it isn't a perfect solution, but its definitely faster than before. Funny I never heard of VLOOKUP before.

Thanks for coming through again. Always appreciate your help.


Report •

#3
January 31, 2011 at 11:41:56
re: "Funny I never heard of VLOOKUP before."

Don't take this the wrong way, but if you've never heard of VLOOKUP before, maybe there are a few more functions that you haven't heard of.

Why not let us know why VLOOKUP isn't "a perfect solution" and perhaps we can expand on it's use or nest some other functions with it to get you even closer to where you need to be?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 31, 2011 at 14:01:04
I'm sure there are plenty I've never heard of... I only remarked about VLOOKUP because it seems pretty powerful and it isn't overly complicated. Just figured I'd have seen it used before is all.

A perfect solution wasn't required. Just cutting the time in half made the task that much easier to complete :)

Again, thanks.


Report •

Ask Question