I don't understand why the formula doesn't work

G

Guest

I need to find duplicate entries in a workbook. Without going into a very
long and boring explanation as to why the dates are in with the headings and
other odd setup practices I’ll just say that I have to use other people’s
data.
Here is the formula that I think should work but it doesn’t.
=IF(COUNTIF($A$2:$A$150,LEFT(A1,LEN(A1)-8)),"Duplicate","")
I need to be able to find duplicates that match except for the date on the
end.
If I could get it to work on the two below then I should be able to apply it
to any of the multitude of headings in the worksheet.
A B C
1 Existing Home Sales (DEC 28)
94 Existing Home Sales (JAN 4)

These need to show as duplicate and I don’t understand why the above formula
doesn’t work. Any ideas would be welcome!
 
G

Guest

when you take 8 characters of off the end it leaves a trailing space on the
first one, so you get

Existing Home Sales<space>
Existing Home Sales

which are not equal.

If the dates are all only 7-8 characters with a space separating them from
the rest of the data, you could use TRIM to remove the space:

=IF(COUNTIF($A$2:$A$150,TRIM(LEFT(A1,LEN(A1)-8))),"Duplicate","")

or you could try using FIND to locate the "(":
=IF(COUNTIF($A$2:$A$150,TRIM(LEFT(A1,FIND("(", A1)-1))),"Duplicate","")

I have not tested these, so am hoping I got the parentheses in the right
places.
 
G

Guest

Create a helper column

B2: =LEFT(A2,FIND("(",A2)-2)
copy down from B2 to B150

C2: =IF(COUNTIF($B$2:$B$150,B2)>1,"duplicate","no")
copy down from C2 to C150
 
G

Guest

Sorry it took so long to get back with you, I’ve been pretty busy. Any way,
the FIND function works GREAT it returns everything to the left of the
parentheses no matter how long the text string. Now there seems to be a bit
of a problem in the COUNTIF section. Even though it recognizes the text
strings as an exact match it returns a zero value, so the rest of the formula
won’t return the desired “duplicateâ€.
MID(A12,1,FIND(" (",A12,1)-1) as I said returns the desired string to the
left of the parentheses.

The problem seems to be in the
COUNTIF(A9:A12,EXACT(MID(A12,1,FIND("(",A12,1)-1),MID(A12,1,FIND("(",A12,1)-1)))
I spent hours on this Friday but I can’t find a way to get it to return a
number greater than zero so it will actually count the matches.

I think the rest of the formula below will work if the COUNTIF works
=IF(COUNTIF(A9:A12,EXACT(MID(A12,1,FIND("(",A12,1)-1),MID(A12,1,FIND("(",A12,1)-1)))>1,"Duplicate","")

I’ll have some more time to spend on this Sunday. We’ll see what happens.
Regards
Norm
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top