98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » IF formulas
IF formulas
Resolved · Medium Priority · Version 2007
Fri 11 May 2012, 15:27Delegate Samantha said...
Samantha has attended:
Excel Advanced course
Excel Intermediate course
IF formulas
I am working on a spreadsheet that has an address list - door number and road being in one column. I have sorted this column in number order using A-Z sort and used the following formula to find duplicates - =IF(C2=C3,0,1). The problem being that even though two lines have an identical address if one has a space after it, it's not recognised as a duplicate. How do I get around this?
Mon 14 May 2012, 09:23Trainer Mark said...
RE: IF formulas
Hello Samantha,
You could use the trim function which removes any extra spaces before or after the cell content.
So your current formula would read:
if(A1=TRIM(A2),0,1)
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Mark
Microsoft Office Specialist Trainer
Tue 15 May 2012, 10:57Delegate Samantha said...
RE: IF formulas
Hi Mark
Thank you for getting back to me.
I tried the formula you gave me with the trim function and whilst it did identify the majority of duplicates, it did still leave back one.
On checking the cells manually, there were identical and both had the space after, so not sure why the formula didn't pick it up?
The formula only identified it as a duplicate when I deleted the space at the end of both cells. Luckily that particular address list was relatively short so easy to check manually.
But the formula did pick up all the other duplicates, so maybe just missed this one?
Mon 21 May 2012, 09:10Trainer Mark said...
RE: IF formulas
Hello Samantha,
Glad it worked, as for it missing the single one in the list, without seeing the original sheet and data it is difficult to know why. If it continues to miss entries let me know and we'll look at alternative ways of checking.
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Mark
Microsoft Office Specialist Trainer
Mon 28 May 2012: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Random NumbersType =RAND()*200 to generate a number between 1 and 200. |
