+ Reply to Thread
Results 1 to 12 of 12

Return latest Alpha / Numeric / Numeric Alpha Revision

  1. #1
    Registered User
    Join Date
    02-06-2014
    Location
    Regina, Saskatchewan
    MS-Off Ver
    Excel 2007
    Posts
    12

    Return latest Alpha / Numeric / Numeric Alpha Revision

    Hello,

    I'm trying to find a formula that will return the latest revision from a range A1:A7. This range could include values A, B, 0, 1, 1A, 1B, 2, etc. and in the order listed would be from oldest revision to the latest revision. Using the values provided, the formula would return "2". If my latest revision was "1A", then the formula would return "1A". The range A1:A7 is fixed so if there were more than 7 revisions, the 8th revision would overwrite the 1st revision.

    Thank you for any help in advance.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Return latest Alpha / Numeric / Numeric Alpha Revision

    If you only have 7 cells and they will all be filled, why not just =A7?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Return latest Alpha / Numeric / Numeric Alpha Revision

    Try this

    =INDEX(A1:A7,MAX(LOOKUP(99,A1:A7, ROW(A1:A7)), LOOKUP("ZZZ", A1:A7, ROW(A1:A7))))
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    02-06-2014
    Location
    Regina, Saskatchewan
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Return latest Alpha / Numeric / Numeric Alpha Revision

    The cells will not always be full and A7 would not necessarily be the latest revision. Any of the cells could contain the latest revision.

  5. #5
    Registered User
    Join Date
    02-06-2014
    Location
    Regina, Saskatchewan
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Return latest Alpha / Numeric / Numeric Alpha Revision

    Please see the attached image file (couldn't attached excel for some reason) for examples and expected results for each situation. Revision Example.jpg

  6. #6
    Registered User
    Join Date
    02-06-2014
    Location
    Regina, Saskatchewan
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Return latest Alpha / Numeric / Numeric Alpha Revision

    Quote Originally Posted by ChemistB View Post
    Try this

    =INDEX(A1:A7,MAX(LOOKUP(99,A1:A7, ROW(A1:A7)), LOOKUP("ZZZ", A1:A7, ROW(A1:A7))))
    This formula partially worked. It returned a "Not Available" error if all values were all numeric and this is a possibility. If the revisions get larger and move back to the top of the range (cell A1) then the formula will only return the largest value in cell A7. I posted a picture of some possibilities for better clarity on the problem.

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Return latest Alpha / Numeric / Numeric Alpha Revision

    Try this

    =INDEX(A3:A10,MAX(IFERROR(LOOKUP(99,A3:A10, ROW(A3:A10)-2), LOOKUP("ZZZ",A3:A10, ROW(A3:A10)-2)),IFERROR(LOOKUP("ZZZ", A3:A10, ROW(A3:A10)-2),0)))

  8. #8
    Registered User
    Join Date
    02-06-2014
    Location
    Regina, Saskatchewan
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Return latest Alpha / Numeric / Numeric Alpha Revision

    This formula works great for the first 4 examples but does not return the correct result on Example 5. It is returning Revision "3" rather than Revision "4" (referring to the image of examples that I posted earlier).

  9. #9
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Return latest Alpha / Numeric / Numeric Alpha Revision

    My mistake, I thought you were looking for the last entry in the range. My formula will not do what you want. How do you define your hierarchy?

    So it looks like 1A is greater than a numerical value. Is 1b greater than 2a? Is A greater than a numerical value?

  10. #10
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Return latest Alpha / Numeric / Numeric Alpha Revision

    Okay, If I am understanding correctly, try this one

    =IFERROR(INDEX(A3:A9,LOOKUP(2,1/SEARCH(MAX(A3:A9),A3:A9), ROW(A3:A9)-2)), LOOKUP("ZZZ", A3:A9))

  11. #11
    Registered User
    Join Date
    02-06-2014
    Location
    Regina, Saskatchewan
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Return latest Alpha / Numeric / Numeric Alpha Revision

    Quote Originally Posted by ChemistB View Post
    Okay, If I am understanding correctly, try this one

    =IFERROR(INDEX(A3:A9,LOOKUP(2,1/SEARCH(MAX(A3:A9),A3:A9), ROW(A3:A9)-2)), LOOKUP("ZZZ", A3:A9))

    YES! This works, Thank you!

  12. #12
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Return latest Alpha / Numeric / Numeric Alpha Revision

    Glad to help

+ 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. Replies: 2
    Last Post: 03-17-2016, 08:55 AM
  2. Replies: 9
    Last Post: 11-18-2013, 07:40 AM
  3. Return Location of Alpha/Numeric String
    By Greg777 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-27-2011, 08:18 PM
  4. Macro to delete alpha and alpha numeric values
    By vijanand1279 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-20-2011, 09:13 AM
  5. [SOLVED] In alpha-numeric text data replace alpha
    By manharji in forum Excel General
    Replies: 3
    Last Post: 07-26-2010, 07:20 PM
  6. Replies: 2
    Last Post: 06-18-2010, 05:10 PM
  7. Replies: 2
    Last Post: 05-26-2005, 05:15 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