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
eezar21Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KimputerCommented:
Some more sample data of the two files, and how you want the results returned would be nice.
0
eezar21Author Commented:
Sorry....

I've tried to illustrate it in the attachment
Example.xlsx
0
KimputerCommented:
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

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

eezar21Author Commented:
Thanks Kimputer, sounds good - but I'm not sure how to implement this...
0
KimputerCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eezar21Author Commented:
Thanks for the extra help with implementation
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.