new to vba ...need to compare 2 spreadsheets

[mlttkw]

I know very little VBA (very adept in SQL) and I am hoping someone could assist me so I can learn from this initial effort.
I want to compare two spreadsheets with the same column order and naming convention. However, I want to highlight the differences in the base spreadsheet (Sheet1) that only has a subset of data of the other sheet (Sheet2). There is a primary key (Column A) in both spreadsheets.
Desired workflow:
IF Colum A id Sheet 1 is in Column A Sheet2, then compare each adjacent column for differences.
Example: If Sheet1!Column A1 id = Sheet2!A9 id, then compare Sheet1!B1 to Sheet2!B9, so and so on.
Highlight the differences on Sheet1
Also, I have some disparate values for some columns that should be treated as matches.
For Example column C in both Sheets need to be equal when the following occurs
Sheet1!column C value is “NEW” and Sheet2!columnC value is “Pipeline” they should be a match
Any help with this is greatly appreciated and will serve as a basis for me in the future.

[xladept]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

[mlttlk]

hi ...here is the attachment

[xladept]

I put them in different sheets - do that and try:

Please Login or Register  to view this content.

If you haven't used macros before you'll need to go to:
File- options - trust center -trust center settings - macro settings ,
the second option down (disable all macros with notification)

Then - Copy the code to the clipboard

Open your Workbook

Press ALT + F11 to open the Visual Basic Editor.

Select "Module" from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name
*Be sure to save the book with the code as Macro-Enabled

[mlttlk]

Absolutely awesome!! Now I can start learning!! thank you.

thank you again

[xladept]

You're welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[mlttlk]

yes it does take care of my question...thanks again. However, once I select Thread Tools there are only 3 choices, none of which allow me to mark it as SOLVED.

the choices are:

Show Printable page
Email the page
Subscribe to this thread

am I missing something?

[xladept]

Well - you've got two different handles - see if it works on post#1

[mlttlk]

hey ...I have a problem I discovered that I can't solve yet. I have created web connections that refresh the data in both sheet1 and sheet2. Once I added the connections the macro no longer works. any ideas?

[xladept]

Does it throw an error?

[mlttlk]

Oh crap ...user error!!!!!!!

[xladept]

I've got to go to work now - let me know how it resolves.