Firefox Extension: excel: delete (or find) cell if it meets certain criteria

excel: delete (or find) cell if it meets certain criteria
Hello,

I have a table that scores the number of frames in a movie that fits a certain combination, which is identified by a series of numbers (101010, 10101 for example (71 combinations in total)). From this -in the third column- I can calculate the fraction of frames that falls into a certain category.
What i want to do is to find the fractions that DO NOT fall into a set of combinations. Or delete the ones the fall into a particular set.
Basically I want to ignore a bunch of numbers from the first column.
Or find a way to highlight where this set of combinations are.
Is there a formula for this? My guess is that VLOOKUP would work but i have basic knowledge of it.
Hope I managed to explain myself.
Thanks in advance.

101011 34 0.165853659
101010 33 0.16097561
10101 29 0.141463415
110101 27 0.131707317
111110 19 0.092682927
11101 14 0.068292683
11111 12 0.058536585
111011 9 0.043902439
111111 7 0.034146341
Reply With Quote
#2
Old 10-11-2011
EINSTEIN_007's Avatar
EINSTEIN_007 EINSTEIN_007 is offline
Member

Join Date: Dec 2007
Posts: 2,029
Re: excel: delete (or find) cell if it meets certain criteria
Check the below code that would probably meet up with something to what you want:

Code:

Sub myDeleteRows()

Dim MyCol As String
Dim MyVal As Variant
Dim i As Integer

MyCol = InputBox("column to look through", "Column Search", "A")
MyVal = InputBox("Value to look for", "search value", 0)
For i = 1 To Range(MyCol & "65536").End(xlUp).Row Step 1
If Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), MyVal) > 0 Then
Range("A" & i).EntireRow.Delete
End If
Next i

End Sub

__________________
Education, Career and Job Discussions
Reply With Quote
#3
Old 10-11-2011
cmendes cmendes is offline
Member

Join Date: Nov 2011
Posts: 2
Re: excel: delete (or find) cell if it meets certain criteria
Thank for your response. I'm really unfamiliar with macros. Do you think there's a way to do it with a formula. At least is there a formula that searches for multiple items at the same time?
Reply With Quote
#4
Old 12-11-2011
AnitG AnitG is offline
Member

Join Date: Aug 2011
Posts: 492
Re: excel: delete (or find) cell if it meets certain criteria
It is a kind of filter search and for that you can use Vlookup which can help. I had used this on my excel sheet to find a data from a peculiar row. The follow is like this way : =IF(ISERROR(VLOOKUP(A2, 'Sheet A'!$A$1:$A$14000, 1, FALSE)), "not on Sheet A", "found"). Second this is you can use Autofilter option to hide the data which you do not want to see.
Reply With Quote