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

CrossedAndPainless · Jul 23, 2019

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.

alansidman · Jul 23, 2019

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.

sandy666 · Jul 23, 2019

something like this?

basic version with 1 Max:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Job Number[/td][td=bgcolor:#70AD47]Max[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]1846TT[/td][td=bgcolor:#E2EFDA]1846-02-251[/td][/tr]

[tr=bgcolor:#FFFFFF][td]1871TT[/td][td]1871-04-119[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]1875TT[/td][td=bgcolor:#E2EFDA]1875-02-251[/td][/tr]

[tr=bgcolor:#FFFFFF][td]1878TT[/td][td]1878-25-200[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]TT1902[/td][td=bgcolor:#E2EFDA]1902-04-251[/td][/tr]

[tr=bgcolor:#FFFFFF][td]TT1904[/td][td]1904-01-251[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]18110TT[/td][td=bgcolor:#E2EFDA]18110-03-WS[/td][/tr]

[tr=bgcolor:#FFFFFF][td]18111TT[/td][td]18111-04-WS[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]1899TT[/td][td=bgcolor:#E2EFDA]1899-02-316[/td][/tr]
[/table]

extended version with 3 Max (if exist)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Job Number[/td][td=bgcolor:#70AD47]Max[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]1846TT[/td][td=bgcolor:#E2EFDA]1846-02-251[/td][/tr]

[tr=bgcolor:#FFFFFF][td]1846TT[/td][td]1846-01-288[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]1871TT[/td][td=bgcolor:#E2EFDA]1871-04-119[/td][/tr]

[tr=bgcolor:#FFFFFF][td]1875TT[/td][td]1875-02-251[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]1875TT[/td][td=bgcolor:#E2EFDA]1875-01-251[/td][/tr]

[tr=bgcolor:#FFFFFF][td]1878TT[/td][td]1878-25-200[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]1878TT[/td][td=bgcolor:#E2EFDA]1878-24-200[/td][/tr]

[tr=bgcolor:#FFFFFF][td]1878TT[/td][td]1878-23-119[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]TT1902[/td][td=bgcolor:#E2EFDA]1902-04-251[/td][/tr]

[tr=bgcolor:#FFFFFF][td]TT1904[/td][td]1904-01-251[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]18110TT[/td][td=bgcolor:#E2EFDA]18110-03-WS[/td][/tr]

[tr=bgcolor:#FFFFFF][td]18111TT[/td][td]18111-04-WS[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]1899TT[/td][td=bgcolor:#E2EFDA]1899-02-316[/td][/tr]

[tr=bgcolor:#FFFFFF][td]1899TT[/td][td]1899-01-WS[/td][/tr]
[/table]

if not, post representative example and expected result (link to the shared excel file is welcome)

CrossedAndPainless · Jul 31, 2019

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

<tbody>
[TD="align: center"]1[/TD]

</tbody>

Row

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

<tbody>
[TD="align: center"]1546[/TD]
[TD="align: right"]192145[/TD]

[TD="align: right"][/TD]

[TD="align: center"]1547[/TD]

[TD="align: right"][/TD]

[TD="align: center"]1548[/TD]

[TD="align: right"][/TD]

[TD="align: center"]1549[/TD]

[TD="align: right"][/TD]

[TD="align: center"]1550[/TD]

[TD="align: right"][/TD]

[TD="align: center"]1551[/TD]

[TD="align: right"][/TD]

[TD="align: center"]1552[/TD]

[TD="align: right"][/TD]

</tbody>

Row

	A	B	C	D	E	F
SR2193	2193-01-315	Plumbing	Ricky Billings	Our Company	S869625.001
SR2193	2193-01-315	Plumbing	Ricky Billings	Our Company	Credit Memo	S869622.001
SR2193	2193-01-315	Plumbing	Ricky Billings	Our Company	S869057.001

<tbody>
[TD="align: center"]1555[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]14.54[/TD]

[TD="align: center"]1556[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]-3.02[/TD]

[TD="align: center"]1557[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]38.66[/TD]

</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?
Thanks for your insight.

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

CrossedAndPainless

New Member

Excel Facts

alansidman

Well-known Member

sandy666

Banned - Rules violations

CrossedAndPainless

New Member

Similar threads

Forum statistics

Share this page

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

CrossedAndPainless

New Member

Excel Facts

alansidman

Well-known Member

sandy666

Banned - Rules violations

CrossedAndPainless

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock