COUNTIF conundrum

[Guest]

Why will using the cell number like A12 in a COUNTIF formula return the value
for the number of matching text strings and using a sub formula like
MID(A12,1,FIND(" (",A12,1)-3) in place of A12 not work? By using the sub
formula instead of A12 I can use the same formula for the whole sheet even
with different row headings. Without going into a long explanation the
(date) needs to be in the same cell as the row heading.
A B
C
ICSC-UBS 1 (JAN 20)
ICSC-UBS 2 (Jan 27)
ICSC-UBS 3 (JAN 6)
ICSC-UBS 4 (DEC 30)

MID(A12,1,FIND(" (",A12,1)-3) returns ICSC-UBS there 4 cells that contain
this text string. Is it because ICSC-UBS is only a portion? If so is there
a way to get the COUNTIF to count the matches returned by the
MID(A12,1,FIND(" (",A12,1)-3)

Here is the formula in both forms.

COUNTIF($A$2:$A$150,A12)

COUNTIF($A$2:$A$150,MID(A12,1,FIND(" (",A12,1)-3))

What I’m ultimately trying to accomplish is to find duplicate entries that
match except for the date.
This formula works for exact matches but won’t work with the different dates.

=IF(COUNTIF($A$2:$A$150,A12)>1,"Duplicate","") I just need to get the
COUNTIF to work and the IF formula should work.

 

[Guest]

=COUNTIF(A1:A100,"ICSC-UBS*")

 

[Guest]

If you want to check if there are any duplicates, exclusive of the date
reference...

Example:
if A4: ICSC-UBS 4 (DEC 30)
Then you want to know if any other cells in A2:A150 *begin with*ICSC-UBS 4".

If that's true, then try this in a cell across from A4:
=IF(COUNTIF($A$2:$A$150,MID(A4,1,FIND(" (",A4,1)-1)&"*")>1,"Duplicate","")

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

That works great thank you VERY much!
Norm

=COUNTIF(A1:A100,"ICSC-UBS*")

 

[Guest]

Yes this is EXACTLY what I was looking for. I've spent many hours banging my
head against the wall trying to accomplish what this formula does. I was
getting ready to leave work when I saw the other reply and I didn't think
about that it will only find matches for the ICSC-UBS. There well over 100
headings that I need to keep track of and this one formula will work for all
of them. Thank you VERY much!

 

[Guest]

Thank you so much for the feedback. I'm very glad I could help.


***********
Regards,
Ron

XL2002, WinXP