PDA

View Full Version : VBA to Compare 2 Workbooks with Criteria and Paste Differences



pyrosax
08-22-2014, 11:20 AM
Hello all you fine Excel/VBA professionals lol (WORKBOOKS AND EXAMPLES ARE ATTACHED)

I need your assistance, because I have no clue what I am doing. :bug:

I have two workbooks. ERP and qry_to_xls_Laborcharges_new.

Each one has some columns that are the same, and some that are different.

The ones with the same data (Although different names) are the ones we care about

I would like to have a VBA code that grabs the data from both workbooks and looks at it like an INNER JOIN and OUTER JOIN.

The example workbook will provide the only 4 cases that will occur.

EXAMPLES 1 & 2 Need not to be reported, but I put it there to ensure there is no confusion.

EXAMPLES 3 & 4 Need to be reported. #3 would resemble an OUTER JOIN. #4 would resemble an INNER JOIN Where Hours is not Equal

The complication comes when both EMPLOYEE NAME columns are not the same. I.E. Dingle, Elmer is in "qry_to_xls..." and ELMER J DINGLE appears in ERP.

I think I clarified it well. If you need any more clarification, please let me know lol

SamT
08-25-2014, 03:11 PM
Pyrosax, Welcome to VBAExpress.

While it is possible to use code to compare the different name formats, that will not help with Mary K. Smith and Mary C. Smith.

It is easier to manually create a cross index, however, the same problem of the two Marys will still be there.

The only solution that is foolproof is the have the employee number added to the qry_to_xls Book.

I did take the time to download all three books of yours (about a Megabyte of bandwidth,) and create a new Examples book with just enough data from the two big books to show the layout of their data. Each book's data is on a separate sheet, with an extra sheet showing only the Field Names.

For clarification; The ERP "Network" field appears to be the same data as the qry_to_xls "Charge Number" Field.

While I highly recommend that your organization adds the Employee Number field to all Employee reports, this may not be politically possible. In that case you should create a cross reference between the two name formats as it will require much less problematic programing skills. I would pull ther last name from the ERP format with VBA's InStrRev and Right Functions into a separate column, then sort both columns on the last name only column. Place them next to sorted column from the qry_to_xls format and you will have your index.

Then NEVER hire two Mary Smiths.

Please let us know what is happening.

SamT

pyrosax
08-26-2014, 08:39 AM
Love the comedy lol

But just to be clear, in this instance, the names will always be unique to their First and Last Names. :)

You are right about the "NETWORK" and "CHARGE CODE" Being the same data. that is what needs to be equal first.

I have attached the workbook with the two tabs of "Sample data", highlighting specific fields that we need to see. The others are not really of any concern.


Also, I would cringe if i had 2 Mary Smiths employeed, She does not sound like an effective worker. Especially if she needs a clone to help. :P