Efficiency advice. Finding record status on a sheet to update another sheet.

(Excel 2010 on WinXP and Win7)

My head is not working again. The way I'm putting this together it will take 10 minutes to run. This is a very small part of a large script and I can't afford it to take a long time to run.

I have a sheet called 'InfraDump' and a sheet called 'GKinfra'.

InfraDump has Login ID in column A and Access Status in column I (This address is found and noted as a variable in case it moves in the future)
The same user ID may be repeated with differing entries in the Access Status
There are only 3 possibilities of Access Status, 'Requested', 'Pending Deletion' and 'Active'.
If a Login ID has some Active and some Requested or Pending Deletion, then I count that access as 'Requested'.

GKinfra has each Login ID listed only once.

I need to find Login IDs in InfraDump that have Requested or Pending Deletion against any of the entries and in C column of GKinfra put in "Request Pending" on the appropriate line.
Ignoring Active for now.


- Like most of the examples I can find on the forums, I can do this by checking each row and verifying that an entry has not already been added for the Login ID in the GKinfra sheet. But this seems slow as it will need to examine each of the 1200 rows to find the 20 or less that are not active.

- The other way around is to go through each of the Login ID cells in GKinfra and try to find a match on the InfraDump sheet. This is again examining a lot of lines that it shouldn't have to.

- I guess I can filter out the Active and then find each of the visible LAN IDs and add the Request Pending comment.
This seems like the most efficient method to me so far.


The way I've been working this has been so wrong.
I was going to be searching for the keyword (as a variable so I could search for them seperately), making the cell address a variable for later use, getting the Login ID, going to the GKinfra sheet (using activate which I know is slow), finding the Login ID and entering the comment, going back to the InfraDump sheet, searching again from the cell last found (offset 1 row) and doing it all over again.
Each positive hit would have involved 4 Activate commands. Bad, bad, bad and slow.


Do you agree that the filter option would be the fastest method? It will still have 1 cell Activate per line, but only for 20 or so lines.

Not played with arrays for too long and forgotten how. Maybe that is the best direction for me to investigate and try to relearn.

I could attach a dummy workbook or show my crazy original code, but for now I just need some advice on what method I should be using for efficiency. I think I can work it out from there.

Okay, I've worked it out.

Please Login or Register  to view this content.