Excel - Insert Missing Records Sequence of events (hours)

[jmgibson]

All,

I have an extensive amount of data that includes two columns. Time and Amount ($). Each record in the table reflects a detailed transaction that occurs. Transactions can occur throughout a 24 hour period, however, they may not always occur each and every hour. What I'm trying to do is find an automated way to insert a record with the next hour in a sequence that did not have any activity. For instance, in this example, I would like to insert a record after 01:00 to reflect an entry for 02:00 with $0.

Time Amt
01:00 $4100
03:00 $500
04:00 $600

I'm extracting this data from an Access Database, so if there's a way to do it from there, I'd appreciate those thoughts as well.

 

[SkipVought]

Hi,

The simplest way is to have a table with each time element you want. Then use a lookup to fill in the amount from your existing table. hint: use ISNA to test for no data and return a value that makes sense, like 0 or "".

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]