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

#### CrossedAndPainless

##### New Member
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

<tbody>
</tbody>

Any suggestions for a resolution I truly would be grateful.

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### alansidman

##### Well-known Member
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:

#### sandy666

##### Well-known Member
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)

#### CrossedAndPainless

##### New Member
ABCDEFGHIJK
1Job NumberPO NumberRequest DateVendorPerson Requesting POPerson Writing POShip ToQuantityDescriptionPO AmountVendor Invoice Number

<tbody>
</tbody>

Row
ABCDE
1546192145192145-28-251B ElectricMark Onslow
1547MB19231923-12-MBQCCMichael Barnes
1548TT19251925-00-232PlumbingJohn Martin
1549TT19261926-01-232PlumbingJohn Martin
1550TT19371937-01-232PlumbingJohn Martin
1551WS19381938-02-WSB ElectricWayne Stanley
1552SR21332133-02-316AHUTodd Bryant

<tbody>
</tbody>

Row
ABCDEFGHIJK
1555SR21932193-01-315PlumbingRicky BillingsOur Company114.54S869625.001
1556SR21932193-01-315PlumbingRicky BillingsOur Company1Credit Memo-3.02S869622.001
1557SR21932193-01-315PlumbingRicky BillingsOur Company138.66S869057.001

<tbody>
</tbody>

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?

1,102,645
Messages
5,488,077
Members
407,623
Latest member
Deigs

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...