Please help me out in this

[Closed]
Report
-
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
-
Hello,
i have 2 sheets in ms excel 2003 on sheet 2 i have a master with 1000 employees data like headings are in A2 id number,B2 name and so on the actual data starts from A3. and on sheet1 ihave time keeping data this also starts as the first sheet.
i want to compare both the sheets to know how many employees are not present today. i wrote a micro but it is working as long as the id numbers in both the sheets are same or identical if one id is missing in time keeping sheet (if the employee is absent) then from there the id is not matching in both the sheets and the macro is giving me all absent for the rest of the present employees. please help me in this.
i wrote a macro like this

Sub arr()
Set a = Sheets("sheet1")
Set b = Sheets("sheet2")


Dim x
Dim z
z = 3
x = 3

Do Until IsEmpty(a.Range("A" & z))
If a.Range("A" & z) = b.rnage("A" & z) Then

b.Range("D" & x).Copy Destination:=a.Range("D" & x)

Else
b.Range("E" & x).Copy Destination:=a.Range("D" & x)

End If

x = x + 1
z = z + 1
Loop
End Sub

1 reply

Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Start Macro recording
Put the following in any cell in row 3, =countif('Destination sheet name'!SOurceRange, employee code/name) and
drag till the end
you will get 1 or 0 in these cells .
then add auto filter
filter for 0 to get the persons who were absent
stop recording

You only have to change the destination filename in every case