Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



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

Windows Software


Reply
 
Thread Tools Search this Thread
  #1  
Old 10-11-2011
Member
 
Join Date: Nov 2011
Posts: 2
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
Member
 
Join Date: Dec 2007
Posts: 2,260
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
Reply With Quote
  #3  
Old 10-11-2011
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
Member
 
Join Date: Aug 2011
Posts: 563
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
Reply

  TechArena Community > Software > Windows Software
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "excel: delete (or find) cell if it meets certain criteria"
Thread Thread Starter Forum Replies Last Post
How to delete first 4 letters of Cell in Microsoft Excel Ulrich MS Office Support 2 18-01-2012 05:51 PM
Looking for a Excel Macro to delete rows which doesn’t contain a value in particular cell Antariksh Windows Software 6 23-07-2011 01:05 AM
Based on Cell Criteria Delete Row in excel AlpAnA$ Windows Software 5 09-07-2011 08:50 PM
How can we determine which application meets Java or DotNET eligibility criteria NathanDS Software Development 5 16-10-2010 02:45 PM
Windows is unable to find a system volume that meets its criteria adrin Vista Setup and Install 2 05-03-2007 09:27 AM


All times are GMT +5.5. The time now is 11:32 AM.