+ Reply to Thread
Results 1 to 5 of 5

List of Names, Dates, Durations ...

  1. #1
    Iain Halder
    Guest

    List of Names, Dates, Durations ...

    Hi,

    I hope someone can help me here ...

    I have a list of names well over 2000 and growing listed in one
    column. Adjacent to this column is a list of admission dates and also
    a third column of discharge dates.


    NAME ADMIT DATE DISCHARGE DATE
    Joe Bloggs 22-Jan-05 27-Jan-05

    I need to be able to get Excel to count this entire list of names but
    identify each name and how often it appears and when.

    I work with the NHS and my reason for asking this question is I need
    to be able ...

    * to identify frequent attenders to our services.
    * to see how many people re-refer to our services within 28 days of
    being discharged
    * how many are also discharged within 28 days of admission and
    * the duration of their stay.

    I'm thinking SUMPRODUCT will do the job with some arithmetic related
    to counting length of stay.

    However where I am stuck is in the first part of being able to get the
    computer to run through this initial list counting the names and how
    often they appear. The approach I'm adopting at the moment is a manual
    one of getting excel to sort the names alphabetically and physically
    counting them and then checking the dates visually. This is not really
    practical though, as you could imagine.

    Hope someone here can be of help or refer me even to an online source
    that can.

    Thank you guys in advance.

    Iain Halder
    Rescued Cats & Kittens Needing Homes
    >'o'< www.celiahammond.org >'o'<
    >'o'< www.cat77.org.uk >'o'<


  2. #2
    Biff
    Guest

    Re: List of Names, Dates, Durations ...

    Hi!

    A pivot table would probably be ideal for this but since I hate pivot tables
    I can't really recommend them. <vbg>

    OTOH, (and this is what I would do) all you need to do is to extract the
    unique names to another location then you can do any number analytical
    operations on the data.

    For instance, you can use an advanced filter to extract the uniques and copy
    them to another location. Assume that new location is sheet2 A1:A500. The
    original data is on sheet1 A1:A2000.

    To get the count of each name on sheet2 B1 enter this formula:

    =COUNTIF(Sheet1!A$1:A$2000,A1)

    Double click the fill handle to copy this formula down to A500.

    Biff

    "Iain Halder" <[email protected]> wrote in message
    news:[email protected]...
    > Hi,
    >
    > I hope someone can help me here ...
    >
    > I have a list of names well over 2000 and growing listed in one
    > column. Adjacent to this column is a list of admission dates and also
    > a third column of discharge dates.
    >
    >
    > NAME ADMIT DATE DISCHARGE DATE
    > Joe Bloggs 22-Jan-05 27-Jan-05
    >
    > I need to be able to get Excel to count this entire list of names but
    > identify each name and how often it appears and when.
    >
    > I work with the NHS and my reason for asking this question is I need
    > to be able ...
    >
    > * to identify frequent attenders to our services.
    > * to see how many people re-refer to our services within 28 days of
    > being discharged
    > * how many are also discharged within 28 days of admission and
    > * the duration of their stay.
    >
    > I'm thinking SUMPRODUCT will do the job with some arithmetic related
    > to counting length of stay.
    >
    > However where I am stuck is in the first part of being able to get the
    > computer to run through this initial list counting the names and how
    > often they appear. The approach I'm adopting at the moment is a manual
    > one of getting excel to sort the names alphabetically and physically
    > counting them and then checking the dates visually. This is not really
    > practical though, as you could imagine.
    >
    > Hope someone here can be of help or refer me even to an online source
    > that can.
    >
    > Thank you guys in advance.
    >
    > Iain Halder
    > Rescued Cats & Kittens Needing Homes
    > >'o'< www.celiahammond.org >'o'<
    > >'o'< www.cat77.org.uk >'o'<




  3. #3
    tjtjjtjt
    Guest

    RE: List of Names, Dates, Durations ...

    From your description, I would suggest checking out PivotTables.
    Here is one place to start.
    http://peltiertech.com/Excel/Pivots/pivotstart.htm

    --
    tj


    "Iain Halder" wrote:

    > Hi,
    >
    > I hope someone can help me here ...
    >
    > I have a list of names well over 2000 and growing listed in one
    > column. Adjacent to this column is a list of admission dates and also
    > a third column of discharge dates.
    >
    >
    > NAME ADMIT DATE DISCHARGE DATE
    > Joe Bloggs 22-Jan-05 27-Jan-05
    >
    > I need to be able to get Excel to count this entire list of names but
    > identify each name and how often it appears and when.
    >
    > I work with the NHS and my reason for asking this question is I need
    > to be able ...
    >
    > * to identify frequent attenders to our services.
    > * to see how many people re-refer to our services within 28 days of
    > being discharged
    > * how many are also discharged within 28 days of admission and
    > * the duration of their stay.
    >
    > I'm thinking SUMPRODUCT will do the job with some arithmetic related
    > to counting length of stay.
    >
    > However where I am stuck is in the first part of being able to get the
    > computer to run through this initial list counting the names and how
    > often they appear. The approach I'm adopting at the moment is a manual
    > one of getting excel to sort the names alphabetically and physically
    > counting them and then checking the dates visually. This is not really
    > practical though, as you could imagine.
    >
    > Hope someone here can be of help or refer me even to an online source
    > that can.
    >
    > Thank you guys in advance.
    >
    > Iain Halder
    > Rescued Cats & Kittens Needing Homes
    > >'o'< www.celiahammond.org >'o'<
    > >'o'< www.cat77.org.uk >'o'<

    >


  4. #4
    Iain Halder
    Guest

    Re: List of Names, Dates, Durations ...

    Hi Guys!

    Thanks for the quick replies and I'll try the both of these out. I'm
    not really that up on pivot tables but I'll give it a shot.

    Thanks Again for the help all you guys offer on this newsgroup!

    Iain

    On Wed, 28 Sep 2005 23:51:41 +0000 (UTC), Iain Halder
    <[email protected]> wrote:

    >Hi,
    >
    >I hope someone can help me here ...
    >
    >I have a list of names well over 2000 and growing listed in one
    >column. Adjacent to this column is a list of admission dates and also
    >a third column of discharge dates.
    >
    >
    >NAME ADMIT DATE DISCHARGE DATE
    >Joe Bloggs 22-Jan-05 27-Jan-05
    >
    >I need to be able to get Excel to count this entire list of names but
    >identify each name and how often it appears and when.
    >
    >I work with the NHS and my reason for asking this question is I need
    >to be able ...
    >
    >* to identify frequent attenders to our services.
    >* to see how many people re-refer to our services within 28 days of
    >being discharged
    >* how many are also discharged within 28 days of admission and
    >* the duration of their stay.
    >
    >I'm thinking SUMPRODUCT will do the job with some arithmetic related
    >to counting length of stay.
    >
    >However where I am stuck is in the first part of being able to get the
    >computer to run through this initial list counting the names and how
    >often they appear. The approach I'm adopting at the moment is a manual
    >one of getting excel to sort the names alphabetically and physically
    >counting them and then checking the dates visually. This is not really
    >practical though, as you could imagine.
    >
    >Hope someone here can be of help or refer me even to an online source
    >that can.
    >
    >Thank you guys in advance.
    >
    >Iain Halder
    >Rescued Cats & Kittens Needing Homes
    > >'o'< www.celiahammond.org >'o'<
    > >'o'< www.cat77.org.uk >'o'<


    >o< Rescued Cats & Kittens Needing Homes >o<
    >o< www.celiahammond.org >o<
    >o< www.cat77.org.uk >o<


  5. #5
    Roger Govier
    Guest

    Re: List of Names, Dates, Durations ...

    Hi Iain

    In spite of Biff's aversion to Pivot Tables <vbg> I would recommend you take
    the trouble to learn how to use them.

    The small effort in learning will be well rewarded with the ease and speed
    with which you can perform all sorts of anlyses without writing a single
    formula yourself.

    Biff is so good at formulas and loves the challenge of writing new ones, he
    doesn't need them, but mere mortals like you and I do. <bg>


    Regards

    Roger Govier


    Iain Halder wrote:
    > Hi Guys!
    >
    > Thanks for the quick replies and I'll try the both of these out. I'm
    > not really that up on pivot tables but I'll give it a shot.
    >
    > Thanks Again for the help all you guys offer on this newsgroup!
    >
    > Iain
    >
    > On Wed, 28 Sep 2005 23:51:41 +0000 (UTC), Iain Halder
    > <[email protected]> wrote:
    >
    >
    >>Hi,
    >>
    >>I hope someone can help me here ...
    >>
    >>I have a list of names well over 2000 and growing listed in one
    >>column. Adjacent to this column is a list of admission dates and also
    >>a third column of discharge dates.
    >>
    >>
    >>NAME ADMIT DATE DISCHARGE DATE
    >>Joe Bloggs 22-Jan-05 27-Jan-05
    >>
    >>I need to be able to get Excel to count this entire list of names but
    >>identify each name and how often it appears and when.
    >>
    >>I work with the NHS and my reason for asking this question is I need
    >>to be able ...
    >>
    >>* to identify frequent attenders to our services.
    >>* to see how many people re-refer to our services within 28 days of
    >>being discharged
    >>* how many are also discharged within 28 days of admission and
    >>* the duration of their stay.
    >>
    >>I'm thinking SUMPRODUCT will do the job with some arithmetic related
    >>to counting length of stay.
    >>
    >>However where I am stuck is in the first part of being able to get the
    >>computer to run through this initial list counting the names and how
    >>often they appear. The approach I'm adopting at the moment is a manual
    >>one of getting excel to sort the names alphabetically and physically
    >>counting them and then checking the dates visually. This is not really
    >>practical though, as you could imagine.
    >>
    >>Hope someone here can be of help or refer me even to an online source
    >>that can.
    >>
    >>Thank you guys in advance.
    >>
    >>Iain Halder
    >>Rescued Cats & Kittens Needing Homes
    >> >'o'< www.celiahammond.org >'o'<
    >> >'o'< www.cat77.org.uk >'o'<

    >
    >
    >>o< Rescued Cats & Kittens Needing Homes >o<

    >
    > >o< www.celiahammond.org >o<
    > >o< www.cat77.org.uk >o<


+ 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