I have a list in Excel like this:

You can see that A01->A0101->A010101, it's a chain where we have parents and children.

I need to create a CSV file, where I can see their parent ID and child ID. Like here:

The question: How to make it possible in Excel? I have idea how to do it with C#, but I need to do it in Excel.

Which result I would like to see:

1. A01 - ID should be 1
2. A0101 - ID should be 2 | Parent ID - 1
3. A010101 - ID should be 3 | Parent ID - 2

In the columns it will be like this:

1. cell - 1
2. cell - 2 | cell - 1
3. cell - 3 | cell - 2

So, I would like to have list only with 2 columns, where I can see ID of the A01 and the parent ID.

In the link below you can see how it should work if you want to put a CSV file in JIRA.

Link:

Formula in B2:

=LET(x,A2:A18,y,SEQUENCE(ROWS(x)),HSTACK(y,XLOOKUP(LEFT(x,LEN(x)-2),x,y,"")))