Excel / powerquery formula to compare dates/time when other value is matching.

B

BadTweety

New Member
Joined
Jan 2, 2017
Messages
8
Hi all,

I have a list of shipping numbers with piece numbers and date/time of first scan. Now i want to know when there is more then 1 hour between different piece numbers of the same shipping number (partial arrival).

As i have more then 100k rows i would like to find a formula in PowerQuery to reduce the stress on excel but all ideas are welcome!

the table should have following outcome.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Shipping number[/TD]
[TD]piece number[/TD]
[TD]first scan[/TD]
[TD]Partial arrival?[/TD]
[/TR]
[TR]
[TD]123456789[/TD]
[TD]2[/TD]
[TD]22/06/2019 13:00[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]456789123[/TD]
[TD]1[/TD]
[TD]21/06/2019 10:00[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]123456789[/TD]
[TD]4[/TD]
[TD]22/06/2019 13:50[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]123456789[/TD]
[TD]3[/TD]
[TD]22/06/2019 14:10[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]456789123[/TD]
[TD]2[/TD]
[TD]21/06/2019 10:50[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]123456789[/TD]
[TD]1[/TD]
[TD]22/06/2019 14:30[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]987654321[/TD]
[TD]2[/TD]
[TD]22/06/2019 12:10[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]987654321[/TD]
[TD]3[/TD]
[TD]23/06/2019 12:30[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]987654321[/TD]
[TD]1[/TD]
[TD]22/06/2019 12:50[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

I was thinking to combine a min, max function combined with countifs but i cant figure it out :(
in addition i am limited to excel 2013 and the datedif function doesnt seem to be included in this version.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Here's a piece of M-code that groups rows by Shipping number and extracts the min & max time stamps + calculates a column to check if the duration between the scans is more than an hour:
Code: 
let    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Shipping number", type text}, {"piece number", Int64.Type}, {"first scan", type datetime}, {"Partial arrival?", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Shipping number"}, {{"First Scan", each List.Min([first scan]), type datetime}, {"Last Scan", each List.Max([first scan]), type datetime}, {"All Rows", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Hour+ Apart", each [Last Scan]-[First Scan]>#duration(0,1,0,0),type logical)
in
    #"Added Custom"
You might have to adjust the code a little bit but I'm sure you can take it from here.
 
Upvote 0
is that what you want?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Shipping number[/td][td=bgcolor:#5B9BD5]piece number[/td][td=bgcolor:#5B9BD5]first scan[/td][td][/td][td=bgcolor:#70AD47]Shipping number[/td][td=bgcolor:#70AD47]piece number[/td][td=bgcolor:#70AD47]first scan[/td][td=bgcolor:#70AD47]Y/N[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
123456789​
[/td][td=bgcolor:#DDEBF7]
2​
[/td][td=bgcolor:#DDEBF7]
22/06/2019 13:00​
[/td][td][/td][td=bgcolor:#E2EFDA]
123456789​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
22/06/2019 13:00​
[/td][td=bgcolor:#E2EFDA]Yes[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
456789123​
[/td][td]
1​
[/td][td]
21/06/2019 10:00​
[/td][td][/td][td]
456789123​
[/td][td]
1​
[/td][td]
21/06/2019 10:00​
[/td][td]No[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
123456789​
[/td][td=bgcolor:#DDEBF7]
4​
[/td][td=bgcolor:#DDEBF7]
22/06/2019 13:50​
[/td][td][/td][td=bgcolor:#E2EFDA]
123456789​
[/td][td=bgcolor:#E2EFDA]
4​
[/td][td=bgcolor:#E2EFDA]
22/06/2019 13:50​
[/td][td=bgcolor:#E2EFDA]Yes[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
123456789​
[/td][td]
3​
[/td][td]
22/06/2019 14:10​
[/td][td][/td][td]
123456789​
[/td][td]
3​
[/td][td]
22/06/2019 14:10​
[/td][td]Yes[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
456789123​
[/td][td=bgcolor:#DDEBF7]
2​
[/td][td=bgcolor:#DDEBF7]
21/06/2019 10:50​
[/td][td][/td][td=bgcolor:#E2EFDA]
456789123​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
21/06/2019 10:50​
[/td][td=bgcolor:#E2EFDA]No[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
123456789​
[/td][td]
1​
[/td][td]
22/06/2019 14:30​
[/td][td][/td][td]
123456789​
[/td][td]
1​
[/td][td]
22/06/2019 14:30​
[/td][td]Yes[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
987654321​
[/td][td=bgcolor:#DDEBF7]
2​
[/td][td=bgcolor:#DDEBF7]
22/06/2019 12:10​
[/td][td][/td][td=bgcolor:#E2EFDA]
987654321​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
22/06/2019 12:10​
[/td][td=bgcolor:#E2EFDA]Yes[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
987654321​
[/td][td]
3​
[/td][td]
23/06/2019 12:30​
[/td][td][/td][td]
987654321​
[/td][td]
3​
[/td][td]
23/06/2019 12:30​
[/td][td]Yes[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
987654321​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
22/06/2019 12:50​
[/td][td][/td][td=bgcolor:#E2EFDA]
987654321​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
22/06/2019 12:50​
[/td][td=bgcolor:#E2EFDA]Yes[/td][/tr]
[/table]

Let me know if you don't manage with the post #2
 
Upvote 0
thank you so much !

Grouped rows where the key to success.
its not showing which piece is first/last but in my case i dont need that information.

with some minor changes i just made my daily work a lot faster.

:bow::bow:
 
Upvote 0
BadTweety said:
thank you so much !

Grouped rows where the key to success.
its not showing which piece is first/last but in my case i dont need that information.

with some minor changes i just made my daily work a lot faster.

:bow::bow:
Click to expand...

Worked with post nr 2 btw ! ;)
 
Upvote 0
sandy666 said:
is that what you want?

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Shipping number[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]piece number[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]first scan[/COLOR][/TD]
[TD][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Shipping number[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]piece number[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]first scan[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Y/N[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]
123456789​
[/TD]
[TD="bgcolor: #DDEBF7"]
2​
[/TD]
[TD="bgcolor: #DDEBF7"]
22/06/2019 13:00​
[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]
123456789​
[/TD]
[TD="bgcolor: #E2EFDA"]
2​
[/TD]
[TD="bgcolor: #E2EFDA"]
22/06/2019 13:00​
[/TD]
[TD="bgcolor: #E2EFDA"]Yes[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
456789123​
[/TD]
[TD]
1​
[/TD]
[TD]
21/06/2019 10:00​
[/TD]
[TD][/TD]
[TD]
456789123​
[/TD]
[TD]
1​
[/TD]
[TD]
21/06/2019 10:00​
[/TD]
[TD]No[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]
123456789​
[/TD]
[TD="bgcolor: #DDEBF7"]
4​
[/TD]
[TD="bgcolor: #DDEBF7"]
22/06/2019 13:50​
[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]
123456789​
[/TD]
[TD="bgcolor: #E2EFDA"]
4​
[/TD]
[TD="bgcolor: #E2EFDA"]
22/06/2019 13:50​
[/TD]
[TD="bgcolor: #E2EFDA"]Yes[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
123456789​
[/TD]
[TD]
3​
[/TD]
[TD]
22/06/2019 14:10​
[/TD]
[TD][/TD]
[TD]
123456789​
[/TD]
[TD]
3​
[/TD]
[TD]
22/06/2019 14:10​
[/TD]
[TD]Yes[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]
456789123​
[/TD]
[TD="bgcolor: #DDEBF7"]
2​
[/TD]
[TD="bgcolor: #DDEBF7"]
21/06/2019 10:50​
[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]
456789123​
[/TD]
[TD="bgcolor: #E2EFDA"]
2​
[/TD]
[TD="bgcolor: #E2EFDA"]
21/06/2019 10:50​
[/TD]
[TD="bgcolor: #E2EFDA"]No[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
123456789​
[/TD]
[TD]
1​
[/TD]
[TD]
22/06/2019 14:30​
[/TD]
[TD][/TD]
[TD]
123456789​
[/TD]
[TD]
1​
[/TD]
[TD]
22/06/2019 14:30​
[/TD]
[TD]Yes[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]
987654321​
[/TD]
[TD="bgcolor: #DDEBF7"]
2​
[/TD]
[TD="bgcolor: #DDEBF7"]
22/06/2019 12:10​
[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]
987654321​
[/TD]
[TD="bgcolor: #E2EFDA"]
2​
[/TD]
[TD="bgcolor: #E2EFDA"]
22/06/2019 12:10​
[/TD]
[TD="bgcolor: #E2EFDA"]Yes[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
987654321​
[/TD]
[TD]
3​
[/TD]
[TD]
23/06/2019 12:30​
[/TD]
[TD][/TD]
[TD]
987654321​
[/TD]
[TD]
3​
[/TD]
[TD]
23/06/2019 12:30​
[/TD]
[TD]Yes[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]
987654321​
[/TD]
[TD="bgcolor: #DDEBF7"]
1​
[/TD]
[TD="bgcolor: #DDEBF7"]
22/06/2019 12:50​
[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]
987654321​
[/TD]
[TD="bgcolor: #E2EFDA"]
1​
[/TD]
[TD="bgcolor: #E2EFDA"]
22/06/2019 12:50​
[/TD]
[TD="bgcolor: #E2EFDA"]Yes[/TD]
[/TR]
</tbody>[/TABLE]


Let me know if you don't manage with the post #2
Click to expand...

I am curious tough, what was your solution ?
 
Upvote 0
post#2 is similar to my code, I just finished it

Code: 
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1)
in
    Index

// Table1_2
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Shipping number"}, {{"Max", each List.Max([first scan]), type datetime}, {"Min", each List.Min([first scan]), type datetime}, {"Count", each _, type table}}),
    Subtract = Table.AddColumn(Group, "Subtraction", each [Max] - [Min], type duration),
    Condition = Table.AddColumn(Subtract, "Y/N", each if [Subtraction] > #duration(0, 1, 0, 0) then "Yes" else "No"),
    ROC = Table.SelectColumns(Condition,{"Shipping number", "Y/N"})
in
    ROC

// Result
let
    Source = Table.NestedJoin(Table1_2,{"Shipping number"},Table1,{"Shipping number"},"Table3 (2)",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Source, "Table3 (2)", {"piece number", "first scan", "Index"}, {"piece number", "first scan", "Index"}),
    Sort = Table.Sort(Expand,{{"Index", Order.Ascending}}),
    Reorder = Table.ReorderColumns(Sort,{"Shipping number", "piece number", "first scan", "Y/N"}),
    ROC = Table.SelectColumns(Reorder,{"Shipping number", "piece number", "first scan", "Y/N"}),
    Type = Table.TransformColumnTypes(ROC,{{"first scan", type datetime}})
in
    Type[/SIZE]
 
Last edited:
Upvote 0
sandy666 said:
post#2 is similar to my code, I just finished it

Code: 
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1)
in
    Index

// Table1_2
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Shipping number"}, {{"Max", each List.Max([first scan]), type datetime}, {"Min", each List.Min([first scan]), type datetime}, {"Count", each _, type table}}),
    Subtract = Table.AddColumn(Group, "Subtraction", each [Max] - [Min], type duration),
    Condition = Table.AddColumn(Subtract, "Y/N", each if [Subtraction] > #duration(0, 1, 0, 0) then "Yes" else "No"),
    ROC = Table.SelectColumns(Condition,{"Shipping number", "Y/N"})
in
    ROC

// Result
let
    Source = Table.NestedJoin(Table1_2,{"Shipping number"},Table1,{"Shipping number"},"Table3 (2)",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Source, "Table3 (2)", {"piece number", "first scan", "Index"}, {"piece number", "first scan", "Index"}),
    Sort = Table.Sort(Expand,{{"Index", Order.Ascending}}),
    Reorder = Table.ReorderColumns(Sort,{"Shipping number", "piece number", "first scan", "Y/N"}),
    ROC = Table.SelectColumns(Reorder,{"Shipping number", "piece number", "first scan", "Y/N"}),
    Type = Table.TransformColumnTypes(ROC,{{"first scan", type datetime}})
in
    Type[/SIZE]
Click to expand...

Sweet ! thanks, i had the same idea about the last part.
 
Upvote 0
here is all together:

Code: 
[SIZE=1]// Query1
let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source1, "Index", 1, 1),
    Source2 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source2, {"Shipping number"}, {{"Max", each List.Max([first scan]), type datetime}, {"Min", each List.Min([first scan]), type datetime}, {"Count", each _, type table}}),
    Subtract = Table.AddColumn(Group, "Subtraction", each [Max] - [Min], type duration),
    Condition = Table.AddColumn(Subtract, "Y/N", each if [Subtraction] > #duration(0, 1, 0, 0) then "Yes" else "No"),
    ROC = Table.SelectColumns(Condition,{"Shipping number", "Y/N"}),
    Result = Table.NestedJoin(ROC,{"Shipping number"},Index,{"Shipping number"},"Result",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Result, "Result", {"piece number", "first scan", "Index"}, {"piece number", "first scan", "Index"}),
    Sort = Table.Sort(Expand,{{"Index", Order.Ascending}}),
    Reorder = Table.ReorderColumns(Sort,{"Shipping number", "piece number", "first scan", "Index", "Y/N"}),
    Type = Table.TransformColumnTypes(Reorder,{{"first scan", type datetime}}),
    ROC1 = Table.SelectColumns(Type,{"Shipping number", "piece number", "first scan", "Y/N"})

in
    ROC1[/SIZE]
 
Upvote 0
Cross-posted: https://www.excelforum.com/excel-fo...-dates-time-when-other-value-is-matching.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
607
awoohaw
awoohaw
M
  • Question Question
Pivot table not showing as Numbers
Replies
1
Views
288
shift-del
shift-del
sharky12345
  • Solved
Issue trying to identify earliest date & time
2
Replies
15
Views
551
sharky12345
sharky12345
S
  • Solved
Formula to split date and time and work out energy usage trend
Replies
6
Views
470
Sundance_Kid
S
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
392
Asbestos_Jen
Asbestos_Jen

Forum statistics

Threads
1,224,820
Messages
6,181,154
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top