+ Reply to Thread
Results 1 to 8 of 8

Newbie in need of help with Frequency function please

  1. #1
    Registered User
    Join Date
    11-16-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Question Newbie in need of help with Frequency function please

    Hi, and apologies for joining and getting straight in with a 'help' request ... but I'm baffled.

    I have a list (about 6000 rows) of numbers. Each number appears between once and 20 times. I'd like to identify the numbers that appear only once. I think I need to use the Frequency function but I'm struggling with understanding it.

    I'd appreciate any help to:
    a) suggest another way of identifying numbers that only appear once
    b) understand the Frequency function to resolve my problem.

    Just to be clear, as a small example...
    2
    5
    5
    7
    7
    7
    8

    I only want to be able to find those that appear once (the 2 & 8)

    Thanks in advance
    Last edited by chan73; 11-19-2012 at 04:21 PM. Reason: Solved

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Newbie in need of help with Frequency function please

    you can use the countif() function for this. assuming you're data is in column A...

    =IF(COUNTIF($A$1:$A$7,A1)=1,1,"")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    11-16-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Newbie in need of help with Frequency function please

    Thanks for the quick reply FDibbins. I've tried that to no avail ... is it my usage:
    =IF(COUNTIF($S$1:$S$5758,S1)=1,1,"")
    The result is 1?

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Newbie in need of help with Frequency function please

    Quote Originally Posted by chan73 View Post
    Hi, and apologies for joining and getting straight in with a 'help' request ... but I'm baffled.

    I have a list (about 6000 rows) of numbers. Each number appears between once and 20 times. I'd like to identify the numbers that appear only once. I think I need to use the Frequency function but I'm struggling with understanding it.

    I'd appreciate any help to:
    a) suggest another way of identifying numbers that only appear once
    b) understand the Frequency function to resolve my problem.

    Just to be clear, as a small example...
    2
    5
    5
    7
    7
    7
    8

    I only want to be able to find those that appear once (the 2 & 8)

    Thanks in advance
    thats what you asked for, wast it? that formula will give you, in column B, a list of only those entries that appear only once. if you want to see just that list, you can apply filters and filter on that column

  5. #5
    Registered User
    Join Date
    11-16-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Newbie in need of help with Frequency function please

    Cheers ... but that formula doesn't work

    No matter what data you run that against, the answer is always 1.

    Thanks for trying - very much appreciated

  6. #6
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Newbie in need of help with Frequency function please

    Hi chan73

    This works, you need to enter the formula somewhere in row 1 and copy down to row 5758. I don't think that is what you require! I think you are after a formula that returns a list numbers that only appear once.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    11-16-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Newbie in need of help with Frequency function please

    Hi Kevin
    Cheers for that . I'm there. Thanks for the help guys :-)
    Very much appreciated

  8. #8
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Newbie in need of help with Frequency function please

    Hi chan73

    Thanks for the feed back.

+ 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