+ Reply to Thread
Results 1 to 7 of 7

Count of Names in Vertical List(according to criteria), then arrange Horizontally...

  1. #1
    Registered User
    Join Date
    05-19-2013
    Location
    Chester, England
    MS-Off Ver
    Excel 2013
    Posts
    40

    Count of Names in Vertical List(according to criteria), then arrange Horizontally...

    Hello,
    I have a list of absentees pasted into a worksheet, as shown in the attached example, which lists each absence/absentee - with multiple repeated names/instances.
    I want to enter, into the column indicated, the number of unique staff absent for each department ie removing the duplicates.
    If possible, I'd also like to list the names vertically in the end columns - to give a summary of those staff absent for each month.
    Any ideas appreciated...
    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Count of Names in Vertical List(according to criteria), then arrange Horizontally...

    Hi,

    If the whole table is a list of absences then aren't the column I totals the same as column F?

    It would help if you were to add manually a few examples of the results you expect. However I can't help thinking this can't be achieved in more elegant,efficient and useful way with a Pivot Table. If you don't have Excel 2013 as your profile suggests then you may need to add a helper column to your data but that's a very small price to pay for a much more powerful analysis tool.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    05-19-2013
    Location
    Chester, England
    MS-Off Ver
    Excel 2013
    Posts
    40

    Re: Count of Names in Vertical List(according to criteria), then arrange Horizontally...

    Hi Richard,
    The staff numbers in Column F are the numbers in each department rather than the numbers absent for that month.
    In Column I, I want to count the number of staff who were absent for each department, excluding duplicates, rather than the number of instances of absence(that's calculated in another column).
    This particular sheet is also just a summary from the original list - I use pivots elsewhere in the workbook but they can't populate my summary table in the way I want.
    Thanks for your suggestions.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,048

    Re: Count of Names in Vertical List(according to criteria), then arrange Horizontally...

    I would suggest the best and most efficient approach is a Pivot Table. It won't be exactly the layout you have specified but it would give the information you require.


    F
    G
    H
    I
    J
    K
    L
    M
    N
    13
    14
    Count of REASON
    Column Labels
    15
    Row Labels
    A/L
    Appointment
    Compassionate Leave
    Illness
    Meeting
    Personal
    Training
    Grand Total
    16
    ACCOUNTS
    17
    A JONES
    1
    2
    3
    18
    D HACKER
    2
    1
    3
    19
    J PORTEOUS
    1
    2
    2
    5
    20
    ACCOUNTS Total
    3
    1
    2
    5
    11
    21
    Administration
    22
    R RAINEY
    1
    1
    1
    3
    23
    S CORDER
    1
    2
    3
    24
    Administration Total
    2
    1
    1
    2
    6
    25
    CLEAN
    26
    O LEMB
    1
    2
    3
    27
    T MCGRADY
    1
    1
    2
    4
    28
    CLEAN Total
    1
    1
    3
    2
    7
    29
    HELPD
    30
    B FENNELL
    1
    2
    3
    31
    D HACKER
    1
    1
    32
    HELPD Total
    1
    2
    1
    4
    33
    INDOOR
    34
    A JONES
    1
    2
    3
    35
    K BRYAN
    1
    1
    1
    3
    36
    INDOOR Total
    2
    1
    2
    1
    6
    37
    IT
    38
    D HACKER
    1
    1
    2
    39
    E SMITH
    1
    1
    2
    40
    M MARTHER
    2
    2
    41
    IT Total
    1
    1
    2
    2
    6
    42
    SLT
    43
    F HOPE
    2
    3
    5
    44
    G S STRAVER
    1
    1
    1
    3
    45
    SLT Total
    3
    1
    3
    1
    8
    46
    Grand Total
    10
    3
    8
    11
    12
    1
    3
    48
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Count of Names in Vertical List(according to criteria), then arrange Horizontally...

    Hi,

    With a helper column E
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then in J2 (formerly I before the new helper column) copied down

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


    When it comes to the names then there's probably some complex compound formula that can identify the names but as both Trevor and I have remarked it does seem that a Pivot Table is perhaps the best way forward.

  6. #6
    Registered User
    Join Date
    05-19-2013
    Location
    Chester, England
    MS-Off Ver
    Excel 2013
    Posts
    40

    Re: Count of Names in Vertical List(according to criteria), then arrange Horizontally...

    Thanks Trevor,
    May have to stick with the Pivot - but still would love to find a formula, particularly for Column I, for my worksheet.
    Think I can possibly do the horizontal groups at the right hand of the sheet - did this a couple of years ago on another project but struggling to remember.
    Regards.

  7. #7
    Registered User
    Join Date
    05-19-2013
    Location
    Chester, England
    MS-Off Ver
    Excel 2013
    Posts
    40

    Re: Count of Names in Vertical List(according to criteria), then arrange Horizontally...

    Thanks again Richard.
    Works perfectly...the horizontal names isn't something hugely important - but remain curious as to how it might be achieved!
    Kind regards.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] autofill vertical list horizontally with 2 blank cells in-between each list item
    By Phily915 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-14-2014, 08:15 PM
  2. Vertical List to Sort Horizontally on another sheet
    By ncurran217 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-04-2013, 02:01 PM
  3. Replies: 4
    Last Post: 03-11-2013, 06:43 PM
  4. Arrange List of Names in range table
    By bopsgtir in forum Excel General
    Replies: 5
    Last Post: 12-18-2010, 06:13 AM
  5. List track names horizontally
    By CobraLAD in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-25-2010, 07:59 AM
  6. Replies: 3
    Last Post: 12-14-2006, 01:58 PM
  7. [SOLVED] Count Unique Names in list w/ Additional Criteria?
    By Nodak in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-25-2005, 08:06 PM

Tags for this Thread

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