+ Reply to Thread
Results 1 to 8 of 8

Numerical Incident Order based on Department Name

  1. 01-23-2017, 11:00 PM #1
    Anarchus
    Anarchus is offline
    Forum Contributor
    Join Date
    10-03-2016
    Location
    Sydney, Australia
    MS-Off Ver
    MS 365
    Posts
    128

    Numerical Incident Order based on Department Name

    Hi there,

    Not sure what forum this would be most suitable for.

    I have an incident register at work for the different departments I look after. I would like a number recorded for each department but each department have a different record, not consecutive. Not sure how to explain this exactly.

    I have a "Mail" department and a "Courier" department. I want it to be numbered in numerical order based on the department name.

    I've attached a workbook to show what I want it to do. I know I can have each department on a different worksheet but I need it all to be on one.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-23-2017, 11:17 PM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,055

    Re: Numerical Incident Order based on Department Name

    Maybe like this:

    =VLOOKUP(B2,$J$1:$K$3,2,FALSE)&TEXT(COUNTIF($B$2:B2,B2),"0000")

    with a table of data in J1-K3
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 01-23-2017, 11:25 PM #3
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,055

    Re: Numerical Incident Order based on Department Name

    Or this (no data table needed) in A2, copied down:

    ="INC-"&UPPER(LEFT(B2,3)&"-")&TEXT(COUNTIF($B$2:B2,B2),"0000")
    Reply With QuoteRegister To Reply
  4. 01-23-2017, 11:31 PM #4
    Anarchus
    Anarchus is offline
    Forum Contributor
    Join Date
    10-03-2016
    Location
    Sydney, Australia
    MS-Off Ver
    MS 365
    Posts
    128

    Re: Numerical Incident Order based on Department Name

    Thanks Glenn Kennedy, exactly what I wanted!
    Reply With QuoteRegister To Reply
  5. 01-23-2017, 11:35 PM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,055

    Re: Numerical Incident Order based on Department Name

    You're welcome.
    Reply With QuoteRegister To Reply
  6. 01-23-2017, 11:39 PM #6
    Anarchus
    Anarchus is offline
    Forum Contributor
    Join Date
    10-03-2016
    Location
    Sydney, Australia
    MS-Off Ver
    MS 365
    Posts
    128

    Re: Numerical Incident Order based on Department Name

    Sorry, one last question. How can I get the cell to appear blank instead of "#NA"?
    Reply With QuoteRegister To Reply
  7. 01-24-2017, 12:02 AM #7
    Anarchus
    Anarchus is offline
    Forum Contributor
    Join Date
    10-03-2016
    Location
    Sydney, Australia
    MS-Off Ver
    MS 365
    Posts
    128

    Re: Numerical Incident Order based on Department Name

    Nevermind, I used the second formula and put it in an "IF" statement. All working now, thanks again
    Reply With QuoteRegister To Reply
  8. 01-24-2017, 12:11 AM #8
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Numerical Incident Order based on Department Name

    Anarchus you could also wrap Glenn's formula in an IFERROR function like this:

    =IFERROR("INC-"&UPPER(LEFT(B2,3))&"-"&TEXT(COUNTIF($B$2:B2,B2),"0000"),"")
    Dave
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to calculate no. of days from last incident? Multiple stores with > incident dates
    By AnaM21 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-26-2014, 09:26 PM
  2. [SOLVED] Re - Number in numerical order
    By WCE_123 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-17-2013, 11:09 AM
  3. Macro to rearrange cells based on numerical order
    By Mbutler132000 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-18-2010, 11:02 AM
  4. Display Results In Numerical Order
    By Wanabe in forum Excel General
    Replies: 4
    Last Post: 07-26-2010, 05:49 PM
  5. Numerical order
    By leighmills33 in forum Excel General
    Replies: 3
    Last Post: 03-15-2010, 09:06 AM
  6. Insert row in numerical order
    By itsunclebill in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-23-2010, 12:10 AM
  7. Sum values based on department.
    By excelkeechak in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-30-2010, 07:47 AM
  8. How do I put my spreadsheet in numerical order?
    By mcall in forum Excel General
    Replies: 1
    Last Post: 04-21-2006, 02:35 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1