Find closest match and copy

  • Thread starter Thread starter saman110 via OfficeKB.com
  • Start date Start date
S

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

T. Valko said:
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,
Click to expand...
[quoted text clipped - 10 lines]
thank you.
Click to expand...
Click to expand...
 
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


saman110 via OfficeKB.com said:
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)

T. Valko said:
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,
Click to expand...
[quoted text clipped - 10 lines]
thank you.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top