Prev: MAX IF
Next: How do I get to change my xl document in to readable text or numbe
From: Mike H on 18 May 2010 06:18
Paula,

A formula can't delete lines of data but we could try this. I put a list of
countries to keep in AA1 to AA9 and then put this formula in N1 and dragged
down

=IF(ISNA(VLOOKUP(M1,$AA$1:$AA$9,1,FALSE)),"Delete","")

If the value in Col M isn't in your list in AA the the formula returns
DELETE. You can now sort the data on this column and manually delete the rows.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Paula" wrote:

> Hi Mike,
>
> I am not very familiar with macros, well not at all, is there any way just
> to do this with formula?
>
> Regards
>
> Paula
>
>
> "Mike H" wrote:
>
> > Paula,
> >
> > Are you ready for a Macro. Alt+F11 to open VB editor. Right click
> > 'ThisWorkbook' and insert modula and paste the code below in.
> >
> > Edit these 2 lines
> > Set sht = Sheets("Sheet1") ' change to suit
> > S = "France,Germany,Spain,UK" ' add as required
> >
> > To the correct sheet and the correct countries. Add the necessaey countries
> > sperated by a comma and NO spaces. Run the code.
> >
> >
> > Sub Marine()
> > Dim R As Range
> > Dim V As Variant
> > Dim S As String
> > Dim CopyRange As Range
> > Set sht = Sheets("Sheet1") ' change to suit
> > LastRow = sht.Cells(Cells.Rows.Count, "M").End(xlUp).Row
> > S = "France,Germany,Spain,UK" ' add as required
> > V = Split(S, ",")
> > For Each R In sht.Range("M1:M" & LastRow)
> > If IsError(Application.Match(CStr(R.Value), V, 0)) Then
> > If CopyRange Is Nothing Then
> > Set CopyRange = R.EntireRow
> > Else
> > Set CopyRange = Union(CopyRange, R.EntireRow)
> > End If
> > End If
> > Next R
> > If Not CopyRange Is Nothing Then
> > CopyRange.Delete
> > End If
> > End Sub
> > --
> > Mike
> >
> > When competing hypotheses are otherwise equal, adopt the hypothesis that
> > introduces the fewest assumptions while still sufficiently answering the
> > question.
> >
> >
> > "Paula" wrote:
> >
> > > Hi,
> > >
> > > Hope you can help me, I have a report which I need to delete some lines of
> > > data. The criteria is as follows:
> > >
> > > Each record can have numerous lines of data and has a unique reference i.e.
> > > MEBACI, however if this record contains any of 9 conditions in column M the
> > > record should be left in full with no deleted lines of data.
> > >
> > > However if the record does not contain any of the 9 conditions the entire
> > > record should be deleted from the report.
> > >
> > > The 9 conditions are Countires, e.g. UK, France, Germany etc.
> > >
> > > Is there any formula which can be used to detect this?
> > >
> > > Regards
> > > Paula
> > >
> > >
First  |  Prev  | 
Pages: 1 2
Prev: MAX IF
Next: How do I get to change my xl document in to readable text or numbe