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

1. ## 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. ## 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.

3. ## 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)

4. ## 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.

## User Tag List

#### Posting Permissions

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