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

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 Ill just say that I have to use other peoples
data.
Here is the formula that I think should work but it doesnt.
=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 dont understand why the above formula
doesnt work. Any ideas would be welcome!

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

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.


"mmcap" wrote:

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 Ill just say that I have to use other peoples
data.
Here is the formula that I think should work but it doesnt.
=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 dont understand why the above formula
doesnt work. Any ideas would be welcome!

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

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


"mmcap" wrote:

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 Ill just say that I have to use other peoples
data.
Here is the formula that I think should work but it doesnt.
=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 dont understand why the above formula
doesnt work. Any ideas would be welcome!

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

Sorry it took so long to get back with you, Ive 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
wont 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 cant 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","")

Ill have some more time to spend on this Sunday. Well see what happens.
Regards
Norm


"JMB" wrote:

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.


"mmcap" wrote:

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 Ill just say that I have to use other peoples
data.
Here is the formula that I think should work but it doesnt.
=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 dont understand why the above formula
doesnt work. Any ideas would be welcome!