I recently posted a question about this and I think I was pointed in the right direction, but I can't seem to get it to work. Here's the question.
I have a large quantity of transactional data and I need to eventually produce a graph that reflects the activity that occurred per hour. If the hour had no activity, it should reflect it as 0. Unfortunately, the data I have includes only the hourly increments with activity, so if no activity occurred at 1pm, it would not show up in my list. What I need to do is to rebuild the list to show every hour in the day and reflect a 0 where no activity occurred. I was directed earlier towards the lookup function which will work, but I have an added twist now. I need to match the data (in the lookup function) to two elements, date and time. So in the example below, I would have one spreadsheet that lists every hour in the day for each and every day in the month. When the spreadsheet matches the date and time, it will drop in the amt, otherwise, it adds a zero.
Date Time Amt
03/01/00 01:00 $4100
03/01/00 03:00 $500
03/01/00 04:00 $600
03/02/00 00:00 $6600
I've actually included an example of the data I'm working with. If possible the all hours tab will lookup and find matches in the partial hours tab. When there is a lookup hit on both data and time, it plugs the value in, otherwise, it leaves it at 0.
Any thoughts?
I have a large quantity of transactional data and I need to eventually produce a graph that reflects the activity that occurred per hour. If the hour had no activity, it should reflect it as 0. Unfortunately, the data I have includes only the hourly increments with activity, so if no activity occurred at 1pm, it would not show up in my list. What I need to do is to rebuild the list to show every hour in the day and reflect a 0 where no activity occurred. I was directed earlier towards the lookup function which will work, but I have an added twist now. I need to match the data (in the lookup function) to two elements, date and time. So in the example below, I would have one spreadsheet that lists every hour in the day for each and every day in the month. When the spreadsheet matches the date and time, it will drop in the amt, otherwise, it adds a zero.
Date Time Amt
03/01/00 01:00 $4100
03/01/00 03:00 $500
03/01/00 04:00 $600
03/02/00 00:00 $6600
I've actually included an example of the data I'm working with. If possible the all hours tab will lookup and find matches in the partial hours tab. When there is a lookup hit on both data and time, it plugs the value in, otherwise, it leaves it at 0.
Any thoughts?