Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Comparison
Hi
I have 2 text strings of equal length (7) in the cells for columns B & C. The strings in C differ slightly to those in B. I am a novice at macros but would like some help in comparing column C cells to B cells and returning the difference in Column D. i.e. Compare C2 to B2 and return the difference in D2 and continue down the rows until the end of the data. The first row contains the column headings. Any help wolud be much appreciated. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Comparison
Sub Macro1()
' ' Macro1 Macro ' Macro recorded 7/2/2008 by Joel ' ' RowCount = 1 Do While Range("B" & RowCount) < "" If Range("B" & RowCount) < Range("C" & RowCount) Then 'copy cell B to column D Range("D" & RowCount).Value = _ Range("B" & RowCount).Value 'highlight characters that are different For i = 1 To Len(Range("D" & RowCount)) If Mid(Range("B" & RowCount), i, 1) < _ Mid(Range("C" & RowCount), i, 1) Then Range("D" & RowCount). _ Characters(Start:=i, Length:=1).Font _ .ColorIndex = 41 End If Next i End If RowCount = RowCount + 1 Loop End Sub " wrote: Hi I have 2 text strings of equal length (7) in the cells for columns B & C. The strings in C differ slightly to those in B. I am a novice at macros but would like some help in comparing column C cells to B cells and returning the difference in Column D. i.e. Compare C2 to B2 and return the difference in D2 and continue down the rows until the end of the data. The first row contains the column headings. Any help wolud be much appreciated. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Comparison
On Jul 2, 12:20*pm, "
wrote: Hi I have 2 text strings of equal length (7) in the cells for columns B & C. *The strings in C differ slightly to those in B. I am a novice at macros but would like some help in comparing column C cells to B cells and returning the difference in Column D. i.e. Compare C2 to B2 and return the difference in D2 and continue down the rows until the end of the data. The first row contains the column headings. Any help wolud be much appreciated. Thanks Sounds like you could simply use built-in Excel functions here, rather than VBA code, but you'd need to specify the type of comparison you wanted to perform i.e. flag if cells don't match, compare each character separately and flag if any different etc. Post a few row examples and the result you want to see then we'll be able to help... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Comparison
On Jul 2, 12:56*pm, Joel wrote:
Sub Macro1() ' ' Macro1 Macro ' Macro recorded 7/2/2008 by Joel ' ' RowCount = 1 Do While Range("B" & RowCount) < "" * *If Range("B" & RowCount) < Range("C" & RowCount) Then * * * 'copy cell B to column D * * * Range("D" & RowCount).Value = _ * * * * *Range("B" & RowCount).Value * * * 'highlight characters that are different * * * For i = 1 To Len(Range("D" & RowCount)) * * * * *If Mid(Range("B" & RowCount), i, 1) < _ * * * * * * Mid(Range("C" & RowCount), i, 1) Then * * * * * * Range("D" & RowCount). _ * * * * * * * *Characters(Start:=i, Length:=1).Font _ * * * * * * * *.ColorIndex = 41 * * * * *End If * * * Next i * *End If * *RowCount = RowCount + 1 Loop End Sub " wrote: Hi I have 2 text strings of equal length (7) in the cells for columns B & C. *The strings in C differ slightly to those in B. I am a novice at macros but would like some help in comparing column C cells to B cells and returning the difference in Column D. i.e. Compare C2 to B2 and return the difference in D2 and continue down the rows until the end of the data. The first row contains the column headings. Any help wolud be much appreciated. Thanks- Hide quoted text - - Show quoted text - Thanks Joel. That was a step in the right direction. However it only highlighted the difference in Column D. What i want done is return the different characters. e.g.: B C D 2 ZSZ80KT Z528OKT S0 3 ZGY92GG ZGY926G G 4 YYS3WDE YYS3WOE D In this case comparing C2 to B2 we find that B2 has 'S' and '0' different in C2 so these two characters are returned in D2 and C4 to B4, 'D' is different in C4 so o'D' is returned in D4 and so on. Rob Allchurch, does this help? Thanks again. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Comparison
This is a little strange because you don't get a lot of useful information.
You don't know which postitions the characters are differrent but here is the solution anyway Sub Macro1() ' ' Macro1 Macro ' Macro recorded 7/2/2008 by Joel ' ' RowCount = 1 Do While Range("B" & RowCount) < "" If Range("B" & RowCount) < Range("C" & RowCount) Then CompareStr = €œ€ For i = 1 To Len(Range("D" & RowCount)) If Mid(Range("B" & RowCount), i, 1) < _ Mid(Range("C" & RowCount), i, 1) Then CompareStr = CompareStr & Mid(Range("B" & RowCount), i, 1) End If Next i Range("D" & RowCount) = CompareStr End If RowCount = RowCount + 1 Loop End Sub " wrote: On Jul 2, 12:56 pm, Joel wrote: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 7/2/2008 by Joel ' ' RowCount = 1 Do While Range("B" & RowCount) < "" If Range("B" & RowCount) < Range("C" & RowCount) Then 'copy cell B to column D Range("D" & RowCount).Value = _ Range("B" & RowCount).Value 'highlight characters that are different For i = 1 To Len(Range("D" & RowCount)) If Mid(Range("B" & RowCount), i, 1) < _ Mid(Range("C" & RowCount), i, 1) Then Range("D" & RowCount). _ Characters(Start:=i, Length:=1).Font _ .ColorIndex = 41 End If Next i End If RowCount = RowCount + 1 Loop End Sub " wrote: Hi I have 2 text strings of equal length (7) in the cells for columns B & C. The strings in C differ slightly to those in B. I am a novice at macros but would like some help in comparing column C cells to B cells and returning the difference in Column D. i.e. Compare C2 to B2 and return the difference in D2 and continue down the rows until the end of the data. The first row contains the column headings. Any help wolud be much appreciated. Thanks- Hide quoted text - - Show quoted text - Thanks Joel. That was a step in the right direction. However it only highlighted the difference in Column D. What i want done is return the different characters. e.g.: B C D 2 ZSZ80KT Z528OKT S0 3 ZGY92GG ZGY926G G 4 YYS3WDE YYS3WOE D In this case comparing C2 to B2 we find that B2 has 'S' and '0' different in C2 so these two characters are returned in D2 and C4 to B4, 'D' is different in C4 so o'D' is returned in D4 and so on. Rob Allchurch, does this help? Thanks again. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Comparison
On Jul 2, 3:15*pm, "
wrote: On Jul 2, 12:56*pm, Joel wrote: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 7/2/2008 by Joel ' ' RowCount = 1 Do While Range("B" & RowCount) < "" * *If Range("B" & RowCount) < Range("C" & RowCount) Then * * * 'copy cell B to column D * * * Range("D" & RowCount).Value = _ * * * * *Range("B" & RowCount).Value * * * 'highlight characters that are different * * * For i = 1 To Len(Range("D" & RowCount)) * * * * *If Mid(Range("B" & RowCount), i, 1) < _ * * * * * * Mid(Range("C" & RowCount), i, 1) Then * * * * * * Range("D" & RowCount). _ * * * * * * * *Characters(Start:=i, Length:=1).Font _ * * * * * * * *.ColorIndex = 41 * * * * *End If * * * Next i * *End If * *RowCount = RowCount + 1 Loop End Sub " wrote: Hi I have 2 text strings of equal length (7) in the cells for columns B & C. *The strings in C differ slightly to those in B. I am a novice at macros but would like some help in comparing column C cells to B cells and returning the difference in Column D. i.e. Compare C2 to B2 and return the difference in D2 and continue down the rows until the end of the data. The first row contains the column headings. Any help wolud be much appreciated. Thanks- Hide quoted text - - Show quoted text - Thanks Joel. That was a step in the right direction. *However it only highlighted the difference in Column D. *What i want done is return the different characters. e.g.: * * * * B * * * C * * * D 2 * * * ZSZ80KT Z528OKT S0 3 * * * ZGY92GG ZGY926G G 4 * * * YYS3WDE YYS3WOE D In this case comparing C2 to B2 we find that B2 has 'S' and '0' different in C2 so these two characters are returned in D2 and C4 to B4, 'D' is different in C4 so o'D' is returned in D4 and so on. *Rob Allchurch, does this help? Thanks again. OK - this is what I would do. Not necessarily very elegant, but it works and is easy to follow. Put this formula in D2 and copy to all necessary rows ... =IF(MID(B2,1,1)=MID(C2,1,1),"",MID(C2,1,1))&IF(MID (B2,2,1)=MID(C2,2,1),"",MID(C2,2,1))&IF(MID(B2,3,1 )=MID(C2,3,1),"",MID(C2,3,1))&IF(MID(B2,4,1)=MID(C 2,4,1),"",MID(C2,4,1))&IF(MID(B2,5,1)=MID(C2,5,1), "",MID(C2,5,1))&IF(MID(B2,6,1)=MID(C2,6,1),"",MID( C2,6,1))&IF(MID(B2,7,1)=MID(C2,7,1),"",MID(C2,7,1) ) The formula assumes all cells in columns B & C only contain 7 characters. If they contain more, you'll need to copy the last part of the formula (after the ampersand) and change the '7' to '8' (for the eighth character) and so on. The result will show only different characters. If you'd rather have a placeholder showing 'correct' characters then change the "" for "-" (or "*" or whatever). I'm sure the code below would work fine, and there's probably a more elegant formula to use, but this should work. Best regards, Rob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
String Comparison | Excel Programming | |||
Comparison of alphanumeric string | Excel Discussion (Misc queries) | |||
comparison string VBA excell | Excel Discussion (Misc queries) | |||
String Comparison | Excel Programming | |||
VB String Comparison In Excel | Excel Programming |