Identifying times from a list

[tourgis2000]

Hi,

I have a list of variables created by a user like this:

BAND ID START END
Band1 06:00 08:59
Band2 09:00 15:59
Band3 16:00 17:59
Band4 18:00 22:30
Band5 22:31 24:29
Band6 24:30 05:59

Seperately I have data from a third party like this:

DATE TIME VALUE
18/05/08 17:22 10
19/05/08 02:15 1
20/05/08 07:14 5
20/05/08 21:36 15
20/05/08 25:43 1
21/05/08 19:15 15
22/05/08 18:26 16
22/05/08 24:59 2
etc.

I need to be able to identify which BAND ID belongs to each TIME in the list so that I can sum the VALUE figures for each BAND ID (e.g. the first time given,17:22, would belong to Band 3). have tried using LOOKUP but I can't seem to get it to work. Additionally, I have a problem with the 30 hour clock. Would I have to stick to a 24 hour clock? This would mean that I would have to find each time from the list that was between 24:00 and 05:59 and allocate it to the previous day because it is important to keep the values that I ultimately derive in the right day of the week.

Thanks in advance,

Martin

 

[SydneyGeek]

How big is "very big"? I usually try to stay below a few MB for formula-intensive files but I have worked with some pivot table-based files in the high teens, without trouble.
Obviously the smaller the better, but files of that size are pretty stable.

If you really want to get fancy you can create the PT in code on a new sheet, copy the data over as values, then delete the PT sheet. Takes no more than a couple of seconds to run, the file stays small, and you avoid the overhead of SUMIF formulas covering hundreds or possibly thousands of rows.

Denis

 

[tourgis2000]

High teens maximum I'd say. My purpose in this is to provide users with the quickest way possible (without using VBA) of producing a number of generic charts and dumping them into PowerPoint. I will produce a version with a number of pivots (and pivot charts as well) and compare speed to my more SUMIFS intensive version.

Thanks

Martin