Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel counting formula needed. (2000)

    Hi,
    This is probably a pretty simple one, but one I'm stuck on.
    I have a report that is generated in an excel spreadsheet. I need to count the names (regardless of whether their repeated) for each entry.
    However the names appear to be generated in the same cell (see attached image), and I'm stuck as to how to count them seperately.
    Apologies if this has been asked before.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel counting formula needed. (2000)

    What exactly do you want to count? Does "EKE Matthew" count as 2, or "EKE Matthew James" as 1 and "EKE Matthew" as 1? Or something else?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel counting formula needed. (2000)

    Its literally every single name. So with regards to EKE this would be two names.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel counting formula needed. (2000)

    You state that "I have a report that is generated". Would it be possible to generate the report in such a way that each name is in its own cell?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel counting formula needed. (2000)

    No, I'm afraid not. The report is generated from a web based system that runs on a server, so we have no control over its production.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel counting formula needed. (2000)

    Could you attach a small sample file? Modify names to protect the guilty, if necessary.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel counting formula needed. (2000)

    Yep, as attached. Names duly modified...

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel counting formula needed. (2000)

    I have attached a text file with two macros. Copy them into a module.

    The first macro, SplitRows, splits the rows with multiple names, so that each row contains only a single name.
    The second macro, CreateCount, creates a pivot table in a new worksheet with a count of unique names. It MUST be run after the first one.
    (In your sample file, there are no duplicate names, so each name has frequency 1)

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel counting formula needed. (2000)

    Sorry, I'm overly experienced with Excel. Is it just a case of pressing Alt + F11 and then pasting this into the VBA to create a macro?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel counting formula needed. (2000)

    Why ask if you're overly experienced? <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    It's the same as in Access:
    - Activate the Visual Basic Editor (Alt+F11)
    - Select Insert | Module.
    - Paste the code into the module window.

    To run a macro from Excel:
    - Select Tools | Macro | Macros... or press Alt+F8
    - Select the name of the macro in the list.
    - Click Run.

    To run a macro from the Visual Basic Editor:
    - Click anywhere in the macro (between Sub and End Sub).
    - Select Run | Run Sub or press F5.

  11. #11
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel counting formula needed. (2000)

    Thanks Hans, works a treat. You star!!!! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

Posting Permissions

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