Solved

VBA to perform a search from one file to another.

Posted on 2014-01-20
3
223 Views
Last Modified: 2014-01-27
I need to search a file that has over 200,000 rows. If the specific reference in my file is on the other file I need the macro to put Yes in my file and if its not there to put No.

So..... the file is in the path G:\Clients\Open Invoice\Open Invoice - UPDATED.xlsx

My file [the one with the Macro] needs to look up the Reference in C5 [on tab called PD3] and see if this is on the file ‘Open Invoice - UPDATED.xlsx’. If the Ref is found enter 'Yes' in my file [on tab called PD3] cell J6 and if not found put 'No'
0
Comment
Question by:Jagwarman
  • 2
3 Comments
 
LVL 19

Accepted Solution

by:
regmigrant earned 500 total points
ID: 39794523
You don't need a macro just a vlookup:-

in D5 (the column you want yes or no in) put:
=If(iserror(VLOOKUP($C5,'[G:\Clients\Open Invoice\Open Invoice - UPDATED.xlsx]Sheet1'!$A:$A,1,0)),"No","Yes")

- you may need to change the 'sheet1' to refer to specific sheet and the range $A:$A to refer to the appropriate column

Copy the formula down all of the rows in your target spreadsheet

Reg
0
 

Author Comment

by:Jagwarman
ID: 39802336
regmigrant bizzarley I could not get this to work but, I changed iserror to ISNA and it does work. Fingers crossed that it will always return the correct vallue.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39802460
iserror should work (for excel 2003 and up) but hopefully you will be ok with ISNA - good luck
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now