Show me the nth (Index & Match or not?)
Results 1 to 4 of 4

Thread: Show me the nth (Index & Match or not?)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Show me the nth (Index & Match or not?)

    Need a solution to return the highest value of PO number issued.

    Our PO log has over 2500 entries at present including duplicate PO references on numerous jobs.
    Filtering is rather cumbersome with this many entries.

    Purchase order structure:
    Job Number PO Sequence# PO Requestor
    1878 - 25 - 200

    Index and match only gives me the first occurence.

    In the case of job number 1878TT, ultimately, the desired result would find the highest sequence number and
    concatenate (3) fields to generate the next PO number (i.e. 1878-26-232).

    Is this possible to accomplish with a formula or will it require macro/vba?

    Sample data below:

    Job Number PO Number
    1846TT 1846-01-288
    1846TT 1846-02-251
    1846TT 1846-02-251
    1846TT 1846-02-251
    1846TT 1846-02-251
    1871TT 1871-04-119
    1875TT 1875-01-251
    1878TT 1878-23-119
    TT1902 1902-04-251
    TT1904 1904-01-251
    18110TT 18110-03-WS
    18111TT 18111-04-WS
    1875TT 1875-02-251
    1878TT 1878-24-200
    1878TT 1878-25-200
    1899TT 1899-01-WS
    1899TT 1899-02-316

    Any suggestions for a resolution I truly would be grateful.

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,860
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Show me the nth (Index & Match or not?)

    Please explain the criteria for the highest sequence number and how it relates to creating the next number for a project. I don't see an obvious calculation based upon your example. How do you get from 200 to 232? And I notice some have letters for the third sequence. How are those handled. I think we need more understanding of how your PO numbers are created.
    Last edited by alansidman; Jul 23rd, 2019 at 06:13 PM.
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  3. #3
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,577
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Show me the nth (Index & Match or not?)

    something like this?

    basic version with 1 Max:

    Job Number Max
    1846TT 1846-02-251
    1871TT 1871-04-119
    1875TT 1875-02-251
    1878TT 1878-25-200
    TT1902 1902-04-251
    TT1904 1904-01-251
    18110TT 18110-03-WS
    18111TT 18111-04-WS
    1899TT 1899-02-316


    extended version with 3 Max (if exist)

    Job Number Max
    1846TT 1846-02-251
    1846TT 1846-01-288
    1871TT 1871-04-119
    1875TT 1875-02-251
    1875TT 1875-01-251
    1878TT 1878-25-200
    1878TT 1878-24-200
    1878TT 1878-23-119
    TT1902 1902-04-251
    TT1904 1904-01-251
    18110TT 18110-03-WS
    18111TT 18111-04-WS
    1899TT 1899-02-316
    1899TT 1899-01-WS


    if not, post representative example and expected result (link to the shared excel file is welcome)
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  4. #4
    New Member
    Join Date
    Mar 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Show me the nth (Index & Match or not?)

    A B C D E F G H I J K
    1 Job Number PO Number Request Date Vendor Person Requesting PO Person Writing PO Ship To Quantity Description PO Amount Vendor Invoice Number

    Row
    A B C D E
    1546 192145 192145-28-251 B Electric Mark Onslow
    1547 MB1923 1923-12-MB QCC Michael Barnes
    1548 TT1925 1925-00-232 Plumbing John Martin
    1549 TT1926 1926-01-232 Plumbing John Martin
    1550 TT1937 1937-01-232 Plumbing John Martin
    1551 WS1938 1938-02-WS B Electric Wayne Stanley
    1552 SR2133 2133-02-316 AHU Todd Bryant

    Row
    A B C D E F G H I J K
    1555 SR2193 2193-01-315 Plumbing Ricky Billings Our Company 1 14.54 S869625.001
    1556 SR2193 2193-01-315 Plumbing Ricky Billings Our Company 1 Credit Memo -3.02 S869622.001
    1557 SR2193 2193-01-315 Plumbing Ricky Billings Our Company 1 38.66 S869057.001

    Sorry for the tardy response, but end of the month billings and...
    Hiyas! And thanks for the interest.

    Sorry for not keeping KISS previously but here goes...

    Our PO#'s are:
    "Job Number" (Column A numeric only)
    "PO Sequence# (Column B, i.e. 01, 02, 03, etc.)"
    "Requestor" (Column E (drawn from hourly/salaried employee list)

    Therefore, in the case of "Row 1751", PO# in Column B should is "2425-01-315".


    Presently, this is how formula runs in “Column B”:

    =CONCATENATE(SUMPRODUCT(MID(0&$A1768, LARGE(INDEX(ISNUMBER(--MID($A1768, ROW(INDIRECT("1:"&LEN($A1768))), 1)) * ROW(INDIRECT("1:"&LEN($A1768))), 0), ROW(INDIRECT("1:"&LEN($A1768))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN($A1768)))/10),"-",INDEX('PO Log'!$B$2:$B1767,MATCH($A1768,'PO Log'!$A:$A,0)),"-",IFERROR(INDEX('Employee List'!$A:$A,MATCH($E1768,'Employee List'!$B:$B,0)),IF(LEN($E1768)-LEN(SUBSTITUTE($E1768," ",""))=0,LEFT($E1768,1),IF(LEN($E1768)-LEN(SUBSTITUTE($E1768," ",""))=1,LEFT($E1768,1)&MID($E1768,FIND(" ",$E1768)+1,1),LEFT($E1768,1)&MID($E1768,FIND(" ",$E1768)+1,1)&MID($E1768,FIND(" ",$E1768,FIND(" ",$E1768)+1)+1,1)))))

    The only variable that eludes me is trying to find the "max sequence number” (Column B, red text in formula) of the highest match from job number (Column A) irrespective of PO requestor (Column E) ...
    ofc, "+1"



    btw...
    There may be numerous instances of the same "PO Sequence#" due to multiple shipments (i.e. Column L), so I’m not sure any form of count will work.

    Reverting back to the KISS method, I concede helper columns are in order considering I will want to find the
    "PO Sequence#"in the “$B:$B” range.

    Is this possible?
    ...or no?
    Thanks for your insight.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •