Delete Rows based on multiple conditions

[hamaraghar]

Hi I have a table with 3 columns - col_id, col_id2&Col_point.
I have sorted the data for col_id2 and then col_id.

col_id2 has same values for multiple lines and within the same values, col_id has unique values with value 1 common across different col_id2 values.

Problem statement.
I want to delete rows based on the value in Col_point column using listed conditions.
1. If for a given value in col_id2, the corresponding col_point for all the same col_id2 is less than 50, then it should delete everyrows having the same col_id2.
2. If for a given value in col_id2, the corresponding col_point for any same col_id2 is more than 50, then it shoud delete all the rows having the same col_id2 except for the rows where the point is more than 50 AND the row with col_id value as 1.

Example. I need to retain the rows marked with green in col_Id and Col_point.

How can I accomplish this? I can do it one col_id2 at a time but I have data worth 80K+rows and it will take me forever. Is there a way to get it automated using VBA or online script.

I have added the MiniSheet below

Test.xlsx
	A	B	C
1	col_id	col_id2	col_point
2	1	0000a5a5801f9ead	29
3	2	0000a5a5801f9ead	31
4	3	0000a5a5801f9ead	25
5	4	0000a5a5801f9ead	37
6	5	0000a5a5801f9ead	10
7	6	0000a5a5801f9ead	19
8	7	0000a5a5801f9ead	17
9	8	0000a5a5801f9ead	20
10	9	0000a5a5801f9ead	11
11	10	0000a5a5801f9ead	19
12	1	0000a5a5804e45e2	237
13	11	0000a5a5804e45e2	3
14	12	0000a5a5804e45e2	7
15	13	0000a5a5804e45e2	7
16	2	0000a5a5804e45e2	36
17	14	0000a5a5804e45e2	5
18	3	0000a5a5804e45e2	51
19	15	0000a5a5804e45e2	3
20	16	0000a5a5804e45e2	65
21	4	0000a5a5804e45e2	0
22	17	0000a5a5804e45e2	6
23	18	0000a5a5804e45e2	35
24	19	0000a5a5804e45e2	78
25	20	0000a5a5804e45e2	4
26	5	0000a5a5804e45e2	33
27	21	0000a5a5804e45e2	5
28	6	0000a5a5804e45e2	27
29	7	0000a5a5804e45e2	28
30	8	0000a5a5804e45e2	39
31	9	0000a5a5804e45e2	1
32	22	0000a5a5804e45e2	4
33	10	0000a5a5804e45e2	1
34	23	0000a5a5804e45e2	31
35	24	0000a5a5804e45e2	18
36	25	0000a5a5804e45e2	8
37	1	0000a5a580af402b	44
38	2	0000a5a580af402b	42
39	3	0000a5a580af402b	38
40	16	0000a5a580af402b	22
41	4	0000a5a580af402b	1
42	18	0000a5a580af402b	36
43	19	0000a5a580af402b	97
44	5	0000a5a580af402b	6
45	6	0000a5a580af402b	1
46	7	0000a5a580af402b	7
47	8	0000a5a580af402b	4
48	9	0000a5a580af402b	9
49	10	0000a5a580af402b	0
Sheet8

 

[Peter_SSs]

I'm getting an error @ .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlYes
The error is Run time error '1004'. Sort method of range class failed.

Is your data in a formal Excel table? If so, what is the name of the table? (Check in Formulas ribbon tab -> Name Manager if required)

BTW, for future sample data, please consider using XL2BB so that we can easily copy for testing.

When I add some unique values in column 2 which are not repeatative, I am not getting the blanks for values more than 50 in column 4.

You will no doubt still get the sort error but see if this change correctly marks the rows to delete

If r = j - 1 Then
If r = j - 1 And (r > 0 Or a(fr, 3) < 50) Then