Compare unique references in same column of two files, clearing row of "surplus"

**fahnskap** · 01-27-2012, 10:25 AM

Good afternoon,

I've been struggling to find a solution to a problem I came across when working with a new file; I'll try my best to describe the problem I'm having.
In an effort to try and bring my problem across clearly, I will be calling File1 the "Edited" and File2 the "Master" file. These are both separate Excel files saved with macro support.

There are unique references in both of these files that will appear in cells E11:E65536 (currently it only goes as far as E89 - but it will grow over time, although 65536 rows is probably overkill). The way the reference is setup is "ABCD000456789", where "ABCD" is the only identical part of each reference.

What I'm trying to find is a macro that takes each of the unique references in cells E11:E65536 of the Edited file, and tries to find the same reference in column E of the Master file. If it finds the reference from the Edited in the Master file, it's correct and that row should be left alone. If one of the references from the Edited file cannot be found in the Master, it clears the row of the "surplus" reference in the Edited file (columns B through J and L through S).

I've searched numerous sites for a solution, but the ones I found didn't work out - so here I am. I'm not sure just how complex this would be to make, but any help on this would be greatly appreciated, as always.

Kind regards,
Christofer

**PaulMcF** · 01-27-2012, 07:22 PM

Hi Christofer,

Here's some code that might help:

Please Login or Register  to view this content.

The code runs through the references in the "Edited" file, and uses the VLOOKUP function to look for the reference in the "Master" file. If it doesn't find it, it clears the "surplus" item from the Edited file.

I hope this helps. Let me know if you need more explanation or if I missed something you need.

Cheers,
Paul

**Leith Ross** · 01-27-2012, 08:22 PM

Hello fahnskap,

This macro should do what you asked. If you have any problems, let me know.

Please Login or Register  to view this content.

**fahnskap** · 01-30-2012, 04:40 AM

Many thanks, PaulMcF and Leith Ross.

The script that Paul posted seems to delete all rows below the row that holds the "surplus" row, but I will still do my best to make use of it.

In the code that Leith Ross posted, I had to change one line (at the bottom of the listed code) to make it run, but everything appears to be going well.

Please Login or Register  to view this content.

I will post an update in the next coming days as soon as I get a chance to sit down with this, and I'll make sure to mark my topic as solved at that point. I greatly appreciate all the help I've received, you guys are awesome.

**fahnskap** · 01-31-2012, 06:51 AM

Alright, I sat down with this and everything is working perfectly. However, it brought me to a point that I realized I would have been much better off mentioning to begin with.

Leith, I was wondering if it'd be possible to have the macro first check if the "Edited" workbook is open in the background (since the macro needs to run from the "Master" one), and if it's not - to open it?

I tried to add the following code and custom function that I found at another website, but if I "cut it up" and put it in your code, I get an error (I can't seem to get it so that it does this before it executes the rest of your macro).

Please Login or Register  to view this content.

**Leith Ross** · 01-31-2012, 04:24 PM

Hello

If the macro to open the "Edited" workbook functions correctly then making a single macro to call both should work.

Please Login or Register  to view this content.

**fahnskap** · 02-01-2012, 05:46 AM

Ahh, of course. Thanks a lot for all the help!

Compare unique references in same column of two files, clearing row of "surplus"

LinkBack

Thread Tools

Rate This Thread

Display

Compare unique references in same column of two files, clearing row of "surplus"

Re: Compare unique references in same column of two files, clearing row of "surplus"

Re: Compare unique references in same column of two files, clearing row of "surplus"

Re: Compare unique references in same column of two files, clearing row of "surplus"

Re: Compare unique references in same column of two files, clearing row of "surplus"

Re: Compare unique references in same column of two files, clearing row of "surplus"

Re: Compare unique references in same column of two files, clearing row of "surplus"

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions