asked on

Match a list from one excel sheet in another workbook

Hi Experts,

I receive an excel data dump which contains about 10,000+ of domains in it.
I have a separate list which is in excel (although I could format it in any way) containing about 100 domains.

I need to be able to search the large file and extract only the data pertaining to the 100 domains I have.

Where do I start? Because I receive these data dumps regularly I need something that is repeatable and relatively straight forward to execute.

any ideas?

Thanks

Kimputer

Some more sample data of the two files, and how you want the results returned would be nice.

eezar21

ASKER

Sorry....

I've tried to illustrate it in the attachment
Example.xlsx

Kimputer

Provided you have 2 clean excel sheets (no headers, data starts at A1), and the files are in the same folder, in that folder will be a text file results.txt.
Also, please input the FULL name, as in x1.xlsx, if you only input x1, it will try x1.xlsb by
This is vbscript btw, didn't know if you wanted VBA or not.

Const adOpenStatic      = 3

ForReading = 1
StrFolder = "D:\temp\"
logfilename = StrFolder & "results.txt"
Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists(logfilename) Then
	fso.deletefile(logfilename)
End If

Set logFile = fso.CreateTextFile(logfilename, True)

Set xlApp = CreateObject("Excel.Application") 
xlApp.visible = True 
xlApp.DisplayAlerts = False
strAnswer = InputBox("Please enter a name for your SOURCE (contains the data to match) FILE:", _
    "Input SOURCE File")

source1 = Array(1,2)
source1 = readexcel2array(StrFolder & strAnswer, "a")
source2 = readexcel2array(StrFolder & strAnswer, "b")

strAnswer2 = InputBox("Please enter a name for your DATA (contains the raw data to be searched file:", _
    "Input DATA File")

data = readexcel2array(StrFolder & strAnswer2, "a")

counter = 0
datacounter = 0

Do Until datacounter > ubound(data)
	
	sourcecounter=0
	Do Until (sourcecounter = ubound(source1)) or (counter = 1)
			If data(datacounter) = source1(sourcecounter) Then
				Logfile.writeline source1(sourcecounter) & "," & source2(sourcecounter)
			End If
	sourcecounter = sourcecounter + 1
	Loop
	datacounter = datacounter + 1
	counter = 0	
Loop

LogFile.Close
Set LogFile = Nothing
Wscript.echo "Done comparing"
 
 
 	 
function readexcel2array(excelfile, range)
	
	Set objExcel = CreateObject( "ADODB.Connection" )
     objExcel.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                   excelfile & ";Extended Properties=""Excel 12.0;IMEX=1;" & _
                   "HDR=NO;" & """"
				   
     Set objRS = CreateObject( "ADODB.Recordset" )
     strRange = "Sheet1$"
     objRS.Open "Select * from [" & range & ":" & range & "]", objExcel, adOpenStatic
	      ' Open the object for the Excel file
     
	 
	 i = 0
	 ReDim Preserve arrData(i + 1)
	 
     Do Until objRS.EOF

         If IsNull( objRS.Fields(0).Value ) Or Trim( objRS.Fields(0).Value ) = "" Then Exit Do
         
         if IsNull( objRS.Fields(0).Value ) Then
                 arrData(i) = ""
         Else
		         arrData(i) =  objRS.Fields(0).Value
				 ReDim Preserve arrData(i + 1)
         End If
         i = i + 1
         ' Move to the next row
         objRS.MoveNext
         ' Increment the array "row" number
         
     Loop
	 readexcel2array = arrData

end function

Open in new window

eezar21

ASKER

Thanks Kimputer, sounds good - but I'm not sure how to implement this...

ASKER CERTIFIED SOLUTION

Kimputer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

eezar21

ASKER

Thanks for the extra help with implementation