IF cell contains value - delete entire row

Hello!

This appears to be a good place to learn from others and share my own knowledge

I've outlined the problem at hand with other important information.

Software

O/S: Windows Vista
Programs: Microsoft Excel 2003 (If required would install Open Office)

The Task

What I am trying to do is possibly complicated unless we can use these formulas within the code/formula

[x]$

Step 1: Checking the last value contains a (1,2,3) perhaps using %[123]$ -
For rows not containing these values the entire row is deleted

Step 2: This is basically checking the figure in %[123]$ is less than "one from last" perhaps %[123456789]%$
Where this isn't true the entire row is deleted.

Step 3: If there is just ONE value it must be "1" anything else is to be deleted

Test Data

This is found on my spreadsheet in Column G
These values will soon go all the way down to the last row G1:G63356

23324
953110
651320
2
611433
332554
111111
245
014193
78
442
996222

Using the test data as an example

Step 1:

611433
111111
014193
442
996222

Deleted values:

23324
953110
651320
332554
245
78

Step 2

111111
014193
442

Deleted

611433
996222

Step 3

Deleted:

2

If anyone has any solutions on how to solve this problem would be delighted to hear from you. A macro would be fine or just a simple formula otherwise be just as good

Kind regards

Chris.

I first copied your original data to sheet1 and sheet3 also. sheet3 will keep the data safe

now the following macro "step1" will give the step1 results
(2 also should be added)

Please Login or Register  to view this content.

now the logic of your step 2 is not clear. use the examples after running step1 and explain once again. similarly reformulate your step 3 also.

Hi Venkat

Thank you for replying to my problem.

Sorry about this I will do my best to re-explain the following steps.

Step 2

Following on from the first step these values will remain

2
611433
111111
014193
442
996222

Now this is when I need it to compare the last two values. From this it will determine if the value one from last is greater than the last value.

There are two exceptions.

1) when these numbers are both "1" the row isn't to be deleted.

The other scenario is when we are dealing with single numbers - these are kept to be processed in step 3. I have explained this below using the above example.

2
- This is left alone

611433
- This row will be removed (3 is equal to 3) NOT greater than

111111
- This is kept as both values are "1" the end two figures

014193
- This is kept as "9" is greater than "3"

442
- This is kept as "4" is greater than 2.

996222
- This row is removed as "2" isn't greater than "2".

Hope that is a better explaination.

Step 3

Now using the above example we have only one number left over

2

In this step all we checking is whether the single value is equal to "1" else delete row.

Any questions?

Thanks Chris.

Ps: @ Admin RoyUK - I have updated this information with my OS and Office Version.

Originally Posted by venkat1926

I first copied your original data to sheet1 and sheet3 also. sheet3 will keep the data safe

now the following macro "step1" will give the step1 results
(2 also should be added)

Please Login or Register  to view this content.

now the logic of your step 2 is not clear. use the examples after running step1 and explain once again. similarly reformulate your step 3 also.

How would I change this code to do the following:

IF
The last digit is equal to 1 - delete row
The second from last digit is equal to 1 - delete row
And finally if three digits from the end digit is equal to 1/2 - delete row

Test Data

13452
11397
80311
11
1
111
211
301
233

Stage 1

Expected Result

13452
11397
233

Stage 2

Expected Result

13452
11397
233

Stage 3

Expected Result

13452
11397

Thanks Chris.