Excel 2007 and Security key cards

[mikehseven]

Can anyone please help me with an interesting problem which concerns Excel 2007 and the security type key cards which are used to open an electronic lock to gain entrance to a building?
The key cards are numbered 100 thro’ 140, 200 thro’ 240, 300 thro’ 340, 400 thro’ 440 and 500 thro’ 540.
The key card software produces data which can be imported into Excel 2007 in the following manner:-
Sheet 1
Cell A1 contains the date on which the key card was used. (DD/MM/YY format)
Cell B1 contains the time that the key card was used. (This column is not pertinent to this problem)
Cell C1 contains the identification number of the key card used to open thedoor.
The dates in column A start at the earliest and proceed down the sheet, inorder, to the latest date. There will be approximately 31 dates, It is possible though extremely unlikely that all the key cards may not be used on one or more days. Most key cards will be used one or more times each day. Some will not be used at all on some days.
The key card numbers in column C are in random order.
The result that is required is to find out which key cards were NOT used and on which days they were not used. (Sort of like an absentee check)
The desired readout would be as follows:-
Sheet 2
Cell A1 contains key card number 100, Cell B1 contains key card number 101 and so on across the sheet listing all of the key card numbers.
The desired result when the formulae run would be:-
Starting at A2 and working down the column, in order, would be all the dates that the key card 100 was NOT used. Similarly for column starting at B2, in order, would be all the dates that the key card 101 was NOT used. And soon all across the sheet.
Misc. Info
Sheet 1 Column A will typically have about 31 dates, each one occurring up to approximately 300 times.
Sheet 1 Columns A, B & C will typically have about 10000 rows.
There may be typically up to approximately 30 key card numbers which are not used on any specific day.
Mike

 

[Auric__]

Can anyone please help me with an interesting problem which concerns
Excel 2007 and the security type key cards which are used to open an
electronic lock to gain entrance to a building? The key cards are
numbered 100 thro' 140, 200 thro' 240, 300 thro' 340, 400 thro' 440 and
500 thro' 540. The key card software produces data which can be imported
into Excel 2007 in the following manner:- Sheet 1
Cell A1 contains the date on which the key card was used. (DD/MM/YY
format) Cell B1 contains the time that the key card was used. (This
column is not pertinent to this problem) Cell C1 contains the
identification number of the key card used to open the door.
The dates in column A start at the earliest and proceed down the sheet,
in order, to the latest date. There will be approximately 31 dates, It
is possible though extremely unlikely that all the key cards may not be
used on one or more days. Most key cards will be used one or more times
each day. Some will not be used at all on some days.
The key card numbers in column C are in random order.
The result that is required is to find out which key cards were NOT used
and on which days they were not used. (Sort of like an absentee check)
The desired readout would be as follows:-
Sheet 2
Cell A1 contains key card number 100, Cell B1 contains key card number
101 and so on across the sheet listing all of the key card numbers. The
desired result when the formulae run would be:- Starting at A2 and
working down the column, in order, would be all the dates that the key
card 100 was NOT used. Similarly for column starting at B2, in order,
would be all the dates that the key card 101 was NOT used. And so on all
across the sheet.
Misc. Info
Sheet 1 Column A will typically have about 31 dates, each one occurring
up to approximately 300 times. Sheet 1 Columns A, B & C will typically
have about 10000 rows. There may be typically up to approximately 30 key
card numbers which are not used on any specific day.

This sounds like some sort of homework problem to me... shrug.

This is somewhat inelegant, and there are a few bits that really bother me,
and there is ABSOLUTELY NO ERROR CHECKING AT ALL, but I'll write all that
off to "I'm tired" (and also "I'm not that great of a coder"). It works
(for me, anyway) and to me, that's pretty important.

So. This sub is what you want:

Sub absenteeCheck()
Dim cards(100 To 540) As Boolean
Dim working As Worksheet
curdate = Sheet1.Cells(1, 1).Value
Set working = Sheets.Add
working.Activate
'column headings, plus filler
For L1 = 1 To 5
For L2 = 0 To 40
Cells(1, ((L1 - 1) * 41) + L2 + 1).Value = (L1 * 100) + L2
Cells(2, ((L1 - 1) * 41) + L2 + 1).Value = 0
Next L2
Next L1
For L0 = 1 To Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row
If Sheet1.Cells(L0, 1).Value <> curdate Then
'report absenteeism for this date
For L1 = 1 To 5
For L2 = 0 To 40
If Not cards((L1 * 100) + L2) Then
Cells(Cells(1, ((L1 - 1) * 41) + L2 + 1).End(xlDown).Row + 1, _
((L1 - 1) * 41) + L2 + 1).Value = curdate
End If
Next L2
Next L1
Erase cards()
curdate = Sheet1.Cells(L0, 1).Value
Else
cards(Sheet1.Cells(L0, 3).Value) = True
End If
Next L0
Cells(2, 1).EntireRow.Delete
Set working = Nothing
End Sub

This creates a new sheet for the report every time it's run. If you don't
want that... again, I'm tired, so I leave it as an exercise for the
student. (Note that if the data isn't actually on a page called Sheet1
you'll need to make changes.)

This sub...

Sub setup4testing()
x = 41275
For n = 1 To 10000
If (n Mod 323) = 0 Then x = x + 1
Cells(n, 1).Value = CDate(x)
Cells(n, 3).Value = Int(Rnd * 41) + ((Int(Rnd * 5) + 1) * 100)
Next
End Sub

....will set up the current page with random sample data for testing. Open a
new workbook and run setup4testing(), then run absenteeCheck(). If the
generated report page is what you're expecting, you're good to go.

 

[Michael Harrison]

Can anyone please help me with an interesting problem which concerns Excel 2007 and the security type key cards which are used to open an electroniclock to gain entrance to a building?

The key cards are numbered 100 thro’ 140, 200 thro’ 240, 300 thro’ 340, 400 thro’ 440 and 500 thro’ 540.

The key card software produces data which can be imported into Excel 2007in the following manner:-

Sheet 1

Cell A1 contains the date on which the key card was used. (DD/MM/YY format)

Cell B1 contains the time that the key card was used. (This column is notpertinent to this problem)

Cell C1 contains the identification number of the key card used to open the door.

The dates in column A start at the earliest and proceed down the sheet, in order, to the latest date. There will be approximately 31 dates, It is possible though extremely unlikely that all the key cards may not be used onone or more days. Most key cards will be used one or more times each day. Some will not be used at all on some days.

The key card numbers in column C are in random order.

The result that is required is to find out which key cards were NOT used and on which days they were not used. (Sort of like an absentee check)

The desired readout would be as follows:-

Sheet 2

Cell A1 contains key card number 100, Cell B1 contains key card number 101 and so on across the sheet listing all of the key card numbers.

The desired result when the formulae run would be:-

Starting at A2 and working down the column, in order, would be all the dates that the key card 100 was NOT used. Similarly for column starting at B2, in order, would be all the dates that the key card 101 was NOT used. And so on all across the sheet.

Misc. Info

Sheet 1 Column A will typically have about 31 dates, each one occurring up to approximately 300 times.

Sheet 1 Columns A, B & C will typically have about 10000 rows.

There may be typically up to approximately 30 key card numbers which are not used on any specific day.

Mike

Hi Auric,
Thank you for your help. Your program works perfectly. This is not a homework problem. I am a board member of a condominium association and I have been trying to squeeze as much useful information out of the security card software as possible mainly by sorting it and graphing the monthly entries of selected residents to look for changing patterns. We are mainly concerned with the people who sublet without telling us and the people who do the “midnight moves over the balcony”, but this may also be useful, although I hope it never happens, if one of our reclusive residents dies. I have used Excel quite a lot over the years and I really like it, but I am very much anovice with it when it comes to something complicated like this. This is the second time I have had to go to the group and hope that someone would take pity on me. The last time was about 8 years ago when someone helped me with a sorting formula. Their help saved me many hours and yours will be a great help to me too. Thank you once more.
Mike

 

[Auric__]

Hi Auric,
Howdy.

Thank you for your help.

No prob.

Your program works perfectly. This is not a homework problem. I am a board
member of a condominium association and I have been trying to squeeze as
much useful information out of the security card software as possible
mainly by sorting it and graphing the monthly entries of selected residents
to look for changing patterns.

In that case, I suggest renaming "absenteeCheck" to something more obvious,
and state its purpose in comments at the beginning, in case you leave the
board and someone else takes over the checking.

(The "homework" comment is because it really *does* look like homework to
me.)

We are mainly concerned with the people who sublet without telling us

That's allowed? When I lived in a condo, it was "owner occupation ONLY".
(Don't really know how the HOA would enforce the rule, or even know.)

and the people who do the “midnight moves over the balcony”, but this
may also be useful, although I hope it never happens, if one of our
reclusive residents dies.

Gonna happen eventually. A recluse is probably far more likely to die at home
unnoticed than anyone else. You checking the logs once in a while will mean
that it doesn't necessarily take a year (or whatever) for them to be found.

I have used Excel quite a lot over the years and I really like it, but I
am very much a novice with it when it comes to something complicated
like this. This is the second time I have had to go to the group and
hope that someone would take pity on me. The last time was about 8 years
ago when someone helped me with a sorting formula. Their help saved me
many hours and yours will be a great help to me too. Thank you once
more.

The whole thing took me maybe half an hour to bang together. Shrug.