+ Reply to Thread
Results 1 to 10 of 10

combine mid and max

  1. #1
    Forum Contributor
    Join Date
    09-24-2009
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    129

    combine mid and max

    example.xlsxColumn A (ticket numbers)

    DAS-011 E
    DAS-012 E
    DAS-013 E

    I want to look up the maxium ticket number in column A. so all i need is the formula to return the value "013". Can this be done?

    I tried creating column b using the MID formula =MID(A3,7,3) and then just doing a MAX formula but all it returns is a zero.

    anyone help me please?
    Last edited by keith6292; 10-09-2014 at 04:39 PM.

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: combine mid and max

    Try this array formula in B1

    =INDEX(A1:A3,MATCH("*"&MAX(--MID(A1:A3,FIND("-",A1:A3)+1,3))&"*",A1:A3,0))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    A
    B
    1
    DAS-011 E DAS-013 E
    2
    DAS-012 E
    3
    DAS-013 E
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Moderator 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
    28,419

    Re: combine mid and max

    One way as an array formula

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


    However a smarter option might be to use Data TextToColumns to split the number into its own column and then just use a standard MAX(A1:A3)
    Richard Buttrey

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

  4. #4
    Forum Contributor
    Join Date
    09-24-2009
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    129

    Re: combine mid and max

    ok that works. Is there anyway i can have that formula return what the next consecutive number would be? so the result would be "DAS-014 E"

  5. #5
    Forum Contributor
    Join Date
    09-24-2009
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    129

    Re: combine mid and max

    Quote Originally Posted by AlKey View Post
    Try this array formula in B1

    =INDEX(A1:A3,MATCH("*"&MAX(--MID(A1:A3,FIND("-",A1:A3)+1,3))&"*",A1:A3,0))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    A
    B
    1
    DAS-011 E DAS-013 E
    2
    DAS-012 E
    3
    DAS-013 E
    ok that works. Is there anyway i can have that formula return what the next consecutive number would be? so the result would be "DAS-014 E"

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: combine mid and max

    ="DAS-"&TEXT(MAX(VALUE(MID(A1:A3,5,3)+1)),"000")&" E"

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

  7. #7
    Forum Contributor
    Join Date
    09-24-2009
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    129

    Re: combine mid and max

    Quote Originally Posted by AlKey View Post
    ="DAS-"&TEXT(MAX(VALUE(MID(A1:A3,5,3)+1)),"000")&" E"

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    that works perfect as well. now if i could make it more complicated I have something else i would like to add.

    Column A

    DAS-011 E
    REQ-019 E
    DAS-013 E
    DAS-012 E
    REG-020 E

    Now i want to lookup just the DAS in column A and have it return the next consecutive number. so the result would be DAS-014 E

  8. #8
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    Re: combine mid and max

    Hi,

    same formula


    ="DAS-"&TEXT(MAX(VALUE(MID(A1:A3,5,3)+1))+1,"000")&" E"

    Hope it helps
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  9. #9
    Forum Contributor
    Join Date
    09-24-2009
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    129

    Re: combine mid and max

    Quote Originally Posted by canapone View Post
    Hi,

    same formula


    ="DAS-"&TEXT(MAX(VALUE(MID(A1:A3,5,3)+1))+1,"000")&" E"

    Hope it helps
    I am terrible at explaining things. can you please look at my example and let me know if this is possible.

    Thanks


    example.xlsx

  10. #10
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    Re: combine mid and max

    Hi,

    confirmed with control+shift+enter


    =VLOOKUP(I7,A6:B9,2,0)&"-"&TEXT(MAX(VALUE(RIGHT(D6:D17,3)+1)),"000")

    could do the trick.

    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] combine multi workbooks into one master workbook but I want to combine only sheet 3
    By Goodstart14 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-31-2013, 12:22 AM
  2. Combine Duplicate Rows and Combine Data in Rows
    By cherylmcgk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-08-2013, 12:04 PM
  3. [SOLVED] Using Combine macro to combine multiple worksheets - need to modify to paste formulas
    By DLSmith in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-26-2012, 09:07 AM
  4. How can i combine 2 row into 1?
    By bored7one4 in forum Excel General
    Replies: 4
    Last Post: 09-19-2006, 11:52 PM
  5. How do I combine IF and OR?
    By ana_15825 - ExcelForums.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM

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