Find closest match and copy

[saman110 via OfficeKB.com]

Hello,

In sheet 1 col. A I have many zip codes and In sheet 2 I have 5 col. A,B,C,D
are zip codes (They Do not match with sheet1 col. A) and in col. "E" I have
phone numbers.
How can I do a macro that compares sheet1 Col.A with sheet2 Col. A,B,C,D and
find the closest match and if found copy corresponding cell in Col. E which
is phone number to sheet1 next to zip code?

thank you.

 

[T. Valko]

How many rows of data do you have in Sheet2, more than 5000?

Are the zip codes standard U.S. 5 digit zip codes? Do any of the zip codes
start with leading 0s? If so, are the zip codes entered as TEXT strings or
are they entered as NUBERS with a custom number format to allow the leading
0s?

 

[saman110 via OfficeKB.com]

Hello Valko,

Sheet2 has more than half a million of rows. (Excel 2007)
Zip codes don't have leading 0s.
They are entered as Numbers. (I can change them if neccessary)

How many rows of data do you have in Sheet2, more than 5000?

Are the zip codes standard U.S. 5 digit zip codes? Do any of the zip codes
start with leading 0s? If so, are the zip codes entered as TEXT strings or
are they entered as NUBERS with a custom number format to allow the leading
0s?

Hello,

[quoted text clipped - 10 lines]

thank you.

 

[T. Valko]

Sheet2 has more than half a million of rows. (Excel 2007)

Ok, with that many rows you need a programmed solution. It could be done
with formulas but would take forever to calculate. Maybe repost in the
Programming group.


--
Biff
Microsoft Excel MVP

Hello Valko,

Sheet2 has more than half a million of rows. (Excel 2007)
Zip codes don't have leading 0s.
They are entered as Numbers. (I can change them if neccessary)

How many rows of data do you have in Sheet2, more than 5000?

Are the zip codes standard U.S. 5 digit zip codes? Do any of the zip codes
start with leading 0s? If so, are the zip codes entered as TEXT strings or
are they entered as NUBERS with a custom number format to allow the
leading
0s?

Hello,

[quoted text clipped - 10 lines]

thank you.