formula or Vba code to search through worksheet

[beeryman]

Hello group,

Some advice needed. We have a task to do overnight that entails checking if netbackup jobs run and tick a relevant box in a checksheet if they have.

Here is an example of that checksheet:-

policy Name Mon Tue WED Thur Fri

server Name tick tick ........

There are 20 pages of these to do, covering approx 600 servers.

What we currntly do is cut and paste the "activity monitor" display from Netbackup into an excel worksheet and then appy the auto filter and then filter the display for each each Policy Name and manually tick the monday to Friday box on our checksheet if they have run. This process takes a good hour and a half to do.

My question is, is it possible by means of a formula inserted into the checksheet in the Mon Tue etc columns
to take the "server name" search the pasted "activity monitor" display and return a Y or N depending on if "server name" has run., or will this only be possible using VBA code.
I had a look the other night and just seemed to go round in circles and get nowhere.
Some tips to point me in the right direction would be greatly appreciated.

Thanks in advance


Bob

 

[Gavona]

vlookup might well do it (or match....). What is the layout of the activity monitor?

Gavin

 

[beeryman]

Thanks gavin,

Here are a few lines of an Activity Monitor dispaly:-


A B C D E F G H I

119 Backup Done 1 Fri Jan 02 19:00:41 GMT 2009 Fri Jan 02 20:45:19 GMT 2009 Bob_policy_1 gblonas69.uk. 2037
374 Backup Done 0 Fri Jan 02 19:00:41 GMT 2009 Fri Jan 02 19:01:39 GMT 2009 Bob_policy_1 gblonas69.uk. 6389
439 Backup Done 1 Fri Jan 02 19:00:41 GMT 2009 Sat Jan 03 10:48:29 GMT 2009 Bob_policy_2 gblonda04.uk 7495
160 Backup Done 0 Fri Jan 02 19:00:41 GMT 2009 Fri Jan 02 19:50:19 GMT 2009 Bob_policy_2 gblonda04.uk 2737
116 Backup Done 58 Fri Jan 02 19:00:41 GMT 2009 Fri Jan 02 19:10:39 GMT 2009 Bob_policy_2 gblonda04.uk 1987
248 Backup Done 0 Fri Jan 02 19:00:16 GMT 2009 Fri Jan 02 19:20:59 GMT 2009 Bob_policy_3 eidnpedir04.uk 4238
412 Backup Done 1 Fri Jan 02 19:00:16 GMT 2009 Fri Jan 02 20:19:49 GMT 2009 Bob_policy_4 eidnpedir03.uk 7029
389 Backup Done 0 Fri Jan 02 19:00:16 GMT 2009 Fri Jan 02 21:20:49 GMT 2009 Bob_policy_5 ukbdf11as09.uk 6634


Currently we do an AUTO FILTER on column G (the policy, in this case Bob_policy_x). This then gives us the servers that are backed up under that policy. As you can see Bob_policy_2 has 3 jobs (they might be all of the same name or different depending if the backup is multi-plexing or not. We then check across in columne C for DONE or ACTIVE. From here we then tick our checksheet indicating that for that particular server a backup has run. We don't need to know how many instances of a backup runs. As long as there is at least an entry as above then for the purpose of this cheeksheet we tick it.
The reason we use the policy is that because we are doing the task manually it is much quicker. However if a formula will work then I can use the server Name instead of the Policy Name.

policy Name Mon Tue WED Thur Fri

server Name tick tick


Sorry to be so long winded but I hope this explains what we do and what I hope to achieve by means of a formula.


Bob

 

[SkipVought]

Hi,

You ought to be able to use the PivotTable Wizard to generate a report by server across days (using the Group feature) and COUNT of Server rather than TIC.

Here are my results (after massaging the date columns to remove the DAY and 'GMT' about 3 minutes of work)
[tt]
Status Done

Count of Server BackupEnd
Server 2-Jan 3-Jan
eidnpedir03.uk 1
eidnpedir04.uk 1
gblonas69.uk. 2
gblonda04.uk 2 1
ukbdf11as09.uk 1
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[beeryman]

Thanks Skip,

I tried your suggestion this weekend and it worked great.
Using the server list in the row field it almost returns exactly what i need. Is it possible to stop the the Pivot table from sorting the "row field" column. I'm losing the order that the tick list is in.
I have the Autosort option for server field set to manual
but still it sorts the data.

Many thanks.

 

[SkipVought]

Add a SEQ field to your source data and then PT this field as primary, sort, then hide the column if necessary.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}