Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF in between rows | Excel Worksheet Functions | |||
Change the work sheet name in a formula by using cell reference | Excel Worksheet Functions | |||
Why does this Formula work? | Excel Worksheet Functions | |||
Making this formula work | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |