Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing character strings in separate cells
Hi All,
I have asked here before and on other fora for help on the following problem ( i am a total novice at VB and Macros): I have a large excel spreadsheet with alpha-numeric data. I want to be able to compare two cells in the same row and return the disimilar characters in another cell(s). e.g. I have in cell B2, "BB26" and in cell C2, "BBZ5". I need to compare the cell C2 to cell B2 and return the disimilar characters in cell D2 which in this case would be the characters "Z5". If possible i would like to specify in cell E2 the positions of the differing characters i.e. cell E2, "34" representing the 3rd and 4th positions of the disimilar characters. If there is a better and more elegant solution to this problem that would provide a similar solution, i would appreciate your help very much. Thank you in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing character strings in separate cells
Are all the compared strings the same lengths ?
If not, what would you expect from "BB26" and "BBZ526" ? How about if you just returned a binary string, representing each character ? e.g. "BB26" and "BBZ5" returns "1100" But this depend somewhat on your answer to the first question. NickHK "Odin" wrote in message oups.com... Hi All, I have asked here before and on other fora for help on the following problem ( i am a total novice at VB and Macros): I have a large excel spreadsheet with alpha-numeric data. I want to be able to compare two cells in the same row and return the disimilar characters in another cell(s). e.g. I have in cell B2, "BB26" and in cell C2, "BBZ5". I need to compare the cell C2 to cell B2 and return the disimilar characters in cell D2 which in this case would be the characters "Z5". If possible i would like to specify in cell E2 the positions of the differing characters i.e. cell E2, "34" representing the 3rd and 4th positions of the disimilar characters. If there is a better and more elegant solution to this problem that would provide a similar solution, i would appreciate your help very much. Thank you in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing character strings in separate cells
Hi NickHK, the compared strings could be of different lengths. As i
already have columns with the string lengths, in your example I would expect D2 to still return "Z5" in positions "34". The binary string solution to cell E2 is interesting but in your example would that return "110011" or just "1100"? Odin NickHK wrote: Are all the compared strings the same lengths ? If not, what would you expect from "BB26" and "BBZ526" ? How about if you just returned a binary string, representing each character ? e.g. "BB26" and "BBZ5" returns "1100" But this depend somewhat on your answer to the first question. NickHK "Odin" wrote in message oups.com... Hi All, I have asked here before and on other fora for help on the following problem ( i am a total novice at VB and Macros): I have a large excel spreadsheet with alpha-numeric data. I want to be able to compare two cells in the same row and return the disimilar characters in another cell(s). e.g. I have in cell B2, "BB26" and in cell C2, "BBZ5". I need to compare the cell C2 to cell B2 and return the disimilar characters in cell D2 which in this case would be the characters "Z5". If possible i would like to specify in cell E2 the positions of the differing characters i.e. cell E2, "34" representing the 3rd and 4th positions of the disimilar characters. If there is a better and more elegant solution to this problem that would provide a similar solution, i would appreciate your help very much. Thank you in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing character strings in separate cells
Based on the data you have provided, 26 could just as well be the disimilar
characters since at this point we don't know whether there is a constant pattern to the data we are comparing, whether we are comparing one cell to a range of cells or whether we are comparing cell for cell to the adjacent column of cells. Perhaps you could state your problem a little clearer. "Odin" wrote: Hi NickHK, the compared strings could be of different lengths. As i already have columns with the string lengths, in your example I would expect D2 to still return "Z5" in positions "34". The binary string solution to cell E2 is interesting but in your example would that return "110011" or just "1100"? Odin NickHK wrote: Are all the compared strings the same lengths ? If not, what would you expect from "BB26" and "BBZ526" ? How about if you just returned a binary string, representing each character ? e.g. "BB26" and "BBZ5" returns "1100" But this depend somewhat on your answer to the first question. NickHK "Odin" wrote in message oups.com... Hi All, I have asked here before and on other fora for help on the following problem ( i am a total novice at VB and Macros): I have a large excel spreadsheet with alpha-numeric data. I want to be able to compare two cells in the same row and return the disimilar characters in another cell(s). e.g. I have in cell B2, "BB26" and in cell C2, "BBZ5". I need to compare the cell C2 to cell B2 and return the disimilar characters in cell D2 which in this case would be the characters "Z5". If possible i would like to specify in cell E2 the positions of the differing characters i.e. cell E2, "34" representing the 3rd and 4th positions of the disimilar characters. If there is a better and more elegant solution to this problem that would provide a similar solution, i would appreciate your help very much. Thank you in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing character strings in separate cells
The return value would depend on whether you are basing it on the 1st or
second string. What value would you expect for "BBZ526" and "BB26" then ? You could maybe use some "padding" character in the return string, so it is as long as the longer string. NickHK "Odin" wrote in message oups.com... Hi NickHK, the compared strings could be of different lengths. As i already have columns with the string lengths, in your example I would expect D2 to still return "Z5" in positions "34". The binary string solution to cell E2 is interesting but in your example would that return "110011" or just "1100"? Odin NickHK wrote: Are all the compared strings the same lengths ? If not, what would you expect from "BB26" and "BBZ526" ? How about if you just returned a binary string, representing each character ? e.g. "BB26" and "BBZ5" returns "1100" But this depend somewhat on your answer to the first question. NickHK "Odin" wrote in message oups.com... Hi All, I have asked here before and on other fora for help on the following problem ( i am a total novice at VB and Macros): I have a large excel spreadsheet with alpha-numeric data. I want to be able to compare two cells in the same row and return the disimilar characters in another cell(s). e.g. I have in cell B2, "BB26" and in cell C2, "BBZ5". I need to compare the cell C2 to cell B2 and return the disimilar characters in cell D2 which in this case would be the characters "Z5". If possible i would like to specify in cell E2 the positions of the differing characters i.e. cell E2, "34" representing the 3rd and 4th positions of the disimilar characters. If there is a better and more elegant solution to this problem that would provide a similar solution, i would appreciate your help very much. Thank you in advance. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing character strings in separate cells
Hi NickHK/JLGWhiz,
I hope that this example will enable you to understand the problem better. B C D E 1 RX5TGE RX5TGEU U 7 2 I3816AE 13816AE 1 1 3 A269LYT B269LYT M 1 4 LGP5XGB LGR5ZGB RZ 35 5 BB26 BBZ526 Z5 34 6 BBZ526 BB26 This does not ocur Thanks again for the consideration. NickHK wrote: The return value would depend on whether you are basing it on the 1st or second string. What value would you expect for "BBZ526" and "BB26" then ? You could maybe use some "padding" character in the return string, so it is as long as the longer string. NickHK "Odin" wrote in message oups.com... Hi NickHK, the compared strings could be of different lengths. As i already have columns with the string lengths, in your example I would expect D2 to still return "Z5" in positions "34". The binary string solution to cell E2 is interesting but in your example would that return "110011" or just "1100"? Odin NickHK wrote: Are all the compared strings the same lengths ? If not, what would you expect from "BB26" and "BBZ526" ? How about if you just returned a binary string, representing each character ? e.g. "BB26" and "BBZ5" returns "1100" But this depend somewhat on your answer to the first question. NickHK "Odin" wrote in message oups.com... Hi All, I have asked here before and on other fora for help on the following problem ( i am a total novice at VB and Macros): I have a large excel spreadsheet with alpha-numeric data. I want to be able to compare two cells in the same row and return the disimilar characters in another cell(s). e.g. I have in cell B2, "BB26" and in cell C2, "BBZ5". I need to compare the cell C2 to cell B2 and return the disimilar characters in cell D2 which in this case would be the characters "Z5". If possible i would like to specify in cell E2 the positions of the differing characters i.e. cell E2, "34" representing the 3rd and 4th positions of the disimilar characters. If there is a better and more elegant solution to this problem that would provide a similar solution, i would appreciate your help very much. Thank you in advance. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing character strings in separate cells
I think you will need to return something like a string or array of values
indicating whether each char in the second string is : 1-Right-char, right location 2-Right-char, wrong location 3-Wrong-char 4-Position does not exist in first string What would you expect from BB26 BBBB26 You need to define more clearly the rules of comparison required. NickHK "Odin" wrote in message ups.com... Hi NickHK/JLGWhiz, I hope that this example will enable you to understand the problem better. B C D E 1 RX5TGE RX5TGEU U 7 2 I3816AE 13816AE 1 1 3 A269LYT B269LYT M 1 4 LGP5XGB LGR5ZGB RZ 35 5 BB26 BBZ526 Z5 34 6 BBZ526 BB26 This does not ocur Thanks again for the consideration. NickHK wrote: The return value would depend on whether you are basing it on the 1st or second string. What value would you expect for "BBZ526" and "BB26" then ? You could maybe use some "padding" character in the return string, so it is as long as the longer string. NickHK "Odin" wrote in message oups.com... Hi NickHK, the compared strings could be of different lengths. As i already have columns with the string lengths, in your example I would expect D2 to still return "Z5" in positions "34". The binary string solution to cell E2 is interesting but in your example would that return "110011" or just "1100"? Odin NickHK wrote: Are all the compared strings the same lengths ? If not, what would you expect from "BB26" and "BBZ526" ? How about if you just returned a binary string, representing each character ? e.g. "BB26" and "BBZ5" returns "1100" But this depend somewhat on your answer to the first question. NickHK "Odin" wrote in message oups.com... Hi All, I have asked here before and on other fora for help on the following problem ( i am a total novice at VB and Macros): I have a large excel spreadsheet with alpha-numeric data. I want to be able to compare two cells in the same row and return the disimilar characters in another cell(s). e.g. I have in cell B2, "BB26" and in cell C2, "BBZ5". I need to compare the cell C2 to cell B2 and return the disimilar characters in cell D2 which in this case would be the characters "Z5". If possible i would like to specify in cell E2 the positions of the differing characters i.e. cell E2, "34" representing the 3rd and 4th positions of the disimilar characters. If there is a better and more elegant solution to this problem that would provide a similar solution, i would appreciate your help very much. Thank you in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search group of cells for several sets of character strings | Excel Discussion (Misc queries) | |||
how do i separate 2 strings of a cell in different cells? | Excel Worksheet Functions | |||
Comparing text strings in cells | Excel Discussion (Misc queries) | |||
Comparing text strings in cells | Excel Discussion (Misc queries) | |||
Break up strings into separate cells | Excel Programming |