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.
Mike