IF function does not display the result
Solved
CMUSHI
-
Raymond PENTIER Posted messages 58216 Registration date Status Contributor Last intervention -
Raymond PENTIER Posted messages 58216 Registration date Status Contributor Last intervention -
Hello,
I’m trying to use the IF function to check for duplicates in a list of names (a very long list), but when I validate my formula, it shows up in the cell as:
=IF(C1=C2,yes,) Is this formula “anti-text”? Or is there another problem?
If not, is there another way to identify duplicates?
Thank you for your help.
Configuration: Mac OS X / Firefox 3.0.2
I’m trying to use the IF function to check for duplicates in a list of names (a very long list), but when I validate my formula, it shows up in the cell as:
=IF(C1=C2,yes,) Is this formula “anti-text”? Or is there another problem?
If not, is there another way to identify duplicates?
Thank you for your help.
Configuration: Mac OS X / Firefox 3.0.2
2 answers
-
Hello.
It seems there are several problems:
* 1) When Excel reads =IF(C1=C2,yes;) it translates "if the content of cell C1 is equal to that of cell C2 then I show the content of the cell named yes, otherwise I show zero"; if you want to display the word yes, you need to put it in quotes in the formula =IF(C1=C2;"yes";).
* 2) If you copy this exact formula downwards, it will become successively =IF(C2=C3,yes;) =IF(C3=C4,yes;) =IF(C3=C4,yes;) which means you are always comparing 2 adjacent cells; this assumes that you have previously sorted your list alphabetically... and you cannot know if a word repeats 2 or 3 times. The SUMPRODUCT function would be more appropriate than IF.
* 3) Yes, there is another way: In the data tools, there is the "Remove duplicates" command which not only identifies all duplicates, even in an unsorted list, but also deletes them instantly.
--
It's nice, retirement! Especially in the Caribbean... :-)
☻ Raymond ♂-
A big thank you to Raymond, the quotes were indeed missing; I still tried the other solutions: the SUMPRODUCT function doesn't seem appropriate, and I couldn't find a "remove duplicates" command (where is it hiding?)
Wishing you all the best in your new activity and watch out for the "sun + tipunch" association.
-
-
Hello CMUSHI.
You didn't specify your version of Excel; that's why I didn't tell you where to look for removing duplicates.
In version 2007, it's in the "Data" tab.
In version 2003, it should be somewhere in Tools or Data.
You're right, SUMPRODUCT is not the best function here; it's better to write in B1 the formula =COUNTIF($A$1:$A$9999,A1) and copy it down; you'll get the number of occurrences of each item in the list.
Don't worry about the ti-punch: I'll take care of it!
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂-
Hello Raymond,
Unfortunately, I have the 2008 version, and I say unfortunately because I find it much less efficient than the previous two, on which I didn't encounter this kind of trouble.
Anyway, in always wanting to do more, I think they forget a little along the way...
In any case, thank you for this new formula that I will try right away.
Have a good day, -
-
