Combine multiple values in rows based on related time (hours)

**JuJuBe** · 06-08-2010, 11:53 PM

See example. I need to combine multiple rows, (that represent transactions), that are within 1 hour of each other. Rows 25 & 26 are only 7 minutes apart and need to be counted as 1 transaction. I thought about combining costomer code & date shipped to get a unique value them using some sort of countif or sum if???

After combining the data I plan on making a pivot table with 'Customer Code' in the rows, 'Day of month' in columns and 'count of order #' in values but I anm open to something else that can give me the same results. Thank you very much

**DonkeyOte** · 06-09-2010, 03:05 AM

Just to be clear ... what happens in the following scenario:

Three transactions occur for the same customer with a span from first to last of 1:45 but with each transaction occurring within 1 hour of the last, ie (ignoring date):

10:00
10:50
11:45

How many "transactions" does such a scenario represent ?

**JuJuBe** · 06-09-2010, 09:24 AM

Great point DonkeyOte, I should have been clearer. In your example only the 10:00 and 10:50 should be combined. Somehow establishing a base using the earliest time might be a problem. Is this scenario below, the 10:00 and 10:50 should be combined and also the 14:15 and 14:46.

10:00
10:50
11:45
14:15
14:46
16:12