Hi Everyone,
Am wondering if someone could do a macro on this to help me to speed up the process.
I do following steps when I receive new address sheet to match with old.
Old-Data sheet1
Select Colum H [ Phone1 ]
Ctrl+F [Find & Replace] [ - ],[ + ],[space],[ . ] (Removing symbols and spaces from phone numbers)
New-Data-Sheet2
Delete Column C,D,E,F
Select Column D and Insert
Select Column I & J [ Area Code ] & [ Phone Number ]
Ctrl+F [Find & Replace] [ - ],[ + ],[space],[ . ] (Removing symbols and spaces from area code & phone numbers)
Insert Column K after [ Phone Number ]
In Column K add formula [ =CONCATENA(I2,J2) ] and copy formula till end.
Select All > Data > Sort > [ * ] My data has headers
Sort by Column L [ Country ]
Insert Column L and add formula [ =RIGHT(K2,9) ] for AUS only and copy formula where country is AUS.
Insert Column L and add formula [ =RIGHT(K2,10) ] for NZ only and copy formula where ****ry is NZ.
Copy column L from line 2 to end of the column and paste special [ value only ] at J2
Delete Column K & L
Select Column J, right click and convert all to numbers.
Go to Column D2 and add following formula
[ =INDEX(Old-Data sheet1!A:A, MATCH('New-Data-Sheet2'!J2,Old-Data sheet1!H:H,0)) ] Copy formula till end.
Highly appreciate your help on this!
Am wondering if someone could do a macro on this to help me to speed up the process.
I do following steps when I receive new address sheet to match with old.
Old-Data sheet1
Select Colum H [ Phone1 ]
Ctrl+F [Find & Replace] [ - ],[ + ],[space],[ . ] (Removing symbols and spaces from phone numbers)
New-Data-Sheet2
Delete Column C,D,E,F
Select Column D and Insert
Select Column I & J [ Area Code ] & [ Phone Number ]
Ctrl+F [Find & Replace] [ - ],[ + ],[space],[ . ] (Removing symbols and spaces from area code & phone numbers)
Insert Column K after [ Phone Number ]
In Column K add formula [ =CONCATENA(I2,J2) ] and copy formula till end.
Select All > Data > Sort > [ * ] My data has headers
Sort by Column L [ Country ]
Insert Column L and add formula [ =RIGHT(K2,9) ] for AUS only and copy formula where country is AUS.
Insert Column L and add formula [ =RIGHT(K2,10) ] for NZ only and copy formula where ****ry is NZ.
Copy column L from line 2 to end of the column and paste special [ value only ] at J2
Delete Column K & L
Select Column J, right click and convert all to numbers.
Go to Column D2 and add following formula
[ =INDEX(Old-Data sheet1!A:A, MATCH('New-Data-Sheet2'!J2,Old-Data sheet1!H:H,0)) ] Copy formula till end.
Highly appreciate your help on this!
