VBA/Formula for comparing three or four tables

[Vishaal]

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]

Try this:

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

 

[Vishaal]

Thanks bro

its working for me

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Thanks bro

its working for me

 

[JackDanIce]

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

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:

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

 

[Peter_SSs]

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
	A	B	C
1	Hdr1	Hdr2	Hdr3
2	x	c	q
3	f	g	f
4		j	g
5	y	y	y
6		h
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

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]

Thanks JackDanIce Sir

 

[Vishaal]

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]

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]

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?