0

I am trying to compare the values of two different columns.

Image

so there are two cases:-

  1. In column 'A' a two digit value is present which leads to a seven digit value in column 'B'.
  2. In column 'A' a three digit value is present which leads to a eight digit value in column 'B'.

I want to validate whether 'B2' and 'B3' start with 14 and 21 respectively if no Msgbox 'Error in row number'. and whether 'B3' and 'B4' start with and 109 and 289 respectively if no Msgbox 'Error in row number'.

Improve this question
2
  • 1
    Sounds great, what have you tried? What's not working exactly? As it stands this isn't a specific programming question, it's a list of specs. Commented Sep 23, 2017 at 15:24
  • Well i can handle this by using Length, left and equals to formula but i want to do the same with VBA Commented Sep 23, 2017 at 15:51

3 Answers 3

Reset to default
2

I would use a UDF. Enter this is a standard module:

Public Function IsItGood(r1 As Range, r2 As Range) As String
    Dim v1 As String, v2 As String, L As Long
    v1 = r1.Text
    v2 = r2.Text
    L = Len(v1)
    If v1 = Left(v2, L) Then
        IsItGood = "Noerror"
    Else
        IsItGood = "Error in this row"
    End If
End Function

Then in C2 enter:

=isitgood(A2,B2)

and copy down:

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Put this formula in C2 and copy Down:

=IF(AND(LEN(SUBSTITUTE(B2,A2,""))=5,FIND(A2,B2&A2)=1),"No Error","Error")

enter image description here

To get a list of row numbers that do not work you can use this formula:

=AGGREGATE(15,6,ROW($A$2:$A$7)/((--LEFT($B$2:$B$7,LEN($A$2:$A$7))<>$A$2:$A$7)+(LEN($B$2:$B$7)-LEN($A$2:$A$7)<>5)>0),ROW(1:1))

Put it in the first cell then copy/paste down till you run out of rows with errors.

enter image description here

Improve this answer

2 Comments

This formula :- =IF(AND(LEN(SUBSTITUTE(B2,A2,""))=5,FIND(A2,B2&A2)=1),"No Error","Error") is working great.
@sb.SB please mark the method you used as correct, by clicking the check mark by the answer you choose.
1

You may give this a try...

Sub CompareColumns()
Dim lr As Long, n As Long
Dim rng As Range, cell As Range
Dim strA As String, strB As String, str As String
Dim NotMatched As Boolean

lr = Cells(Rows.Count, 1).End(xlUp).Row

'Assuming your data starts from Row2
Set rng = Range("B2:B" & lr)
str = "The following cells don't match." & vbNewLine & vbNewLine
For Each cell In rng
    If cell <> "" Then
        n = Len(cell.Offset(0, -1))
        If n > 0 Then
            strA = cell.Offset(0, -1).Text
            strB = Left(cell, n)
            If strA <> strB Then
                NotMatched = True
                str = str & cell.Offset(0, -1).Address(0, 0) & " : " & cell.Offset(0, -1).Value & vbTab & cell.Address(0, 0) & " : " & cell.Value & vbNewLine
            End If
        Else
            str = str & cell.Offset(0, -1).Address(0, 0) & " : " & cell.Offset(0, -1).Value & vbTab & cell.Address(0, 0) & " : " & cell.Value & vbNewLine
        End If
    End If
    n = 0
    strA = ""
    strB = ""
Next cell
If NotMatched Then
    MsgBox str, vbInformation
Else
    MsgBox "Both columns match.", vbInformation
End If
End Sub
Improve this answer

2 Comments

Thanks !! Absolutely working . but if column position is not known i mean first we need to search the both columns based on headers and then perform the same operation. Assuming both columns may not be consecutive always but will be in the same sheet.
@sb.SB You're welcome! Glad you found it helpful. That's a different question altogether. Please open a New Question and provide the existing code in the question description and also provide more details like what column headers you would like to search and in which row? Also, please take a minute to accept the answer to mark your existing question as Solved.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.