VBA/Formula for comparing three or four tables

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
261
Hi,

Thanks in advance,

i have three columns and want to compare them, conditions are

1- If Coloumn 1 & Coloumn 2 are equal - Delete entire row
2- If Coloumn 2 & Coloumn 3 are equal - Delete entire row
3- If Coloumn 1 & Coloumn 3 are equal - Delete entire row


please provide any VBA or code or formula
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,286
Try this:
Code:
Sub Delete_If()
'Modified 8/26/2019 3:47:12 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = Lastrow To 1 Step -1
    If Cells(i, 1).Value = Cells(i, 2).Value Then Rows(i).Delete
Next
Application.ScreenUpdating = True
End Sub
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,334
It's unclear in the suggested code #2 where blue conditions are tested:
1- If Coloumn 1 & Coloumn 2 are equal - Delete entire row
2- If Coloumn 2 & Coloumn 3 are equal - Delete entire row
3- If Coloumn 1 & Coloumn 3 are equal - Delete entire row
The loop seems to only compare columns 1 and 2
Rich (BB code):
For i = lastRow To 1 Step -1
    If Cells(i, 1).Value = Cells(i, 2).Value Then Rows(i).Delete
Next
vs
i have three columns and want to compare them, conditions are

If you need to compare column 1 vs column 3 or column 2 vs column 3 as well as column 1 vs column 2 then try:
Rich (BB code):
Sub M1()

    Dim v   As Variant
    Dim x   As Long
    
    x = Cells(Rows.Count, 1).End(xlUp).Row
    v = Cells(1, 1).Resize(x, 3).Value

    For x = LBound(v, 1) To UBound(v, 1)
        If v(x, 1) = v(x, 2) Or v(x, 1) = v(x, 3) Or v(x, 2) = v(x, 3) Then v(x, 1) = ""
    Next x
    
    With Cells(1, 1).Resize(UBound(v, 1))
        .Resize(, UBound(v, 2)).Value = v
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
    
    Erase v
    
End Sub
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,269
Office Version
365
Platform
Windows
It's unclear in the suggested code #2 where blue conditions are tested:

The loop seems to only compare columns 1 and 2
I agree, but perhaps the OP adapted to add the other conditions.

I disagree with your suggested alternative though as it would delete row 4 from the following sample data even though that row does not meet the delete conditions. Of course you could perhaps set v(x, 1) to an error value & then delete the error rows (assuming no errors already in column A).

Excel Workbook
ABC
1Hdr1Hdr2Hdr3
2xcq
3fgf
4jg
5yyy
6h
Compare columns



If the data is not too large, here is a non-looping approach. I have assumed ..
- a header row
- no data beyond column C

Code:
Sub Del_Matches()
  Application.ScreenUpdating = False
  With Range("A1:C" & Columns("A:C").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row)
    Range("D2").Formula = "=OR(A2=B2,B2=C2,A2=C2)"
    .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("D1:D2"), Unique:=False
    .Offset(1).EntireRow.Delete
    Range("D2").ClearContents
  End With
  If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
  Application.ScreenUpdating = True
End Sub

@Vishaal
If the data is very large, post back for another faster approach & tell us about how many rows of data you might actually have.
 

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
261
Thanks Peter_SSs Sir Ji

I have only Four Coloumn in some and only eight coloumn in some

and raw for all will be row 2 to row 10000
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,269
Office Version
365
Platform
Windows
I have ... eight coloumn in some

and raw for all will be row 2 to row 10000
So, have you tried any of the suggestions with 8 columns and 10,000 rows?

- If not, I suggest that you do and report back on the time taken

-If so, was the code fast enough (about how long?) and exactly what code did you use since none of the suggestions were based on 8 columns.

For the future I also suggest that you try to be more accurate with your initial information. In this thread you stated specifically "i have three columns"
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,334
Hey @Peter_SSs
I agree, but perhaps the OP adapted to add the other conditions.

How many times on this forum have we seen replies complaining OP hasn't given enough precise information AND after a suggestion has been posted (and thanked by the OP) others have contributed with their alternatives or different assumptions/interpretations of the ask?

By extention your reply is inferring the first suggestion should be sufficient alone and so not provide alternatives.

A reverse loop vs an array loop are both different yet used for same outcome, so even if the OP had adapted, it would be a different way to consider a solution and as you did with your code, suggest an alternative solution that is hopefully faster than the initial posted suggestion?

OP could have run code where only the first condition satisfies but if data size is large, not OP didn't scroll down far enough, there may be other rows that should be deleted but were not, because it's only comparing columns 1 & 2 and the User experienced a unknown known?

I disagree with your suggested alternative
Great spot, I hadn't tested for that scenario - I (wrongly!) assumped column A to not be blank :)

Finally, their follow up with the unclear,
I have ... eight column in some
suggests they hadn't adapted the suggestion or considered in relation to the initial code suggestions, so better to not assume they adapated the code, especially given the initial post does state for 3 columns AND the first suggestion only compared columns A&B exclusively? ;)
 
Last edited:

Forum statistics

Threads
1,082,305
Messages
5,364,400
Members
400,800
Latest member
germanpbv

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top