Filter out rows that do not match the pattern

aliexcel

New Member
Joined
Feb 9, 2012
Messages
41
Hi Everyone,

I have a list with Employee sign in/out, occationaly by mistake they sign in/out more than 1 time.
So, I would like to filter out rows that do not match in (I) and outs (O) pattern.

[TABLE="width: 371"]
<tbody>[TR]
[TD][TABLE="width: 424"]
<tbody>[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Date and Time[/TD]
[TD][/TD]
[TD]Check In/Out[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]15/03/2017 9:08[/TD]
[TD][/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]15/03/2017 10:17[/TD]
[TD][/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]B[/TD]
[TD="align: right"]15/03/2017 10:53[/TD]
[TD][/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]15/03/2017 11:03[/TD]
[TD][/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]C[/TD]
[TD="align: right"]15/03/2017 11:56[/TD]
[TD][/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD]D[/TD]
[TD="align: right"]15/03/2017 12:35[/TD]
[TD][/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]15/03/2017 13:03[/TD]
[TD][/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]15/03/2017 13:26[/TD]
[TD][/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]15/03/2017 13:34[/TD]
[TD][/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD]B[/TD]
[TD="align: right"]15/03/2017 15:34[/TD]
[TD][/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD]D[/TD]
[TD="align: right"]15/03/2017 15:50[/TD]
[TD][/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD]C[/TD]
[TD="align: right"]15/03/2017 15:59[/TD]
[TD][/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]15/03/2017 16:07[/TD]
[TD][/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD]D[/TD]
[TD="align: right"]15/03/2017 17:03[/TD]
[TD][/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD]B[/TD]
[TD="align: right"]15/03/2017 18:13[/TD]
[TD][/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]15/03/2017 18:24[/TD]
[TD][/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD]D[/TD]
[TD="align: right"]15/03/2017 18:41[/TD]
[TD][/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]15/03/2017 18:49[/TD]
[TD][/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD]D[/TD]
[TD="align: right"]15/03/2017 18:54[/TD]
[TD][/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD][/TD]
[TD]C[/TD]
[TD="align: right"]15/03/2017 19:13[/TD]
[TD][/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD]C[/TD]
[TD="align: right"]15/03/2017 19:35[/TD]
[TD][/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]15/03/2017 20:00[/TD]
[TD][/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD][/TD]
[TD]B[/TD]
[TD="align: right"]15/03/2017 21:05[/TD]
[TD][/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD]D[/TD]
[TD="align: right"]15/03/2017 21:24[/TD]
[TD][/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD]C[/TD]
[TD="align: right"]15/03/2017 22:01[/TD]
[TD][/TD]
[TD]O[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In the above list for Employee "C", in row 6 we have one sign in (I) and then row 13 (O) but again in row 21, there is one more sign out before a sign in is recorded, as such row 21 should be filtered out

Thanks
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Re: Filter our rows that do not match the pattern

The solution is to sort the table on Name and Date/Time, add indices so that the table can be merged with itself such that the Name and Check In/Out from the previous row are added to the current row.

Then select all rows on which the Name <> Previous.Name or Check In/Out <> Previous.Check In/out
(or in other words: delete rows where the Name = Previous.Name and Check In/Out = Previous Check In/Out).

The code below performs just that, with the addition that the remaining rows are sorted in the original order.

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date and Time", type datetime}, {"Check In/Out", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Original Sort", 0, 1),
    #"Sorted Rows" = Table.Sort(#"Added Index",{{"Name", Order.Ascending}, {"Date and Time", Order.Ascending}}),
    #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Added Index2" = Table.AddIndexColumn(#"Added Index1", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index2",{"Index"},#"Added Index2",{"Index.1"},"Previous",JoinKind.LeftOuter),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Name", "Check In/Out"}, {"Previous.Name", "Previous.Check In/Out"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Previous", each [#"Check In/Out"] <> [#"Previous.Check In/Out"] or [Name] <> [Previous.Name]),
    #"Sorted Rows1" = Table.Sort(#"Filtered Rows",{{"Original Sort", Order.Ascending}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows1",{"Name", "Date and Time", "Check In/Out"})
in
    #"Removed Other Columns"
 
Last edited:
Upvote 0
Re: Filter our rows that do not match the pattern

It seems to me that the C is not reported when it came between 15:59 and 19:35
 
Upvote 0
Re: Filter our rows that do not match the pattern

Indeed: in that case C would be filtered out, as requested.
 
Upvote 0

Forum statistics

Threads
1,225,725
Messages
6,186,646
Members
453,367
Latest member
bookiiemonster

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