Transactions Per Hour

[sa7toot]

Hi all,

I want to run a query which identifies the number of transactions conducted per account per hour and the amount. my table have 3 columns
A: Account Number
B: Transaction date & time
C: Transaction Amount

I have searched the forum q's but didn't see anything relevant so far. So, appreciate your help.

thnx
Sa7toot

 

[RasGhul]

Can you post some sample data, so we can check it out for you.

 

[DSCfromCFA]

Unless you are specifically looking for a VBA programming solution (which I suspect you are), then getting familiar with COUNTIFS, SUMIFS and the HOUR function would seem to be in your future, as they could do it relatively easily in my view.

An indication of the size of your listing will also help, as again I suspect that it is going to be quite long

Cheers

shane

 

[sa7toot]

[TABLE="width: 100"]
<tbody>[TR]
[TD]A/C
[/TD]
[TD]Date & Time
[/TD]
[TD]Amout
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]
[TABLE="width: 103"]
<tbody>[TR]
[TD="width: 137, bgcolor: transparent, align: right"]31/07/2019 00:08:31
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]
[TABLE="width: 103"]
<tbody>[TR]
[TD="width: 137, bgcolor: transparent, align: right"]31/07/2019 00:09:16
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]
[TABLE="width: 103"]
<tbody>[TR]
[TD="width: 137, bgcolor: transparent, align: right"]31/07/2019 00:19:51
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]
[TABLE="width: 103"]
<tbody>[TR]
[TD="width: 137, bgcolor: transparent, align: right"]31/07/2019 00:39:50
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]
[TABLE="width: 103"]
<tbody>[TR]
[TD="width: 137, bgcolor: transparent, align: right"]31/07/2019 00:49:08
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]
[TABLE="width: 103"]
<tbody>[TR]
[TD="width: 137, bgcolor: transparent, align: right"]31/07/2019 00:50:10
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]55
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]
[TABLE="width: 103"]
<tbody>[TR]
[TD="width: 137, bgcolor: transparent, align: right"]31/07/2019 01:27:16
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]
[TABLE="width: 103"]
<tbody>[TR]
[TD="width: 137, bgcolor: transparent, align: right"]31/07/2019 03:59:20
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]
[TABLE="width: 103"]
<tbody>[TR]
[TD="width: 137, bgcolor: transparent, align: right"]31/07/2019 04:00:14
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]
[TABLE="width: 103"]
<tbody>[TR]
[TD="width: 137, bgcolor: transparent, align: right"]31/07/2019 06:39:54
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]105
[/TD]
[/TR]
</tbody>[/TABLE]
Hello,

Below is a sample of the data, in any particular day, there are around 100K to 200K records to review. I am trying with COUNTIFs and SUMIFs but so far failing.

 

[DSCfromCFA]

I'm about to finish for the day, but a few quick questions,

Is the time in 24 hr format?
Are the hour increments across the full 24 hr day?
Do you want a full report, I.e. every account, every hour, or do you just want to examine an account and an hour I.e selectable with drop down lists?
Does the list have multiple days, and if so over what range?

Cheers

shane

 

[sa7toot]

Hello Shane,

Yes, the time is in 2 hours format.
Yes the our increments the full 24 hr day.
I would want the output to show a segregation of transactions by count. for example, how many accounts done 1 in an hour or 2 or 3 or 4 or 5 & more. maximum segregation is 5 and whatever exceeds is in "more than 5" row .
the list will be run on a rolling period, it could contain days.

 

[Sam_D_Ben]

You mean like this.

Excel 2013/2016
	A	B	C	D	E	F	G	H
3	Date	ID	0-1	1-2	3-4	4-5	6-7	Total
4	31-07-2019	1	3		1	1		5
5	31-07-2019	2	1	1			1	3
6	31-07-2019	3	2					2
Sheet11

Muz

 

[RasGhul]

Muz can you post your formula for sa7toot as well pls mate?

 

[Sam_D_Ben]

@ RasGhul, I did Pivot after little bit changes in his frame, just to see how he want the result.

 

[sa7toot]

@RasGhul,
Well yes, sort of. Basically, I am looking to show what is the number of customers who do more than 3 transactions per hour in a day.

thanks