Lookup Function - Populating

[jmgibson]

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?

 

[AnotherHiggins]

Have you looked into using Pivot Tables?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

If you do want to stick with the format you have, there are a few ways to proceed.

Leaving everything the way it is, you could use this in cell C2 then fill the formula down:
[tab][COLOR=blue white]=SumProduct(('# Partial Hours'!$A$2:$A$1000 = $A2) * ('# Partial Hours'!$B$2:$B$1000 = $B2) * ('# Partial Hours'!$C$2:$C$1000))[/color]

Another option is to combine date and time into a single cell. You can just add them together, like =A2+B2. If you wanted, you could still format the cell to only show the date even though it would also contain time data. See faq68-5827 for more information on how Excel deals with dates and times. That would allow you to use whatever lookup you were using before.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[jmgibson]

Pivot Tables may do the trick. I'm not sure I understand what the formula is doing in your second post. Can you ellaborate?

 

[AnotherHiggins]

Given your example, you should be able to just paste that formula into C2 on the "All hours" sheet to see it work.

Inside the "Sumproduct" function,
- the first part finds rows where '# Partial Hours'!$A$2:$A$1000 is equal to A2

- the second part finds the rows isolated in the first part where '# Partial Hours'!$B$2:$B$1000 is also equal to B2

- The last part adds up the value in # Partial Hours'!$C$2:$C$1000 for any record that met the first two criteria

If you had included a criterion in the third part ( ... * ('# Partial Hours'!$C$2:$C$1000 [!]= 1000[/!]) ), then it would have counted how many records met all three conditions. But since there's no criterion listed it just adds up the values in column C.

Sorry - it's time to go home so I know that was a rather hasty explanation. If you search the site for SumProduct you should find several examples.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Lilliabeth]

http://www.dataschenk.com/Newsletters/Tips/ExSumproduct.htm

Lilliabeth
-Why use a big word when a diminutive one will do?-