Here is another way. This will require two helper columns
in B2 and copy down
Formula:
=LEFT(A2,FIND("-",A2)-1)
in C2 and copy down
Formula:
=--SUBSTITUTE(A2,LEFT(A2,FIND("-",A2)),"")
in D2 and copy down (to get unique PO numbers)
Formula:
=IFERROR(INDEX(B$2:B$8,MATCH(0,INDEX(COUNTIF(D$1:D1,B$2:B$8),,),)),"")
In E2 and copy down ( for results)
Formula:
=MAX(INDEX((B$2:B$8=D2)*(C$2:C$8),))
v |
A |
B |
C |
D |
E |
1 |
Data |
Helper 1 |
Helper 2 |
Results |
Vaues |
2 |
P0001-1 |
P0001 |
1 |
P0001 |
2 |
3 |
P0001-2 |
P0001 |
2 |
P0002 |
1 |
4 |
P0002-1 |
P0002 |
1 |
P0003 |
1 |
5 |
P0003-1 |
P0003 |
1 |
P0004 |
1 |
6 |
P0004-1 |
P0004 |
1 |
P0005 |
2 |
7 |
P0005-1 |
P0005 |
1 |
|
|
8 |
P0005-2 |
P0005 |
2 |
|
|
Bookmarks