CleverUserName
New Member
- Joined
- Nov 25, 2018
- Messages
- 11
I have various spreadsheets that can contain mailing addresses with anywhere from 60,000 to 140,000 records. On certain records the street address, which is represented in one cell, is listed as a range of home address numbers, such as 145-150 Smith Rd. The City, State and Zip are also in individual cells.
I need some VBA code that will look at each target record in the spreadsheet, determine if it has a range of home address numbers within it and then duplicate the record "X" amount of times but list each home number separately contained in the original range.
EXAMPLE
Ad | City | State | Zip | |||
100-103 Columbia Ave | ABC | NY | 123456 | |||
9-10 Congress St | ABC | NY | 123457 | |||
1 N Cutler St | ABC | NY | 123458 | |||
245- West St | ABC | NY | 123459 | |||
25A-26B North St | ABC | NY | 123460 | |||
-30 Center St | ABC | NY | 123461 | |||
RESULT | ||||||
Address | City | State | Zip | Informational Notes Only | ||
100-103 Columbia Ave | ABC | NY | 123456 | Original Record | ||
100 Columbia Ave | ABC | NY | 123456 | Dupe Record - Split out | ||
101 Columbia Ave | ABC | NY | 123456 | Dupe Record - Split out | ||
102 Columbia Ave | ABC | NY | 123456 | Dupe Record - Split out | ||
103 Columbia Ave | ABC | NY | 123456 | Dupe Record - Split out | ||
9-10 Congress St | ABC | NY | 123457 | Original Record | ||
9 Congress St | ABC | NY | 123457 | Dupe Record - Split out | ||
10 Congress St | ABC | NY | 123457 | Dupe Record - Split out | ||
1 N Cutler St | ABC | NY | 123458 | Original Record - No Range | ||
245- West St | ABC | NY | 123459 | Original Record - No Range | ||
25A-26B North St | ABC | NY | 123460 | Original Record -Not Numeric Range | ||
-30 Center St | ABC | NY | 123461 | Original Record - No Range |
<tbody>
</tbody>
https://drive.google.com/file/d/14If_GHcwF-xZjn8eVArFOSVHegLsxZao/view?usp=sharing