Your browser isn't supported
It looks like you're using an old web browser. To get the most out of the site and to ensure guides display correctly, we suggest upgrading your browser now. Download the latest:

Welcome to the MSE Forums

We're home to a fantastic community of MoneySavers but anyone can post. Please exercise caution & report spam, illegal, offensive or libellous posts/messages: click "report" or email forumteam@. Skimlinks & other affiliated links are turned on

Search
  • FIRST POST
    • hueknight1969
    • By hueknight1969 8th Feb 18, 9:52 PM
    • 164Posts
    • 22Thanks
    hueknight1969
    excel help
    • #1
    • 8th Feb 18, 9:52 PM
    excel help 8th Feb 18 at 9:52 PM
    Hi

    Is there a way to get excell to look at a group of cells and tell you the frequency of all the values without typing a specfic range or exactly the value. eg
    1
    1
    2
    2
    2
    4
    6

    1=2 times; 2=3times; 4=1 times; 6=1 times

    I have 2800 entries to sort out, im sure this can be done (i have looked on google with no luck)

    thanks

    hugh
Page 1
    • Cornucopia
    • By Cornucopia 8th Feb 18, 10:02 PM
    • 10,015 Posts
    • 9,893 Thanks
    Cornucopia
    • #2
    • 8th Feb 18, 10:02 PM
    • #2
    • 8th Feb 18, 10:02 PM
    Yes, there's probably several ways to do it.

    I would do this:-

    - Copy the list of values
    - Sort the copied list in ascending order
    - Add a de-duplication formula alongside the copied list (see below)
    - Create a de-duplicated new list
    - Add a counting formula to the de-duplicated list

    Typical de-duplication formula for a vertical list:-

    in column B, add: =IF(A1=A2,"",A1) to every cell next to a source value in column A

    Typical counting formula for a vertical list:-

    =COUNTIF("=" & C1, A: A)
    Last edited by Cornucopia; 08-02-2018 at 10:21 PM.
    I'm a Board Guide on the Phones & TV, Techie Stuff, In My Home,
    The Money Savers Arms and Food Shopping boards. I'm a volunteer to help the boards run smoothly, and I can move and merge threads there. Any views (especially those on the UK TV Licence) are mine and not the official line of moneysavingexpert.com.

    Board guides are not moderators. If you spot an inappropriate or illegal post then please report it to forumteam@moneysavingexpert.com
    • WaywardDriver
    • By WaywardDriver 8th Feb 18, 10:16 PM
    • 108 Posts
    • 109 Thanks
    WaywardDriver
    • #3
    • 8th Feb 18, 10:16 PM
    • #3
    • 8th Feb 18, 10:16 PM
    Insert a heading (A say) in the row above the numbers and Insert a PivotTable, drag the heading to both the Rows and Values sections, click where it says Sum of A, click Value Field Settings and change to Count.
    • Tom99
    • By Tom99 9th Feb 18, 12:29 AM
    • 2,068 Posts
    • 1,392 Thanks
    Tom99
    • #4
    • 9th Feb 18, 12:29 AM
    • #4
    • 9th Feb 18, 12:29 AM
    Yes a PivotTable will do it.
    • stragglebod
    • By stragglebod 9th Feb 18, 2:10 PM
    • 114 Posts
    • 92 Thanks
    stragglebod
    • #5
    • 9th Feb 18, 2:10 PM
    • #5
    • 9th Feb 18, 2:10 PM
    +1 pivot table
    • rmg1
    • By rmg1 9th Feb 18, 2:28 PM
    • 2,912 Posts
    • 742 Thanks
    rmg1
    • #6
    • 9th Feb 18, 2:28 PM
    • #6
    • 9th Feb 18, 2:28 PM
    Just to add in my 2p-worth.
    A pivot table will certainly do what you want.
    If you want to re-use it with ranges of varying sizes, research dynamic data ranges and then attach the pivot-table to one of those.
    Flagellation, necrophilia and bestiality - Am I flogging a dead horse?

    Any posts are my opinion and only that. Please read at your own risk.
Welcome to our new Forum!

Our aim is to save you money quickly and easily. We hope you like it!

Forum Team Contact us

Live Stats

1,409Posts Today

7,862Users online

Martin's Twitter
  • It's the start of mini MSE's half term. In order to be the best daddy possible, Im stopping work and going off line? https://t.co/kwjvtd75YU

  • RT @shellsince1982: @MartinSLewis thanx to your email I have just saved myself £222 by taking a SIM only deal for £7.50 a month and keeping?

  • Today's Friday twitter poll: An important question, building on yesterday's important discussions: Which is the best bit of the pizza...

  • Follow Martin