Tricky VBA Question??

[MVX]

Hi,
Have two Workbooks (1 Sheet in each) and was wondering whether the following is possible?
Each 'workbook' has;
Column 'A' - Unique employee pay number; (there may be more than one entry for the employee but column's 'x' and 'Y' are different in these cases
Column 'X' has date;
Column 'Y' has date; (Different from 'X')
Each workbook mirrors each other in number of columns/headings. The data is extrapolated from a database and can have up to 5000 rows of data on each sheet.



Trying to have row highlighted in Workbook 2 where;

• Data in column 'Y' differs between Workbooks (i.e. different date in Workbook 2 opposed to Workbook 1 where date has been changed between audits)
• Data in Workbook 1 that is not in Workbook 2 (i.e. where data row has been 'deleted' between audits)
• Data in Workbook 2 that is not in Workbook 1 (i.e where data has been 'added' to database between audit dates)

Hope this makes sense!!!

I am not sure whether a VBA script would be the right way to go about this problem so any advice would be appreciated.

 

[celestialspring]

what is suggest is this: copy all data from workbook 1 to workbook 2, or vice versa on to a new sheet in the respective document. Create a Vlookup to match the documents. You probably don't need VBA to do this. If your base workbook is Workbook 1, then match workbook 1 to 2 via VLOOKUP.

 

[MVX]

Many Thanks for that -

Copying the Workbook 2 Sheet into Workbook 1 is the obvious way to go about it! BUT I know nothing whatsoever about VLookup - Can you suggest how i would go about starting the formula


Kind Regards

 

[MVX]

Was thinking what about an if(countif) formula ?? opposed to a VLookup formula what is the best to pursue????

 

[mole999]

Many Thanks for that -

Copying the Workbook 2 Sheet into Workbook 1 is the obvious way to go about it! BUT I know nothing whatsoever about VLookup - Can you suggest how i would go about starting the formula


Kind Regards

=VLOOKUP($E2,AltRes,4,FALSE)
or
=VLOOKUP($E2,$B$2:$D$599,4,FALSE)

worth a play with, must make sure that the data you want to find is in your left hand column, i'll elucidate

the column that i want to match is held in column E (constrained by the $ symbol to stop the reference moving as you drag the formula

in my first example i have created a named ranged, its easier for me, but the second example shows it as cell references, again constrain your data with $ and you will be half way there, the value I am after is the four '4' value across the columns. False means I want an exact match.

The data you are searching for as a reference must be in the left most column of your range.

When you work with numbers as your target it will only return the closest number above, so I prefer non numeric lookup.

you could use "Eric" instead of $E2 if you wish

occasionally even when perfectly written you might find errors or the formula refuses to resolve to you intended value, its confusing, be persistant it will work, recording macros at the same time can be one issue