Comparing data in columns and delete if not equal

Hi,
Im new to Macros and I was wondering if there is a simple macro to compare
two columns and see if they have the same value. If the columns dont match, I
need to delete the whole row.

Any guidance would be really appreciated.

Thanks
Rob

Hi,
This compares the column you are in to the column that is just to the of
that column. Hope it helps.
Sub TwoColumns()
Do Until ActiveCell.Value = ""
Column1 = ActiveCell.Value
Column2 = ActiveCell.Offset(0, 1).Value
If Column1 = Column2 Then
Stop
Else
ActiveCell.EntireRow.Delete
ActiveCell.Offset(-1, 0).Range("A1").Select
End If
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
End Sub

Thanks,

"Robert" wrote:

> Hi,
> Im new to Macros and I was wondering if there is a simple macro to compare
> two columns and see if they have the same value. If the columns dont match, I
> need to delete the whole row.
>
> Any guidance would be really appreciated.
>
> Thanks
> Rob

Hello David,
Thanks for the reply. I tried the macro but it has some problems. Everytime
theres a match or not, it gives a Run-time error '1004': Application-defined
or object-defined error.
When theres a match, it gives an error at the "STOP" command. And when
theres no match it points at ActiveCell.Offset(-1, 0).Range("A1").Select.
Any other pointers would be really helpful. Thanks for your help.


"David" wrote:

> Hi,
> This compares the column you are in to the column that is just to the of
> that column. Hope it helps.
> Sub TwoColumns()
> Do Until ActiveCell.Value = ""
> Column1 = ActiveCell.Value
> Column2 = ActiveCell.Offset(0, 1).Value
> If Column1 = Column2 Then
> Stop
> Else
> ActiveCell.EntireRow.Delete
> ActiveCell.Offset(-1, 0).Range("A1").Select
> End If
> ActiveCell.Offset(1, 0).Range("A1").Select
> Loop
> End Sub
>
> Thanks,
>
> "Robert" wrote:
>
> > Hi,
> > Im new to Macros and I was wondering if there is a simple macro to compare
> > two columns and see if they have the same value. If the columns dont match, I
> > need to delete the whole row.
> >
> > Any guidance would be really appreciated.
> >
> > Thanks
> > Rob

Hi,
The stop can be removed sorry about that. Can you tell me how your data is
set up, which columns you are trying to compare? ie
ColA ColB ColC ColD ... etc
5 9 a z .....Etc

Then tell what you are comparing ColA and ColB or ColD and ColZ. It is set
up to start on the first value to compare and then compare it to the nest
column to the right, ie ColA and ColB or ColD and ColE.

Thanks,

"Robert" wrote:

> Hi,
> Im new to Macros and I was wondering if there is a simple macro to compare
> two columns and see if they have the same value. If the columns dont match, I
> need to delete the whole row.
>
> Any guidance would be really appreciated.
>
> Thanks
> Rob

Hello,
Do I need to replace the STOP with anything else?

My data is set this way.

ColA ColB ColC ColD ColE ColF
5/1/05 1 8 25 0.552 3
5/2/05 2 3 47 0.67 3
5/3/05 3 7 8 0.22 3

So for example, I need to compare column F with Column B and everytime there
is a common number in both columns I need to keep the whole row of data.
Otherwise I have to delete the row if Columns B and F dont match.
Hope it is more clearer now.
Thanks for the help.
Rob

"David" wrote:

> Hi,
> The stop can be removed sorry about that. Can you tell me how your data is
> set up, which columns you are trying to compare? ie
> ColA ColB ColC ColD ... etc
> 5 9 a z .....Etc
>
> Then tell what you are comparing ColA and ColB or ColD and ColZ. It is set
> up to start on the first value to compare and then compare it to the nest
> column to the right, ie ColA and ColB or ColD and ColE.
>
> Thanks,
>
> "Robert" wrote:
>
> > Hi,
> > Im new to Macros and I was wondering if there is a simple macro to compare
> > two columns and see if they have the same value. If the columns dont match, I
> > need to delete the whole row.
> >
> > Any guidance would be really appreciated.
> >
> > Thanks
> > Rob

Going down ColB and ColF if they don't match they are gone..... So in your example rows 1 and 2 would need to be deleted because ColB/ColF are 1/3 and 2/3 respectively. This is a simple code to get this done, Hopefully no errors occur on this one.



Range("B1").Select
Do
If ActiveCell.Value = ActiveCell.Offset(0, 2).Value Then GoTo Line1
ActiveCell.EntireRow.Delete
GoTo Line2
Line1:
ActiveCell.Offset(1, 0).Select
Line2:
Loop Until ActiveCell.Value = ""


This one is assuming you have no header (Row1) in any of the columns. If so put an ActiveCell.Offset(1, 0).Select before the "Do" to get off of the heading row. But this one will zoom through your whole and delete the entire row if the two columns don't match up. You might have to adjust the value in red If ActiveCell.Value = ActiveCell.Offset(0, 2).Value Then GoTo Line1 depending on how far away the column is in comparison to the other. Hope this helps....


And one last word of caution... Always, ALWAYS save your worksheet before running any newly modified macro to ensure that it works. This way if it messes up and deletes a whole bunch of stuff that took you 30+ minutes to make, you won't be banging your head against the wall. Trust me on this one, concussions are no fun. You can't hit control+z after a macro.

Hi,
This assumes the data you will compare begins in B1, which means there are
no Headers, if there are headers change the second line of the code from
"Range("B1").Select" to "Range("B2").Select.

Sub TwoColumns()
Range("B1").Select
Do Until ActiveCell.Value = ""
Column1 = ActiveCell.Value
Column2 = ActiveCell.Offset(0, 4).Value
If Column1 = Column2 Then

Else
ActiveCell.EntireRow.Delete
ActiveCell.Offset(-1, 0).Range("A1").Select
End If
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
End Sub

Thanks,

"Robert" wrote:

> Hello,
> Do I need to replace the STOP with anything else?
>
> My data is set this way.
>
> ColA ColB ColC ColD ColE ColF
> 5/1/05 1 8 25 0.552 3
> 5/2/05 2 3 47 0.67 3
> 5/3/05 3 7 8 0.22 3
>
> So for example, I need to compare column F with Column B and everytime there
> is a common number in both columns I need to keep the whole row of data.
> Otherwise I have to delete the row if Columns B and F dont match.
> Hope it is more clearer now.
> Thanks for the help.
> Rob
>
> "David" wrote:
>
> > Hi,
> > The stop can be removed sorry about that. Can you tell me how your data is
> > set up, which columns you are trying to compare? ie
> > ColA ColB ColC ColD ... etc
> > 5 9 a z .....Etc
> >
> > Then tell what you are comparing ColA and ColB or ColD and ColZ. It is set
> > up to start on the first value to compare and then compare it to the nest
> > column to the right, ie ColA and ColB or ColD and ColE.
> >
> > Thanks,
> >
> > "Robert" wrote:
> >
> > > Hi,
> > > Im new to Macros and I was wondering if there is a simple macro to compare
> > > two columns and see if they have the same value. If the columns dont match, I
> > > need to delete the whole row.
> > >
> > > Any guidance would be really appreciated.
> > >
> > > Thanks
> > > Rob

Hi Rob,
This code would work for you, try it out. Thanks to Ron. Just change the
Column from A to whatever you want and if you need to compare it to a column
other than the next one, change the OFFSET value. Hope it helps.

Sub Delete_rows_based_on_ColA_ColB2()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("A").SpecialCells(xlConstants, xlNumbers)
For i = rng.Count To 1 Step -1
If rng(i).Value <> rng(i).Offset(0, 1).Value Then
rng(i).EntireRow.Delete
End If
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


_______
Best regards,

Sean


"Robert" wrote:

> Hello,
> Do I need to replace the STOP with anything else?
>
> My data is set this way.
>
> ColA ColB ColC ColD ColE ColF
> 5/1/05 1 8 25 0.552 3
> 5/2/05 2 3 47 0.67 3
> 5/3/05 3 7 8 0.22 3
>
> So for example, I need to compare column F with Column B and everytime there
> is a common number in both columns I need to keep the whole row of data.
> Otherwise I have to delete the row if Columns B and F dont match.
> Hope it is more clearer now.
> Thanks for the help.
> Rob
>
> "David" wrote:
>
> > Hi,
> > The stop can be removed sorry about that. Can you tell me how your data is
> > set up, which columns you are trying to compare? ie
> > ColA ColB ColC ColD ... etc
> > 5 9 a z .....Etc
> >
> > Then tell what you are comparing ColA and ColB or ColD and ColZ. It is set
> > up to start on the first value to compare and then compare it to the nest
> > column to the right, ie ColA and ColB or ColD and ColE.
> >
> > Thanks,
> >
> > "Robert" wrote:
> >
> > > Hi,
> > > Im new to Macros and I was wondering if there is a simple macro to compare
> > > two columns and see if they have the same value. If the columns dont match, I
> > > need to delete the whole row.
> > >
> > > Any guidance would be really appreciated.
> > >
> > > Thanks
> > > Rob