Formula Help

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

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

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

Hi Mike,

I'm not familiar with macros, is there any way to do this with formula?

Rgds 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

Hi Mike

I am not familiar with macros, is there any way to do this with formulas?

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

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