Hi all
I have got 2 columns and 17 digits for each column and i want to check digit between 2 columns and then report different digit.m
Hope you help and Thank for help!
example
A | B | result digit differance
PE2ET77141SB02023 | PE2FT77141SB02023 | 5
MNBBS2D305W485838 | MNBBB2D305W485838 | 5
MNBBS2D304W375808 | MNBBS2D304W37588 | 16
Hi
1) Do you want to return the position of the different characters?
2) If so, then shouldn't the first result be 4 not 5?
3) In your example file, there are 3 items in column B that have 16 characters, not the 17 you stated.
4) What do you want to do if there is >1 differences? Comma separate the positions?
rylo
Hi iamrony,
Though your thread explanation / example and the data on your spreadsheet are not overly clear, I've created the following macro which if doesn't do the trick should go a long towards it:
Sub CompareAdjoiningCells() 'This macro compares the value in Column A to Column B and returns _ the string position(s) where there is(are) a difference(s) between _ the two. Dim rngCell As Range For Each rngCell In Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row) Dim intPosCnt As Integer Dim strMyCompare As String If rngCell.Offset(0, -1).Value <> _ rngCell.Offset(0, -2).Value Then intPosCnt = 1 Do Until intPosCnt = 17 Select Case val(intPosCnt) Case 1 If Left(rngCell.Offset(0, -1), intPosCnt) <> _ Left(rngCell.Offset(0, -2), intPosCnt) Then strMyCompare = 1 End If Case 2 If Mid(rngCell.Offset(0, -1), intPosCnt, 1) <> _ Mid(rngCell.Offset(0, -2), intPosCnt, 1) Then If strMyCompare = "" Then strMyCompare = intPosCnt Else strMyCompare = strMyCompare & ", " & intPosCnt End If End If Case 3 If Mid(rngCell.Offset(0, -1), intPosCnt, 1) <> _ Mid(rngCell.Offset(0, -2), intPosCnt, 1) Then If strMyCompare = "" Then strMyCompare = intPosCnt Else strMyCompare = strMyCompare & ", " & intPosCnt End If End If Case 4 If Mid(rngCell.Offset(0, -1), intPosCnt, 1) <> _ Mid(rngCell.Offset(0, -2), intPosCnt, 1) Then If strMyCompare = "" Then strMyCompare = intPosCnt Else strMyCompare = strMyCompare & ", " & intPosCnt End If End If Case 5 If Mid(rngCell.Offset(0, -1), intPosCnt, 1) <> _ Mid(rngCell.Offset(0, -2), intPosCnt, 1) Then If strMyCompare = "" Then strMyCompare = intPosCnt Else strMyCompare = strMyCompare & ", " & intPosCnt End If End If Case 6 If Mid(rngCell.Offset(0, -1), intPosCnt, 1) <> _ Mid(rngCell.Offset(0, -2), intPosCnt, 1) Then If strMyCompare = "" Then strMyCompare = intPosCnt Else strMyCompare = strMyCompare & ", " & intPosCnt End If End If Case 7 If Mid(rngCell.Offset(0, -1), intPosCnt, 1) <> _ Mid(rngCell.Offset(0, -2), intPosCnt, 1) Then If strMyCompare = "" Then strMyCompare = intPosCnt Else strMyCompare = strMyCompare & ", " & intPosCnt End If End If Case 8 If Mid(rngCell.Offset(0, -1), intPosCnt, 1) <> _ Mid(rngCell.Offset(0, -2), intPosCnt, 1) Then If strMyCompare = "" Then strMyCompare = intPosCnt Else strMyCompare = strMyCompare & ", " & intPosCnt End If End If Case 9 If Mid(rngCell.Offset(0, -1), intPosCnt, 1) <> _ Mid(rngCell.Offset(0, -2), intPosCnt, 1) Then If strMyCompare = "" Then strMyCompare = intPosCnt Else strMyCompare = strMyCompare & ", " & intPosCnt End If End If Case 10 If Mid(rngCell.Offset(0, -1), intPosCnt, 1) <> _ Mid(rngCell.Offset(0, -2), intPosCnt, 1) Then If strMyCompare = "" Then strMyCompare = intPosCnt Else strMyCompare = strMyCompare & ", " & intPosCnt End If End If Case 11 If Mid(rngCell.Offset(0, -1), intPosCnt, 1) <> _ Mid(rngCell.Offset(0, -2), intPosCnt, 1) Then If strMyCompare = "" Then strMyCompare = intPosCnt Else strMyCompare = strMyCompare & ", " & intPosCnt End If End If Case 12 If Mid(rngCell.Offset(0, -1), intPosCnt, 1) <> _ Mid(rngCell.Offset(0, -2), intPosCnt, 1) Then If strMyCompare = "" Then strMyCompare = intPosCnt Else strMyCompare = strMyCompare & ", " & intPosCnt End If End If Case 13 If Mid(rngCell.Offset(0, -1), intPosCnt, 1) <> _ Mid(rngCell.Offset(0, -2), intPosCnt, 1) Then If strMyCompare = "" Then strMyCompare = intPosCnt Else strMyCompare = strMyCompare & ", " & intPosCnt End If End If Case 14 If Mid(rngCell.Offset(0, -1), intPosCnt, 1) <> _ Mid(rngCell.Offset(0, -2), intPosCnt, 1) Then If strMyCompare = "" Then strMyCompare = intPosCnt Else strMyCompare = strMyCompare & ", " & intPosCnt End If End If Case 15 If Mid(rngCell.Offset(0, -1), intPosCnt, 1) <> _ Mid(rngCell.Offset(0, -2), intPosCnt, 1) Then If strMyCompare = "" Then strMyCompare = intPosCnt Else strMyCompare = strMyCompare & ", " & intPosCnt End If End If Case 16 If Mid(rngCell.Offset(0, -1), intPosCnt, 1) <> _ Mid(rngCell.Offset(0, -2), intPosCnt, 1) Then If strMyCompare = "" Then strMyCompare = intPosCnt Else strMyCompare = strMyCompare & ", " & intPosCnt End If End If Case 17 If Right(rngCell.Offset(0, -1), 1) <> _ Right(rngCell.Offset(0, -2), 1) Then If strMyCompare = "" Then strMyCompare = intPosCnt Else strMyCompare = strMyCompare & ", " & intPosCnt End If End If End Select intPosCnt = intPosCnt + 1 Loop End If rngCell.Value = strMyCompare strMyCompare = "" Next End Sub
Trebor76,rylo : you are excellent! That it work! thanks for all the help!
UDF
Use in cell like
=If(iamrony(A1,B1)=0,"",iamrony(A1,B1))
Function iamrony(txt1 As String, txt2 As String) As Long Dim i As Integer, flg As Boolean If txt1 = txt2 Then Exit Function i = 1 Do While i <= Application.Max(Len(txt1), Len(txt2)) If (i > Application.Min(Len(txt1), Len(txt2))) + _ (Mid$(txt1,i) <> Mid$(txt2,1)) Then flg = True Exit Do End If i = i + 1 Loop If flg Then iamrony = i End Function
Thank you for your help
Last edited by iamrony; 06-17-2008 at 11:26 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks