• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problem with Duplication

guitarman

Member
Hi there
I was wondering if anyone can help me out here. I have two columns of numbers e.g Column A Column B
135 135
86 86
171 171
etc etc
And when I run my conditional formatting for Duplicate numbers they all match as expected. But when I run Auto Sum on each column the total for each column is different column A amounts to 23687 and column B amounts to 23744 how could that be?. I would be very grateful if anyone can solve this problem.
 
Hi Mike ,

Since the difference is 57 , can you see if there is one amount with this value , and check that amount ?

Otherwise , use a helper column , say column C , and in that column , put in the formula :

=A1 + 0

if your data starts from row 1. Copy this down , and see if the total now comes to 23744 ; if so , it is likely that one of the amounts has been entered as text.

Narayan
 
Hi There Narayan
Many thanks for your quick response I have done exactly as you said for column A I placed =A+0 in column B and for C I placed in column D the results were exactly the same so I altered one of the the two duplicate numbers and noticed that it was not picked up by the Conditional formatting formula I have in place so that tells me it is not working properly and I am certainly not going through over 900 rows manually have you any ideas of how to overcome this problem I am working in Excel 2010. Is there a formula I can write to check this for me?
Mike
 
Hi Guitarman,

Excel has a very usefull and rarely used option.. try this..
  • Select both Rows..
  • Now Go to > Special > Row Difference.
Like below..

Row%20Diffrence.gif
 
Hi Mike ,

If you say that you altered one of the duplicate numbers , I assume you changed it so that it was no longer a duplicate. After this , if this was not flagged by your CF formula , then this is more important ; your CF formula needs to be checked.

Rather than concentrating on finding the difference , if you correct your CF formula , that will certainly highlight the value that is different.

Narayan
 
Hi Guitarman,

Excel has a very usefull and rarely used option.. try this..
  • Select both Rows..
  • Now Go to > Special > Row Difference.
Like below..

Row%20Diffrence.gif
Hi there Debraj
Thank you for your reply but one question were is this Special situated I have no idea I cannot see it anywhere on any of the Tabs could you enlighten me.
Mike
 
Hi Mike ,

If you say that you altered one of the duplicate numbers , I assume you changed it so that it was no longer a duplicate. After this , if this was not flagged by your CF formula , then this is more important ; your CF formula needs to be checked.

Rather than concentrating on finding the difference , if you correct your CF formula , that will certainly highlight the value that is different.

Narayan
Hi Narayan
How do I correct a Formula in CF that is already in Excel from what I can see there is no formula there other than letters aAbBcCdD etc etc
Mike
 
Press F5.. it will pop up.. Go To Window, there you can find the SPECIAL button.
Hi There Debraj
I have used the special facility and it worked fine all the numbers in row A and row B are duplicated as they should be but when I click Auto Sum row A amounts to 27684 and row B amounts to 27741 how can that be?
Mike
 
Hi there Somendra
Thanks for that I checked both rows and when I Used Auto Sum Row A amounted to 27684 but row B amounted to 3E+05 whatever that is because I have not got a clue he yony idea?
Regards Mike
 
@guitarman

My guess, just check for some hidden row in your data set, or may be post a sample file.

Regards,
Hi There Everyone who gave me advise on my problem with duplication I have solved it. It seems it was a circular reference was the problem i sorted that and everything works fine Thank you all for your time and trouble have a good day and a brilliant life.
Mike
 
Back
Top