Forumla/ LookUp to find preceding ID

[AFGPB]

Hello everybody ,

I got a little excel task to do, however I really cannot get my head around how to solve it, so I hope you guys can help me out.

The problem:
I have a list of contracts with contract IDs in column A. However, not all of the contracts are completely different ones. Some of them just got replaced or extended by a "new" contract, in which case the ID gets additional digits or letters, however the original serial number is always the same.

My goal:
I have been asked to find out for each contracts if there is a preceding one. Meaning if a contract has been replaced or extended by a new one then I want to have the number of the preceding contract in columns C.
There can also be the case a contract has 2 additional extensions, there has been added even more digits/ letters, but I also want to see the preceding one.

I hope I explained myself correctly.
here is also a small sample which hopefully helps to understand the concept. https://ibb.co/jaNfJG

I am looking forward to your answers and thanks a lot already.
If you have any questions, please let me know.

Thank you!
AF

 

[AlanY]

see if this works for you

Excel 2013/2016
	A	B
1	ID	Preceding
2	AG0002/TS2-KK
3	AG0002/620AGAY
4	AG018-1
5	AG019-6
6	AG019-7	AG019-6
7	AG022-4
8	AG022-4A01	AG022-4
9	AG022-4A02	AG022-4A01
10	AG022-4A03	AG022-4A02
11	AG023-1
Sheet2

Cell Formulas
Range	Formula
B2	=IF(ISERROR(MATCH(TRIM(LEFT(SUBSTITUTE(A2,"-",REPT(" ",500)),500))&"*",$A1:A$1,0)),"",A1)

 

[AFGPB]

Hello AlanY,


Incredible, this works really wonderful. Thank you so much!!!
Unfortunately they added one more complication to the task.


Some of the contracts are called XXXXX-XE
Those contracts are separate contracts and do not relate to the one with XXXXX even though they have the same main serial number.
Other contracts, however like XXXXX-XA and XXXXX-XR do relate, so for those the formula works perfectly.
My questions is if it is possible to handle this exceptions.




[TABLE="width: 611"]
<tbody>[TR]
[TD="colspan: 2"]NOW[/TD]
[TD="colspan: 2"]HOW IT SHOULD BE[/TD]
[/TR]
[TR]
[TD]AG023-1[/TD]
[TD][/TD]
[TD]AG023-1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AG023-9[/TD]
[TD]AG023-1[/TD]
[TD]AG023-9[/TD]
[TD]AG023-1[/TD]
[/TR]
[TR]
[TD]AG023-9E01[/TD]
[TD]AG023-9[/TD]
[TD]AG023-9E01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AG023-9R01[/TD]
[TD]AG023-9E01[/TD]
[TD]AG023-9R01[/TD]
[TD]AG023-9[/TD]
[/TR]
[TR]
[TD]AG024-1[/TD]
[TD][/TD]
[TD]AG024-1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AG025-1[/TD]
[TD][/TD]
[TD]AG025-1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AG025-2[/TD]
[TD]AG025-1[/TD]
[TD]AG025-2[/TD]
[TD]AG025-1[/TD]
[/TR]
[TR]
[TD]AG026-1[/TD]
[TD][/TD]
[TD]AG026-1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AG027-1[/TD]
[TD][/TD]
[TD]AG027-1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AG027-1E01[/TD]
[TD]AG027-1[/TD]
[TD]AG027-1E01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AG028-1[/TD]
[TD][/TD]
[TD]AG028-1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AG028-1R01[/TD]
[TD]AG028-1[/TD]
[TD]AG028-1R01[/TD]
[TD]AG028-1[/TD]
[/TR]
[TR]
[TD]AG029-3[/TD]
[TD][/TD]
[TD]AG029-3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AG029-3A01[/TD]
[TD]AG029-3[/TD]
[TD]AG029-3A01[/TD]
[TD]AG029-3[/TD]
[/TR]
[TR]
[TD]AG030-1[/TD]
[TD][/TD]
[TD]AG030-1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AG030-1A01[/TD]
[TD]AG030-1[/TD]
[TD]AG030-1A01[/TD]
[TD]AG030-1[/TD]
[/TR]
[TR]
[TD]AG031-3[/TD]
[TD][/TD]
[TD]AG031-3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AG031-3A01[/TD]
[TD]AG031-3[/TD]
[TD]AG031-3A01[/TD]
[TD]AG031-3[/TD]
[/TR]
[TR]
[TD]AG031-3E01[/TD]
[TD]AG031-3A01[/TD]
[TD]AG031-3E01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AG031-3E01A01[/TD]
[TD]AG031-3E01[/TD]
[TD]AG031-3E01A01[/TD]
[TD]AG031-3E01[/TD]
[/TR]
[TR]
[TD]AG032-1[/TD]
[TD][/TD]
[TD]AG032-1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AG032-1E01[/TD]
[TD]AG032-1[/TD]
[TD]AG032-1E01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AG032-1E01R01[/TD]
[TD]AG032-1E01[/TD]
[TD]AG032-1E01R01[/TD]
[TD]AG032-1E01[/TD]
[/TR]
[TR]
[TD]AG032-1E01R02[/TD]
[TD]AG032-1E01R01[/TD]
[TD]AG032-1E01R02[/TD]
[TD]AG032-1E01R01[/TD]
[/TR]
[TR]
[TD]AG033-1[/TD]
[TD][/TD]
[TD]AG033-1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AG033-1E01[/TD]
[TD]AG033-1[/TD]
[TD]AG033-1E01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AG034-1[/TD]
[TD][/TD]
[TD]AG034-1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AG034-1E01[/TD]
[TD]AG034-1[/TD]
[TD]AG034-1E01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AG034-1R01[/TD]
[TD]AG034-1E01[/TD]
[TD]AG034-1R01[/TD]
[TD]AG034-1[/TD]
[/TR]
</tbody>[/TABLE]



https://ibb.co/b2VYOG


Thank you so much for your help. It's incredible how fast you guys answer.

Cheers!

 

[AlanY]

not the most elegant solution by far, but works for your sample data

Excel 2013/2016
	A	B
1	ID	Preceding
2	AG023-1
3	AG023-9	AG023-1
4	AG023-9E01
5	AG023-9R01	AG023-9
6	AG024-1
7	AG025-1
8	AG025-2	AG025-1
9	AG026-1
10	AG027-1
11	AG027-1E01
12	AG028-1
13	AG028-1R01	AG028-1
14	AG029-3
15	AG029-3A01	AG029-3
16	AG030-1
17	AG030-1A01	AG030-1
18	AG031-3
19	AG031-3A01	AG031-3
20	AG031-3E01
21	AG031-3E01A01	AG031-3E01
22	AG032-1
23	AG032-1E01
24	AG032-1E01R01	AG032-1E01
25	AG032-1E01R02	AG032-1E01R01
26	AG033-1
27	AG033-1E01
28	AG034-1
29	AG034-1E01
30	AG034-1R01	AG034-1
Sheet1

Cell Formulas
Range	Formula
B2	=IF(AND(MID(A2,8,1)="E",MID(A1,8,1)<>"E"),"",IF(ISERROR(MATCH(TRIM(LEFT(SUBSTITUTE(A2,"-",REPT(" ",500)),500))&"*",$A$1:A1,0)),"",IF(AND(MID(A1,8,1)="E",MID(A2,8,1)<>"E"),INDEX(A:A,MATCH(LEFT(A2,7),A:A,0)),A1)))