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?
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
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?
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
