Find any transactions totaling $10,000+ within 24 hour period for the same customer

[rexdjones]

I have a large amount of data I need to sift through and am having trouble figuring out how to automate this process a bit. My store does many large transactions, often times with the same customers. We need to find any transactions throughout the year totaling $10,000+ within a 24 hour period for the same customer. I.e. If "Customer A" comes in and makes a $6,000 purchase, then comes back within 24 hours and makes a second transaction in the amount of $4,000+, we need to know this.

I have shrunk down the data and randomized any personal info of our customer but kept the basic format of the sale report I need to look through. How can I do this without my head exploding?

[Keebellah]

Hi, I think it can be done writing a macro (VBA), would that be a problem?
Do you have any VBA experience?
Where do you want the data displayed? Another worksheet? Message box?

[rexdjones]

No I do not have VBA experience.

It can be displayed anywhere.

[Keebellah]

Will see what I can do for you, bedtime now for me here

[Gregb11]

For lack of a better solution, you could add a "Helper" column adding the date and time together (say in G2 enter A2+B2 and copy it down). Then in Cell H2 you could enter:
=IF(SUMIFS($D$2:$D$31,$G$2:$G$31,">="&G2-1,$G$2:$G$31,"<="&G2+1,$C$2:$C$31,C2)>=10000,"Large Transactions","")
copy down

[bebo021999]

@Greb11,
I read it differently.
According to his/her words, " If "Customer A" comes in and makes a $6,000 purchase, then comes back within 24 hours and makes a second transaction in the amount of $4,000+"
not coming forward.
Difference would come with data like this for specific customer:
14-May 8:PM _____4,000
15-May 4:PM_____7,000
16-May 2:PM_____4,000
Or
14-May 8:PM _____4,000
15-May 4:PM_____3,000
16-May 2:PM_____4,000

I end up with non-helper solution:

Please Login or Register  to view this content.

[Keebellah]

@bebo021999: Customer E I think does not comply with the request

[bebo021999]

I replace attachment from #6, with new one with both solutions for your choice.
@Keebellah, could you point out what the error is, in my new update file? Thanks.

[Keebellah]

Customer E transactions are not 10000 or more within 24 hours, I could be mistaken due to my date settings which are dd-mm-yyyy

[Gregb11]

@Keeellah, you're looking at MY results, which are incorrect. bebo021999's results are the last column, which don't include Customer E

[Keebellah]

@Gregb11: I stand corrected had both files opened and was looking at the wrong one 🙊