+ Reply to Thread
Results 1 to 9 of 9

Excel 2007 : Data comparison

  1. #1
    Registered User
    Join Date
    06-22-2010
    Location
    Surrey
    MS-Off Ver
    Excel 2003
    Posts
    5

    Question Data comparison

    Hi, first time using this forum, so here goes..
    I have a large data sheet containing a column of numbers, some of which are repeated, i am trying to identify these as 'multiples' as some will appear twice, three times or more and some just once.

    I need to keep the matches in the order they originally appear on so that the data in other columns remains in place.

    I have seen once before a way to comapre the data using a simple formula so that when a number appears more than once it is shown in another column 1, 2, 3 etc depending on the number of times it appears.

    Anyone able to help how this is achieved?

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Data comparison

    Maybe Countif() ?

    e.g.

    =COUNTIF(A$1:A1,A1) copied down.

    This counts cumulatively as you go down the column... so it will say 1 at the first appearance, 2 at the second appearance, 3 at the third, etc...

    or

    =COUNTIF(A:A,A1) copied down

    This will give the total count in the whole column each time an occurance happens... so if the list has the same id twice, both id's will be marked with a 2...

    Hopefully one of these is what you were after.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    06-22-2010
    Location
    Surrey
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Data comparison

    These did not appear to work, some were showing 2 when there was no second entry ?
    Last edited by shg; 06-22-2010 at 04:02 PM. Reason: deleted spurious quote

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Data comparison

    Did you adjust the ranges or refences to suit?

    So if you are beginning in C2 and checking for matches in column B, then you would need to change to

    =COUNTIF(B$2:B2,B2) copied down

    or

    =COUNTIF(B:B,B2) copied down.

  5. #5
    Registered User
    Join Date
    06-22-2010
    Location
    Surrey
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Data comparison

    Yes I copied in the formula you showed in your first reply above, all the data was in column A the formula in column B
    Last edited by shg; 06-22-2010 at 04:26 PM. Reason: deleted spurious quote

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Data comparison

    Go to Tools|Options and in the Calculation tab, make sure Automatic is set on.

    If it is, then attach a sample showing how it is not working.

  7. #7
    Registered User
    Join Date
    06-22-2010
    Location
    Surrey
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Data comparison

    here is a sample showing the result using the top formula,
    09/275472M 1
    09/275472M 1
    09/275472M 2
    09/278402X 3

    as the numbers are in numerical order how is it showing 3 against 09/278402X when it appears only once? but the number above appears 3 times?

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Data comparison

    I don't know what you are doing, but see attached....
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    06-22-2010
    Location
    Surrey
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Data comparison

    Thanks, see that works, so I shall try again, in the morning, thanks very much for you help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1