Duplicate value with blank space

  • Thread starter Thread starter ltong
  • Start date Start date
L

ltong

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,
 
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.
 
Mike said:
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.
Click to expand...

Hi Mike,

Thanks, it solves the question

Regards
Lenard
 
Hi Mike,

Thanks, it solves the question

Regards
Lenard
Click to expand...


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
 
Hi Mike,

Thanks, it solves the question

Regards
Lenard
Click to expand...

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
 
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
Hi Mike,

Thanks, it solves the question

Regards
Lenard
Click to expand...

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
Click to expand...
 
Add as many characters to that array as you need. I stopped after just a few.

Dave said:
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
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
Click to expand...

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
Click to expand...
Click to expand...
 
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 said:
Add as many characters to that array as you need. I stopped after just a few.

Dave said:
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
(e-mail address removed) (ltong) 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
Click to expand...
Click to expand...
Click to expand...
 
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>


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 said:
Add as many characters to that array as you need. I stopped after just a few.

Dave said:
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:

(e-mail address removed) (ltong) 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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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>


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 said:
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:

(e-mail address removed) (ltong) 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
Click to expand...
Click to expand...
Click to expand...
 
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.
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 said:
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>


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

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:

(e-mail address removed) (ltong) 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
Click to expand...
Click to expand...
Click to expand...
 
And Chip Pearson has lots of techniques at:
http://www.cpearson.com/excel/duplicat.htm

(some worksheet formulas, some macros)

Dave said:
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.
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 said:
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

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:

(e-mail address removed) (ltong) 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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
And Chip Pearson has lots of techniques at:
http://www.cpearson.com/excel/duplicat.htm

(some worksheet formulas, some macros)

Dave said:
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.
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

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

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:

(e-mail address removed) (ltong) 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
Click to expand...
Click to expand...
Click to expand...
 
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.


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 said:
And Chip Pearson has lots of techniques at:
http://www.cpearson.com/excel/duplicat.htm

(some worksheet formulas, some macros)

Dave said:
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

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

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:

(e-mail address removed) (ltong) 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
Click to expand...
Click to expand...
Click to expand...
 
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
 
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.Copy _
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
 
Back
Top