How to find date/time entries within an 1 hour interval and remove all but one of them

Hey!

Having a hard time to decide in which section this question belongs or if it has been answered already. Havent found any answer that helped me so i try this part of the forum

I have a large data set which contains four coloumns: Supplier, Supplier number, order number, and date/time of delivery. The date/time coloumn is formatted as YYYY-MM-DD HH:MM with a 24h time notation. What i want to do is to find deliveries that occurs within 1 hour and that are from the same supplier. So i basically want to group (?) the data with regards to the suppliers and then, within these subsets, check for date/time entries that occurs within 1 hour from each others by "reading" each date entry and compare it to the following one(s) (and maybe stop comparing when the 1 hour interval is passed)?

Furthermore, even if this one might be very hard, it would be good if i could make sure that the entries that are "tagged" as within a 1 hour interval, wont be used as basis for a new interval or be included in other intervals.

The result i am after would be number of 1 hour intervals for each supplier and the number of entries in each interval.

Below is an example from the date/time coloumn:

12-03-08 15:32
12-03-08 15:33 ... Interval with 2 entries
12-03-12 14:54
12-03-28 11:57
12-04-16 09:10
12-05-07 13:41
12-05-07 13:46 ... Interval with 2 entries
12-05-28 11:55
12-05-28 12:00
12-06-04 12:01 ... Interval with 2 entries
12-06-04 12:09
12-06-11 08:30
12-06-11 08:31
12-06-11 08:59 ... Interval with 3 entries
12-07-02 11:10


Any suggestions on how to do this, which section of the forum the question belongs to, or where to find additional information?

/mansjo

Originally Posted by dilipandey

HI mansjo,

welcome to the forum.



Can you upload a sample showing above ? thanks.


Regards,
DILIPandey
<click on below * if this helps>

Here is one of the files, had to remove the names of the suppliers due to company policy but the "supplier number" is still there.

Originally Posted by dilipandey

Hi Mansjo,

See the attached and filter column D for 1 and let me know if that is your required results. Thanks.
Attachment 234368

Regards,
DILIPandey
<click on below * if this helps>

Hi!

The document you attached really solved my problem so thank you very much for your help. I do have a few more documents with similar content (different geographical locations but same type of data). Any chance you could write up a short summary/walk through/look here for more info?

Thanks for your help once again, really appriciate it!

/mansjo

Originally Posted by dilipandey

You are welcome mansjo

For other documents, you can use the same formula and make sure you are referencing the correct ranges as I shown in above example. Thanks.

Regards,
DILIPandey
<click on below * if this helps>

Seems im pretty useless when it comes to excel, any chance you could apply the same formula for the other worksheets (think it is 6 of them) in the attached files below (had to split them due to file size)? If its not too much trouble that is. I just couldnt get it to work and it just kept counting upwards from 1 to whatever the last row number was. This happend when i tried to copy your formula to the other excel documents. I guess i just dont get the basic of formula handling in excel...

Would really appriciate if you could help!

/mansjo

Originally Posted by dilipandey

Hi mansjo,

That would be little difficult.
Suggest you to follow the data layout in the above example in post$4 and basis that make sure you are referencing the formula correctly in your other workbooks.. Thanks and Best of luck.


Regards,
DILIPandey
<click on below * if this helps>

Managed to solve the problem, had to make sure that the copied formula was in array form. However, saw one problem when i went through the data more in detail. It seems that the formula only checks which shipments are received within one hour with no overlaps between "different hours". For example, the sequence 13.45, 13.53 and 14.15 gives the result 1 2 1 instead of 1 2 3. Is there anyway to use maybe minutes times 60 to get to one hour and thereby get around the problem?