Closed
baroom - May 16, 2009 at 12:57 AM
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 - May 18, 2009 at 01:07 AM
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 response

Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
May 18, 2009 at 01:07 AM
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 .