ExcelBanter - View Single Post

ltong

(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