| 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Duplicate value with blank space
Hi Dave,
Thanks, it works ......but when I add new characters to that array in the cleanEmUp macro, e.g. myBadChars = Array("-", "[", "]", "{", "}", "(", ")","*"," "), and run the macro on the selected cell contains a numbering as I-03-02 14*(P-PG), it turns out as blank cell ?? Did I miss out anything ? Sorry, I'm still learning excel VBA. Regards Lenard Dave Peterson wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Duplicate value with blank space
Hi Dave,
Thanks again, it works pretty well. I've another guestion.... After the substitute function is used, I'd like to search the entire worksheet, row by row where there is no color being highlighted on the cell ( ie no duplicated value ), it will extract the whole row ( i.e. row without color ) from columns A to G and transfer to a new worksheet under the same workbook. This will continue to extract row by row until it finds that there is no data on the highlighted cell from the entire worksheet. How to device an excel function or excel VBA to solve the above problem ? Thanks in advance Regards Lenard Dave Peterson wrote in message ... Excel allows you to specify wild cards in Edit|Find. Excel supports these two wild cards: * = any set of characters ? = any one character To look/replace an asterisk, you use ~* (tilde is to the left of the 1/! key on my USA keyboard) to replace a question mark, you use ~? And since you're using the tilde (~) as an "escape" character, you have to use this: ~~ (doubled up) to find/replace the single ~ (tilde). So in your case, you didn't do anything wrong--just got hit by something that I didn't expect. myBadChars = Array("-", "[", "]", "{", "}", "(", ")", "~*", " ") should do it. == Pretty neat way to empty cells, though, huh??? <vbg ltong wrote: Hi Dave, Thanks, it works ......but when I add new characters to that array in the cleanEmUp macro, e.g. myBadChars = Array("-", "[", "]", "{", "}", "(", ")","*"," "), and run the macro on the selected cell contains a numbering as I-03-02 14*(P-PG), it turns out as blank cell ?? Did I miss out anything ? Sorry, I'm still learning excel VBA. Regards Lenard Dave Peterson wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Duplicate value with blank space
If you're trying to get a list of unique entries, take a look at Debra
Dalgleish's site: http://www.contextures.com/xladvfilter01.html#FilterUR The copy|Paste the visible rows to the other sheet. ltong wrote: Hi Dave, Thanks again, it works pretty well. I've another guestion.... After the substitute function is used, I'd like to search the entire worksheet, row by row where there is no color being highlighted on the cell ( ie no duplicated value ), it will extract the whole row ( i.e. row without color ) from columns A to G and transfer to a new worksheet under the same workbook. This will continue to extract row by row until it finds that there is no data on the highlighted cell from the entire worksheet. How to device an excel function or excel VBA to solve the above problem ? Thanks in advance Regards Lenard Dave Peterson wrote in message ... Excel allows you to specify wild cards in Edit|Find. Excel supports these two wild cards: * = any set of characters ? = any one character To look/replace an asterisk, you use ~* (tilde is to the left of the 1/! key on my USA keyboard) to replace a question mark, you use ~? And since you're using the tilde (~) as an "escape" character, you have to use this: ~~ (doubled up) to find/replace the single ~ (tilde). So in your case, you didn't do anything wrong--just got hit by something that I didn't expect. myBadChars = Array("-", "[", "]", "{", "}", "(", ")", "~*", " ") should do it. == Pretty neat way to empty cells, though, huh??? <vbg ltong wrote: Hi Dave, Thanks, it works ......but when I add new characters to that array in the cleanEmUp macro, e.g. myBadChars = Array("-", "[", "]", "{", "}", "(", ")","*"," "), and run the macro on the selected cell contains a numbering as I-03-02 14*(P-PG), it turns out as blank cell ?? Did I miss out anything ? Sorry, I'm still learning excel VBA. Regards Lenard Dave Peterson wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Duplicate value with blank space
And Chip Pearson has lots of techniques at:
http://www.cpearson.com/excel/duplicat.htm (some worksheet formulas, some macros) Dave Peterson wrote: If you're trying to get a list of unique entries, take a look at Debra Dalgleish's site: http://www.contextures.com/xladvfilter01.html#FilterUR The copy|Paste the visible rows to the other sheet. ltong wrote: Hi Dave, Thanks again, it works pretty well. I've another guestion.... After the substitute function is used, I'd like to search the entire worksheet, row by row where there is no color being highlighted on the cell ( ie no duplicated value ), it will extract the whole row ( i.e. row without color ) from columns A to G and transfer to a new worksheet under the same workbook. This will continue to extract row by row until it finds that there is no data on the highlighted cell from the entire worksheet. How to device an excel function or excel VBA to solve the above problem ? Thanks in advance Regards Lenard Dave Peterson wrote in message ... Excel allows you to specify wild cards in Edit|Find. Excel supports these two wild cards: * = any set of characters ? = any one character To look/replace an asterisk, you use ~* (tilde is to the left of the 1/! key on my USA keyboard) to replace a question mark, you use ~? And since you're using the tilde (~) as an "escape" character, you have to use this: ~~ (doubled up) to find/replace the single ~ (tilde). So in your case, you didn't do anything wrong--just got hit by something that I didn't expect. myBadChars = Array("-", "[", "]", "{", "}", "(", ")", "~*", " ") should do it. == Pretty neat way to empty cells, though, huh??? <vbg ltong wrote: Hi Dave, Thanks, it works ......but when I add new characters to that array in the cleanEmUp macro, e.g. myBadChars = Array("-", "[", "]", "{", "}", "(", ")","*"," "), and run the macro on the selected cell contains a numbering as I-03-02 14*(P-PG), it turns out as blank cell ?? Did I miss out anything ? Sorry, I'm still learning excel VBA. Regards Lenard Dave Peterson wrote in message ... 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 -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Duplicate value with blank space
Hi Dave,
Advance Filter is a very useful technique in excel and it works in normal situation to get a list of unique entries. However, it seems that it doesn't work in my situation, particularly its conditions fails : - the database contains blank rows and this blank rows can not be eliminated as they are part of the database ( e.g. in the accounting terms where there is a value in debit entry, say cell E2 correspondingly there is no value in credit entry, say cell F2 ) Further, the result needs to get a list of entries excluding duplicated value and it only extracts and transfers the entire rows from range A1:H44 for any value not being highlighted by the duplicate value function " =COUNTIF($H$2:$H$44,H2)1 and its font is to be bold with yellow color "in column H, which contains the value 4003660, 4004045000, 4001600 in column H for the following example to a new worksheet under the same workbook : - 1) 4003660 2) 338845000 3) 338845000 4) 4004045000 5) 33881300 6) 4001600 7) 33881300 In this case, there are a few of non-highlighted value in cell H1, H4 & H6 as in the above example and it will extract and transfer the entire row by row, range from A1 to H1, A4 to H4 and A6 to H6. After the transfer, it will automatically eliminate the blank rows in range A1 to H1, A4 to H4 and A6 to H6. Please advise on how to deal with this situation. Thanks Regards Lenard Dave Peterson wrote in message ... And Chip Pearson has lots of techniques at: http://www.cpearson.com/excel/duplicat.htm (some worksheet formulas, some macros) Dave Peterson wrote: If you're trying to get a list of unique entries, take a look at Debra Dalgleish's site: http://www.contextures.com/xladvfilter01.html#FilterUR The copy|Paste the visible rows to the other sheet. ltong wrote: Hi Dave, Thanks again, it works pretty well. I've another guestion.... After the substitute function is used, I'd like to search the entire worksheet, row by row where there is no color being highlighted on the cell ( ie no duplicated value ), it will extract the whole row ( i.e. row without color ) from columns A to G and transfer to a new worksheet under the same workbook. This will continue to extract row by row until it finds that there is no data on the highlighted cell from the entire worksheet. How to device an excel function or excel VBA to solve the above problem ? Thanks in advance Regards Lenard Dave Peterson wrote in message ... Excel allows you to specify wild cards in Edit|Find. Excel supports these two wild cards: * = any set of characters ? = any one character To look/replace an asterisk, you use ~* (tilde is to the left of the 1/! key on my USA keyboard) to replace a question mark, you use ~? And since you're using the tilde (~) as an "escape" character, you have to use this: ~~ (doubled up) to find/replace the single ~ (tilde). So in your case, you didn't do anything wrong--just got hit by something that I didn't expect. myBadChars = Array("-", "[", "]", "{", "}", "(", ")", "~*", " ") should do it. == Pretty neat way to empty cells, though, huh??? <vbg ltong wrote: Hi Dave, Thanks, it works ......but when I add new characters to that array in the cleanEmUp macro, e.g. myBadChars = Array("-", "[", "]", "{", "}", "(", ")","*"," "), and run the macro on the selected cell contains a numbering as I-03-02 14*(P-PG), it turns out as blank cell ?? Did I miss out anything ? Sorry, I'm still learning excel VBA. Regards Lenard Dave Peterson wrote in message ... 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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Duplicate value with blank space
I think I'd use a separate cell and put that same conditional formatting in that
cell--testing the color of a cell based on conditional formatting is usually frought with problems. It's lots easier to just duplicate the conditional formatting rules into a helper cell (or column of cells). (You could hide this column if you don't want to see it.) Then you could just use Data|Filter|autofilter. Show the values that = 1 and copy|paste to the new sheet. Record a macro if you need to have it more mechanized. ltong wrote: Hi Dave, Advance Filter is a very useful technique in excel and it works in normal situation to get a list of unique entries. However, it seems that it doesn't work in my situation, particularly its conditions fails : - the database contains blank rows and this blank rows can not be eliminated as they are part of the database ( e.g. in the accounting terms where there is a value in debit entry, say cell E2 correspondingly there is no value in credit entry, say cell F2 ) Further, the result needs to get a list of entries excluding duplicated value and it only extracts and transfers the entire rows from range A1:H44 for any value not being highlighted by the duplicate value function " =COUNTIF($H$2:$H$44,H2)1 and its font is to be bold with yellow color "in column H, which contains the value 4003660, 4004045000, 4001600 in column H for the following example to a new worksheet under the same workbook : - 1) 4003660 2) 338845000 3) 338845000 4) 4004045000 5) 33881300 6) 4001600 7) 33881300 In this case, there are a few of non-highlighted value in cell H1, H4 & H6 as in the above example and it will extract and transfer the entire row by row, range from A1 to H1, A4 to H4 and A6 to H6. After the transfer, it will automatically eliminate the blank rows in range A1 to H1, A4 to H4 and A6 to H6. Please advise on how to deal with this situation. Thanks Regards Lenard Dave Peterson wrote in message ... And Chip Pearson has lots of techniques at: http://www.cpearson.com/excel/duplicat.htm (some worksheet formulas, some macros) Dave Peterson wrote: If you're trying to get a list of unique entries, take a look at Debra Dalgleish's site: http://www.contextures.com/xladvfilter01.html#FilterUR The copy|Paste the visible rows to the other sheet. ltong wrote: Hi Dave, Thanks again, it works pretty well. I've another guestion.... After the substitute function is used, I'd like to search the entire worksheet, row by row where there is no color being highlighted on the cell ( ie no duplicated value ), it will extract the whole row ( i.e. row without color ) from columns A to G and transfer to a new worksheet under the same workbook. This will continue to extract row by row until it finds that there is no data on the highlighted cell from the entire worksheet. How to device an excel function or excel VBA to solve the above problem ? Thanks in advance Regards Lenard Dave Peterson wrote in message ... Excel allows you to specify wild cards in Edit|Find. Excel supports these two wild cards: * = any set of characters ? = any one character To look/replace an asterisk, you use ~* (tilde is to the left of the 1/! key on my USA keyboard) to replace a question mark, you use ~? And since you're using the tilde (~) as an "escape" character, you have to use this: ~~ (doubled up) to find/replace the single ~ (tilde). So in your case, you didn't do anything wrong--just got hit by something that I didn't expect. myBadChars = Array("-", "[", "]", "{", "}", "(", ")", "~*", " ") should do it. == Pretty neat way to empty cells, though, huh??? <vbg ltong wrote: Hi Dave, Thanks, it works ......but when I add new characters to that array in the cleanEmUp macro, e.g. myBadChars = Array("-", "[", "]", "{", "}", "(", ")","*"," "), and run the macro on the selected cell contains a numbering as I-03-02 14*(P-PG), it turns out as blank cell ?? Did I miss out anything ? Sorry, I'm still learning excel VBA. Regards Lenard Dave Peterson wrote in message ... 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 -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Duplicate value with blank space
Hi Dave,
Sorry, it is quite difficult for me to visualize your ideas. Perhaps, let make a simple scenario and kindly illustrate with your suggestion for the following : - A database of 8 columns which range from A1: H6 a) ¡§Number¡¨ in cell A1 as Column A header with the following data :- 1) A2 : 1 2) A3 : 2 3) A4 : 3 4) A5 : 4 5) A6 : 5 b) ¡§ Invoice Date¡¨ in cell B1 as Column B header with the following data :- 1) B2 : 14/3/04 2) B3 : 28/5/04 3) B4 : 3/8/04 4) B5 : 28/6/04 5) B6 : 10/5/04 b) ¡§ Particulars¡¨ in cell C1 as Column C header with the following data : - 1) C2 : I03-0046 (P-PG) 2) C3 : I04-0033 (P-PG) 3) C4 : O/R: 003388 4) C5 : I04-0036 (P-PG) 5) C6 : I04-0040 (P-K) c) ¡§Match¡¨ in cell D1 as Column D header with the following data : - 1) D2 : 40033 2) D3 : 3388 3) D4 : 3388 4) D5 : 40040 5) D6 : 3388 d) ¡§Debit¡¨ in cell E1 as Column E header with the following data : - 1) E2 : 60.00 2) E3 : 45,000.00 3) E4 : Blank 4) E5 : 35,000.00 5) E6 : 1,300.00 e) ¡§Credit¡¨ in cell F1 as Column F header with the following data : - 1) F2 : Blank 2) F3 : Blank 3) F4 : 45,000.00 4) F5 : Blank 5) F6 : Blank f) ¡§Balance¡¨ in cell G1 as Column G header with the following data : - 1) G2: 60.00 2) G3: 45,060.00 3) G4 : 60.00 4) G5 : 35,060.00 5) G6 : 36,360.00 g) ¡§Duplicate¡¨ in cell H1 as Column H header with the following data : - 1) H2 : 4003360 2) H3 : 338845000 3) H4 : 338845000 4) H5 : 4004045000 5) H6 : 33881300 ( Note : Only Column H contains duplicate value as highlighted by excel function ¡§ =COUNTIF($H$2:$H$6,H2)1 and format with font red bold + pattern with yellow color¡¨ in cell H3 and H4 ) The above database will change every month. Thus, I need a dynamic excel function or excel VBA to extract & transfer non-duplicated entries to a new worksheet. Please help. Thanks Regards Lenard *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Duplicate value with blank space
Turn on the macro recorder when you insert a helper column (column I).
Put a header in I1 Fill that column with that duplicate formula: =COUNTIF($H:$H,H2)1 Filter by column I to show True Then copy those visible cells to a new sheet. If you can rely on having that index in column A: I modified the recorded macro that I got and finished with this: Option Explicit Sub testme() Dim LastRow As Long Dim newWks As Worksheet Dim curWks As Worksheet Set curWks = ActiveSheet Set newWks = Worksheets.Add With curWks LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("I:I").EntireColumn.Insert .Range("I1").Value = "Dupes" .Range("I2:I" & LastRow).Formula _ = "=COUNTIF($H:$H,H2)1" 'remove autofilter .AutoFilterMode = False .Range("I:I").AutoFilter field:=1, Criteria1:=True With .AutoFilter.Range If .Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then 'only header visible, so do nothing MsgBox "No duplicates found" Else .Cells.SpecialCells(xlCellTypeVisible).EntireRow.C opy _ Destination:=newWks.Range("A1") 'clean up newwks helper column newWks.Range("i:I").Delete End If End With .AutoFilterMode = False .Range("i:i").Delete End With End Sub Lenard Ong wrote: Hi Dave, Sorry, it is quite difficult for me to visualize your ideas. Perhaps, let make a simple scenario and kindly illustrate with your suggestion for the following : - A database of 8 columns which range from A1: H6 a) ¡§Number¡¨ in cell A1 as Column A header with the following data :- 1) A2 : 1 2) A3 : 2 3) A4 : 3 4) A5 : 4 5) A6 : 5 b) ¡§ Invoice Date¡¨ in cell B1 as Column B header with the following data :- 1) B2 : 14/3/04 2) B3 : 28/5/04 3) B4 : 3/8/04 4) B5 : 28/6/04 5) B6 : 10/5/04 b) ¡§ Particulars¡¨ in cell C1 as Column C header with the following data : - 1) C2 : I03-0046 (P-PG) 2) C3 : I04-0033 (P-PG) 3) C4 : O/R: 003388 4) C5 : I04-0036 (P-PG) 5) C6 : I04-0040 (P-K) c) ¡§Match¡¨ in cell D1 as Column D header with the following data : - 1) D2 : 40033 2) D3 : 3388 3) D4 : 3388 4) D5 : 40040 5) D6 : 3388 d) ¡§Debit¡¨ in cell E1 as Column E header with the following data : - 1) E2 : 60.00 2) E3 : 45,000.00 3) E4 : Blank 4) E5 : 35,000.00 5) E6 : 1,300.00 e) ¡§Credit¡¨ in cell F1 as Column F header with the following data : - 1) F2 : Blank 2) F3 : Blank 3) F4 : 45,000.00 4) F5 : Blank 5) F6 : Blank f) ¡§Balance¡¨ in cell G1 as Column G header with the following data : - 1) G2: 60.00 2) G3: 45,060.00 3) G4 : 60.00 4) G5 : 35,060.00 5) G6 : 36,360.00 g) ¡§Duplicate¡¨ in cell H1 as Column H header with the following data : - 1) H2 : 4003360 2) H3 : 338845000 3) H4 : 338845000 4) H5 : 4004045000 5) H6 : 33881300 ( Note : Only Column H contains duplicate value as highlighted by excel function ¡§ =COUNTIF($H$2:$H$6,H2)1 and format with font red bold + pattern with yellow color¡¨ in cell H3 and H4 ) The above database will change every month. Thus, I need a dynamic excel function or excel VBA to extract & transfer non-duplicated entries to a new worksheet. Please help. Thanks Regards Lenard *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! -- Dave Peterson |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Duplicate value with blank space
Hi Dave,
Thanks a lot for your help and it works. Regards Lenard Dave Peterson wrote in message ... Turn on the macro recorder when you insert a helper column (column I). Put a header in I1 Fill that column with that duplicate formula: =COUNTIF($H:$H,H2)1 Filter by column I to show True Then copy those visible cells to a new sheet. If you can rely on having that index in column A: I modified the recorded macro that I got and finished with this: Option Explicit Sub testme() Dim LastRow As Long Dim newWks As Worksheet Dim curWks As Worksheet Set curWks = ActiveSheet Set newWks = Worksheets.Add With curWks LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("I:I").EntireColumn.Insert .Range("I1").Value = "Dupes" .Range("I2:I" & LastRow).Formula _ = "=COUNTIF($H:$H,H2)1" 'remove autofilter .AutoFilterMode = False .Range("I:I").AutoFilter field:=1, Criteria1:=True With .AutoFilter.Range If .Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then 'only header visible, so do nothing MsgBox "No duplicates found" Else .Cells.SpecialCells(xlCellTypeVisible).EntireRow.C opy _ Destination:=newWks.Range("A1") 'clean up newwks helper column newWks.Range("i:I").Delete End If End With .AutoFilterMode = False .Range("i:i").Delete End With End Sub Lenard Ong wrote: Hi Dave, Sorry, it is quite difficult for me to visualize your ideas. Perhaps, let make a simple scenario and kindly illustrate with your suggestion for the following : - A database of 8 columns which range from A1: H6 a) ¡§Number¡¨ in cell A1 as Column A header with the following data :- 1) A2 : 1 2) A3 : 2 3) A4 : 3 4) A5 : 4 5) A6 : 5 b) ¡§ Invoice Date¡¨ in cell B1 as Column B header with the following data :- 1) B2 : 14/3/04 2) B3 : 28/5/04 3) B4 : 3/8/04 4) B5 : 28/6/04 5) B6 : 10/5/04 b) ¡§ Particulars¡¨ in cell C1 as Column C header with the following data : - 1) C2 : I03-0046 (P-PG) 2) C3 : I04-0033 (P-PG) 3) C4 : O/R: 003388 4) C5 : I04-0036 (P-PG) 5) C6 : I04-0040 (P-K) c) ¡§Match¡¨ in cell D1 as Column D header with the following data : - 1) D2 : 40033 2) D3 : 3388 3) D4 : 3388 4) D5 : 40040 5) D6 : 3388 d) ¡§Debit¡¨ in cell E1 as Column E header with the following data : - 1) E2 : 60.00 2) E3 : 45,000.00 3) E4 : Blank 4) E5 : 35,000.00 5) E6 : 1,300.00 e) ¡§Credit¡¨ in cell F1 as Column F header with the following data : - 1) F2 : Blank 2) F3 : Blank 3) F4 : 45,000.00 4) F5 : Blank 5) F6 : Blank f) ¡§Balance¡¨ in cell G1 as Column G header with the following data : - 1) G2: 60.00 2) G3: 45,060.00 3) G4 : 60.00 4) G5 : 35,060.00 5) G6 : 36,360.00 g) ¡§Duplicate¡¨ in cell H1 as Column H header with the following data : - 1) H2 : 4003360 2) H3 : 338845000 3) H4 : 338845000 4) H5 : 4004045000 5) H6 : 33881300 ( Note : Only Column H contains duplicate value as highlighted by excel function ¡§ =COUNTIF($H$2:$H$6,H2)1 and format with font red bold + pattern with yellow color¡¨ in cell H3 and H4 ) The above database will change every month. Thus, I need a dynamic excel function or excel VBA to extract & transfer non-duplicated entries to a new worksheet. Please help. Thanks Regards Lenard *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Duplicate value with blank space
Whew!
Glad it finally worked. ltong wrote: Hi Dave, Thanks a lot for your help and it works. Regards Lenard <<snipped |
| 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) | |||
Linear Mode
