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?

Who is Participating?
KimputerConnect With a Mentor Commented:
copy & paste the code, save it in d:\temp\ as list.vbs
If you want to use c:\temp, also fine, but adjust it in the source code.
Have to 2 excel files there too (source with the domains you need, and the raw data which to check against the source).
Now, double click the vbs file, a prompt will pop up, enter the file name if the source excel file (the one with less domains), press enter, now it will ask  for the raw data (the ones with lots of domains and the extra column), press enter, wait for it to be done, now you can check results.txt, it will have the source domains,with the number behind it.

Depending on your needs, it can also be converted to VBA, then you have to do this within Excel, and there can be file dialogs (for easier navigation to the files), and the results will be in a new unnamed excel file which you will have to save. Let me know if you need this.
Some more sample data of the two files, and how you want the results returned would be nice.
eezar21Author Commented:

I've tried to illustrate it in the attachment
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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
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)
	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
	datacounter = datacounter + 1
	counter = 0	

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) = ""
		         arrData(i) =  objRS.Fields(0).Value
				 ReDim Preserve arrData(i + 1)
         End If
         i = i + 1
         ' Move to the next row
         ' Increment the array "row" number
	 readexcel2array = arrData

end function

Open in new window

eezar21Author Commented:
Thanks Kimputer, sounds good - but I'm not sure how to implement this...
eezar21Author Commented:
Thanks for the extra help with implementation
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.