IF formulas

Resolved · Medium Priority · Version 2007

Fri 11 May 2012, 15:27 Delegate 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:23 Trainer 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:57 Delegate 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:10 Trainer 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 courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Random Numbers

Type =RAND()*200 to generate a number between 1 and 200.
Use the fill handle to drag down and populate as many cells as you'd like with random numbers.

View all Excel hints and tips