Sorting a Lot of data into multiple sheets (up to 200k lines)

[JMWh1t3]

Hiya All,

I need to set up a sheet to sort out a lot of rows of data into 4 separate sheets based on some of the values. The data is arranged as seen in the table below.

I have sorted the sheets for each area but need help filtering the data I paste in.

I have a sheet called Paste Data, this where I paste data from a csv file. The format of the data is always the same, no variations.

What I need help with is sorting the data that is pasted in. I need to get rid of:
Every row that doesn't contain "Marshalling" in either LOCATION or LOCATION2.
Every row that has a DOWNTIME over 25minutes (00:25:00)
Every row that has the area "Area5"

I then need to either label each entry (I tried with =IF but it didn't work) with the shift (AM 06:00-14:00/PM 14:00-22:00/LATE 22:00-06:00) based on the time of the FIRST MOVE, or separate into 3 separate tables (one for each shift).

Hopefully someone can help, cause it's driving me insane.

What I tried so far:
Gave every row a unique number, used =filter to filter out "Marshalling" in LOCATION, another filter for LOCATION2 then HSTACK to combine both. I then Filtered that list based on AREA, But I could not work out labelling or sorting for the times.

It also when really slow when I pasted new data in, probably because my data set has between 20k and 200k lines depending on how I receive the report.

USER ID	FIRST MOVE	LOCATION	QTY	UL	SECOND MOVE	LOCATION2	QTY3	UL4	DOWNTIME	AREA
User1	27/05/2024 14:00:00​	Shift Start			27/05/2024 14:22:56​	103-3-R011-C	3​	21725780​	00:22:56​	Area1
User1	27/05/2024 14:28:33​	103-3-R034-C	4​	21950554​	27/05/2024 14:29:42​	103-3-L036-B	1​	21950554​	00:01:09​	Area1
User1	27/05/2024 14:33:05​	103-3-R053-C	1​	21141834​	27/05/2024 14:34:10​	103-3-R053-C	7​	21141834​	00:01:05​	Area1
User1	27/05/2024 14:42:41​	103-3-R134-A	1​	21495910​	27/05/2024 14:44:00​	Marshalling	1​	21495910​	00:01:19​	Area1
User1	27/05/2024 14:44:38​	103-4-R117-A	8​	21495910​	27/05/2024 14:46:27​	103-4-R115-B	1​	21495910​	00:01:49​	Area1
User1	27/05/2024 14:49:07​	103-4-R071-B	3​	21495910​	27/05/2024 14:50:43​	103-4-R054-B	4​	21495910​	00:01:36​	Area1
User1	27/05/2024 14:52:48​	103-4-R021-C	9​	21495910​	27/05/2024 14:53:53​	103-4-R015-C	2​	21495910​	00:01:05​	Area1
User1	27/05/2024 15:00:51​	Marshalling	12​	21495910​	27/05/2024 15:02:11​	103-4-L096-C	2​	21495910​	00:38:23​	Area1
User2	27/05/2024 15:03:49​	103-4-L116-C	6​	21495910​	27/05/2024 15:08:41​	Marshalling	1​	21358245​	00:04:52​	Area2
User2	27/05/2024 15:08:54​	103-2-R065-C	9​	21358245​	27/05/2024 15:10:51​	103-2-L020-C	1​	21495505​	00:01:57​	Area2
User2	27/05/2024 15:11:29​	103-2-L016-C	12​	21495505​	27/05/2024 15:12:32​	103-2-R015-C	3​	21495505​	00:01:03​	Area2
User2	27/05/2024 15:18:34​	Marshalling	1​	21322965​	27/05/2024 15:19:35​	103-3-R007-A	1​	21416989​	00:01:01​	Area2
User2	27/05/2024 15:24:06​	103-3-R037-B	6​	21416989​	27/05/2024 15:25:45​	103-3-L058-C	1​	21906472​	00:01:39​	Area2
User3	27/05/2024 15:26:06​	103-3-L058-C	9​	21906472​	27/05/2024 15:27:24​	Marshalling	3​	21906472​	00:42:02​	Area3
User3	27/05/2024 15:27:49​	103-3-L065-B	10​	21906472​	27/05/2024 15:29:20​	103-3-R074-B	1​	21906472​	00:01:31​	Area3
User3	27/05/2024 15:35:33​	103-3-R113-A	1​	21906472​	27/05/2024 15:39:53​	103-3-L118-C	6​	21906472​	00:04:20​	Area3
User3	27/05/2024 15:41:15​	103-3-R127-B	1​	21906472​	27/05/2024 15:43:40​	103-4-R021-C	1​	21322965​	00:02:25​	Area3
User4	27/05/2024 15:44:18​	Marshalling	3​	21495505​	27/05/2024 15:45:25​	103-4-L039-C	3​	21416989​	00:01:07​	Area4
User4	27/05/2024 15:45:25​	103-4-L039-C	3​	21416989​	27/05/2024 15:46:35​	Marshalling	2​	21906472​	00:01:10​	Area4
User4	27/05/2024 15:46:56​	103-4-L114-C	1​	21495505​	27/05/2024 15:56:59​	103-1-R062-C	1​	21267787​	00:10:03​	Area4

 

[Artik]

Assuming that the data is in the range A1:Kn, you can use the advanced filter.
1. cell M1 is to be empty, in M2 the formula:

=AND(OR($C2="Marshalling",$G2="Marshalling"),$J2<=TIMEVALUE("00:25:00"),$K2<>"Area5")

2. select a cell in the range A1:Kn, select “Advanced Filter” from the ribbon.
3. in the “Criteria range” field, insert the address $M$1:$M$2.
4. you can filter the data in place or indicate another area (upper left corner of the range) into which the filtered data will be inserted.

Since the formula in M2 is recalculated for each row of data, expect a delay on a large range of input data.

Artik

 

[JMWh1t3]

Assuming that the data is in the range A1:Kn, you can use the advanced filter.
1. cell M1 is to be empty, in M2 the formula:

=AND(OR($C2="Marshalling",$G2="Marshalling"),$J2<=TIMEVALUE("00:25:00"),$K2<>"Area5")

2. select a cell in the range A1:Kn, select “Advanced Filter” from the ribbon.
3. in the “Criteria range” field, insert the address $M$1:$M$2.
4. you can filter the data in place or indicate another area (upper left corner of the range) into which the filtered data will be inserted.

Since the formula in M2 is recalculated for each row of data, expect a delay on a large range of input data.

Artik

Thanks,

That worked, but is there a way to do it without using the Advanced Filter, just so I paste the next lot of data in and it does all the filtering etc automatically?

 

[Artik]

Just record a macro of the filtering process and only slightly modify it:

Sub AdvcdFilter()
    Application.CutCopyMode = False
    
    If Not Application.IsFormula(Range("M2")) Then
        Range("M2").Formula = _
        "=AND(OR($C2=""Marshalling"",$G2=""Marshalling""),$J2<=TIMEVALUE(""00:25:00""),$K2<>""Area5"")"
    End If

    Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
                                             Range("M1:M2"), Unique:=False
End Sub

Artik

 

[JMWh1t3]

Just record a macro of the filtering process and only slightly modify it:

Sub AdvcdFilter()
    Application.CutCopyMode = False
   
    If Not Application.IsFormula(Range("M2")) Then
        Range("M2").Formula = _
        "=AND(OR($C2=""Marshalling"",$G2=""Marshalling""),$J2<=TIMEVALUE(""00:25:00""),$K2<>""Area5"")"
    End If

    Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
                                             Range("M1:M2"), Unique:=False
End Sub

Artik

Hiya,

Can't be a macro, its web based excel.