Delete Rows based on multiple conditions

hamaraghar

New Member
Joined
May 6, 2010
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
1690957930463.png


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
ABC
1col_idcol_id2col_point
210000a5a5801f9ead29
320000a5a5801f9ead31
430000a5a5801f9ead25
540000a5a5801f9ead37
650000a5a5801f9ead10
760000a5a5801f9ead19
870000a5a5801f9ead17
980000a5a5801f9ead20
1090000a5a5801f9ead11
11100000a5a5801f9ead19
1210000a5a5804e45e2237
13110000a5a5804e45e23
14120000a5a5804e45e27
15130000a5a5804e45e27
1620000a5a5804e45e236
17140000a5a5804e45e25
1830000a5a5804e45e251
19150000a5a5804e45e23
20160000a5a5804e45e265
2140000a5a5804e45e20
22170000a5a5804e45e26
23180000a5a5804e45e235
24190000a5a5804e45e278
25200000a5a5804e45e24
2650000a5a5804e45e233
27210000a5a5804e45e25
2860000a5a5804e45e227
2970000a5a5804e45e228
3080000a5a5804e45e239
3190000a5a5804e45e21
32220000a5a5804e45e24
33100000a5a5804e45e21
34230000a5a5804e45e231
35240000a5a5804e45e218
36250000a5a5804e45e28
3710000a5a580af402b44
3820000a5a580af402b42
3930000a5a580af402b38
40160000a5a580af402b22
4140000a5a580af402b1
42180000a5a580af402b36
43190000a5a580af402b97
4450000a5a580af402b6
4560000a5a580af402b1
4670000a5a580af402b7
4780000a5a580af402b4
4890000a5a580af402b9
49100000a5a580af402b0
Sheet8
 
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
Rich (BB code):
If r = j - 1 Then
If r = j - 1 And (r > 0 Or a(fr, 3) < 50) Then
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,223,888
Messages
6,175,208
Members
452,618
Latest member
Tam84

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