Extracting phone numbers from a Sheet, adding a yes or no field via external source, merging the ammended data back
Good Day,
I know the solution sounds simple but it can be risky.
We have an excel document that has name, address, phone number ect and we need to extract the phone number, and send it off to have it compared to a external DB, that is simple and done. Once back there will be the numbers in column A and a "y" or a "n" in Column B. We then need to delete all the numbers with a "y" value and import the "n" back into the original File removing all the rows that are not in the list with a "n" in it, these must be relevant to the original number with contact details.
Please can someone assist in a way to automate this as we have to do this a few times a week and thee are thousands of entries.
Cheers
Grant
Not Using the Macro
Basically if you are not comfortable with editing the Macro here is the manual steps to do:
This is based on information starting in Row 2 and headers in row one.
Open your spreadsheet; open the external DB; in the final column type the following and copy down:
=IF(C2="""","""",IFERROR(VLOOKUP(C2,[data.xlsx]Sheet1!A:B,2,0),""""))
Use the AutoFilter function to select Y only, then delete these rows
Regards
Mark
use this Macro
Okay based on the information above I have created the following Macro. This is based on you saving the external DB to your computer (change the file path and name to suit you I have put it on my desktop as DB.xlsx). Also you will need to rename "Book1" in the below to your document name. Also I have the Y & N data being imported into Column D so if you have Data in that column Already you will need to edit the Macro a little.
Sub Macro1()
'
' Macro1 Macro
'
'
Workbooks.Open Filename:="C:\Users\Default\Desktop\DB.xlsx"
Windows("Book1").Activate
Range("D2:D3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FormulaR1C1 = _
"=IF(RC[-1]="""","""",IFERROR(VLOOKUP(RC[-1],[data.xlsx]Sheet1!C1:C2,2,0),""""))"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveSheet.Range("$A:$D").AutoFilter Field:=4, Criteria1:="Y"
Rows("2:100000").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter
Columns("D:D").Select
Selection.ClearContents
Range("D1").Select
Windows("DB.xlsx").Activate
ActiveWindow.Close
End Sub