Excel VBA. Compare 2 worksheets.

[u473]

I need to filter to a new workbook for exceptions of potential
mischarges, by comparing
a Table of Actual Charges Vs a Table of Allowed Charges by Employee
and Project.
The Table of Actual Charges may vary, plus or minus for Employees or
Projects.
..

Workbook A Sheet1 - Table of Allowed Charges.
Employee Project W Project X Project Y Project Z
Emp A Y Y
Emp B Y
Y <--- "Y" stands for allowed.
Emp C Y
Emp D
Y
..
Workbook B Sheet1 Table of Actual Charges [With a twist : One new
Employee, One new Project]
Employee Project V Project X Project Y Project Z
Emp A 140 20
Emp B 80 60
20
Emp C 70 90
Emp E 40
120
..

Worbook C Sheet1 Table of Potential Mischarges, resulting from
Workbook B Sheet1
Actual Charges, not matching Workbook A Sheet1 - Table of Allowed
Charges.
Employee Project V Project X Project Y Project Z
Emp B 80 60
Emp C 90
Emp E 40
120
..
From this result table, I would update Workbook A Sheet1, if charges
are justified.
Help appreciated
J.P.

 

[Bernie Deitrick]

I'm assuming that in all of your workbooks, your projects are along the top (Row1) , and all
employees along the side(column A).
In workbook C, enter all of your projects and all of your employees: Then in cell B2, enter the
formula

=IF(OR(ISERROR(MATCH(B$1,'[Workbook B.xls]Sheet1'!$1:$1,FALSE)),ISERROR(MATCH($A2,'[Workbook
B.xls]Sheet1'!$A:$A,FALSE))),"",IF(OR(ISERROR(MATCH(B$1,'[Workbook
A.xls]Sheet1'!$1:$1,FALSE)),ISERROR(MATCH($A2,'[Workbook
A.xls]Sheet1'!$A:$A,FALSE))),"",IF(INDEX('[Workbook A.xls]Sheet1'!$1:$10000,MATCH($A2,'[Workbook
A.xls]Sheet1'!$A:$A,FALSE),MATCH(B$1,'[Workbook A.xls]Sheet1'!$1:$1,FALSE))="Y","",INDEX('[Workbook
B.xls]Sheet1'!$1:$10000,MATCH($A2,'[Workbook B.xls]Sheet1'!$A:$A,FALSE),MATCH(B$1,'[Workbook
B.xls]Sheet1'!$1:$1,FALSE)))))

and copy it over and down to match your table. Increase the two references to $1:$10000 to match
your actual number of rows.

HTH,
Bernie
MS Excel MVP

 

[u473]

Ok, it works fine, thank you. I was worried with that long formula. I
am impressed.
Of Course, in my Workbook C, before entering your formula, I had
entered all new Employees and Projects.
In actual practice, how would I filter all new Employees in Col. A
and new Projects in Row 1 in Workbook B to update Workbook A before I
read the resulting exceptions in Workbook C.
Thank you again,

J.P.

 

[Bernie Deitrick]

Perhaps, only put the employees and projects of interest into workbook C....

HTH,
Bernie
MS Excel MVP