| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplicate value with blank space
Hi,
Can anyone pls help me to solve the below question? I've 2 columns typed with the respective header:Ref No(Column C) and Amount ( Column H). I 'd like to check whether Ref No in column C corresponding with the amount in column H are duplicate in these columns. If there is duplicate then highlight the cell with colors. The COUNTIF function is working fine for the above situation but it fails to detect the following values, particularly with blank space in between the value in Ref No column although they are considered the same number or duplicate :- Ref No Amount A BOT 987415 $250.00 B BOT987415 $250.00 C BOT 987415 $250.00 Is there anyway that can help to solve the above either excel VBA or excel function? ( ie to give the same solution as COUNTIF function ) Thanks in advance. Rgds Lenard, |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Duplicate value with blank space
If you can do it, insert a column (could be hidden) and
eliminate the spaces in the Ref No and concatenate with the amount. To eliminate the spaces, use the substitue function. If the first BOT entry is in cell B2 and the first amount in C2, in D2 enter =substitute(B2," ","") &C2. In effect you've made a unique key Excel can now check against. Now use that unique key in a conditional formatting to highlight those instances where they are the same. -----Original Message----- Hi, Can anyone pls help me to solve the below question? I've 2 columns typed with the respective header:Ref No (Column C) and Amount ( Column H). I 'd like to check whether Ref No in column C corresponding with the amount in column H are duplicate in these columns. If there is duplicate then highlight the cell with colors. The COUNTIF function is working fine for the above situation but it fails to detect the following values, particularly with blank space in between the value in Ref No column although they are considered the same number or duplicate :- Ref No Amount A BOT 987415 $250.00 B BOT987415 $250.00 C BOT 987415 $250.00 Is there anyway that can help to solve the above either excel VBA or excel function? ( ie to give the same solution as COUNTIF function ) Thanks in advance. Rgds Lenard, . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Duplicate value with blank space
"Mike" wrote in message ...
If you can do it, insert a column (could be hidden) and eliminate the spaces in the Ref No and concatenate with the amount. To eliminate the spaces, use the substitue function. If the first BOT entry is in cell B2 and the first amount in C2, in D2 enter =substitute(B2," ","") &C2. In effect you've made a unique key Excel can now check against. Now use that unique key in a conditional formatting to highlight those instances where they are the same. -----Original Message----- Hi, Can anyone pls help me to solve the below question? I've 2 columns typed with the respective header:Ref No (Column C) and Amount ( Column H). I 'd like to check whether Ref No in column C corresponding with the amount in column H are duplicate in these columns. If there is duplicate then highlight the cell with colors. The COUNTIF function is working fine for the above situation but it fails to detect the following values, particularly with blank space in between the value in Ref No column although they are considered the same number or duplicate :- Ref No Amount A BOT 987415 $250.00 B BOT987415 $250.00 C BOT 987415 $250.00 Is there anyway that can help to solve the above either excel VBA or excel function? ( ie to give the same solution as COUNTIF function ) Thanks in advance. Rgds Lenard, . Hi Mike, Thanks, it solves the question Regards Lenard |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Duplicate value with blank space
|
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Duplicate value with blank space
|
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Duplicate value with blank space
Add as many characters to that array as you need. I stopped after just a few.
Dave Peterson wrote: You could nest a bunch of substitutes together like: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"[",""),"]","") (but excel has a limit of 7 nested functions) Maybe a macro would work better: Option Explicit Sub cleanEmUp() Dim myBadChars As Variant Dim iCtr As Long myBadChars = Array("-", "[", "]", "{", "}", "(", ")") For iCtr = LBound(myBadChars) To UBound(myBadChars) Selection.Replace What:=myBadChars(iCtr), _ Replacement:="", _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False Next iCtr End Sub Select your cells and run this macro. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ltong wrote: (ltong) wrote in message . com... "Mike" wrote in message ... If you can do it, insert a column (could be hidden) and eliminate the spaces in the Ref No and concatenate with the amount. To eliminate the spaces, use the substitue function. If the first BOT entry is in cell B2 and the first amount in C2, in D2 enter =substitute(B2," ","") &C2. In effect you've made a unique key Excel can now check against. Now use that unique key in a conditional formatting to highlight those instances where they are the same. -----Original Message----- Hi, Can anyone pls help me to solve the below question? I've 2 columns typed with the respective header:Ref No (Column C) and Amount ( Column H). I 'd like to check whether Ref No in column C corresponding with the amount in column H are duplicate in these columns. If there is duplicate then highlight the cell with colors. The COUNTIF function is working fine for the above situation but it fails to detect the following values, particularly with blank space in between the value in Ref No column although they are considered the same number or duplicate :- Ref No Amount A BOT 987415 $250.00 B BOT987415 $250.00 C BOT 987415 $250.00 Is there anyway that can help to solve the above either excel VBA or excel function? ( ie to give the same solution as COUNTIF function ) Thanks in advance. Rgds Lenard, . Hi Mike, Thanks, it solves the question Regards Lenard Hi, I've another questions as follows : - A)Is there anyway to remove "( )", "[ ]" or "{ }" from the numbering in a cell ?, ie the result will be I0307981PS,I04L04513,I0220105S for the following examples It seems that SUBSTITUTE function fails to solve this question, ie =SUBSTITUTE(A2,"()","") E.g. 1) I03-07981(P-S) 2) I04-[L]04513 3) I02-201{05}S B) How to extract a numbering as I030214PPG without the quotation marks from I-03-02 14*(P-PG) ? Can SUBSTITUTE function solve this question as well ? Please helps Thanks Regards Lenard -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Blank space | Excel Worksheet Functions | |||
| How many way to represent the blank space? | Excel Discussion (Misc queries) | |||
| add blank space in cell if first name is not blank | Excel Worksheet Functions | |||
| BLANK SPACE TO A NUMBER | Excel Worksheet Functions | |||
| I want a blank space on a worksheet when the question is blank? | Excel Discussion (Misc queries) | |||
Hybrid Mode
