How to create ID hierarchy from Excel column?

Okay friends, I'm stuck.

I have an Excel-sheet that contains information like below:

Title	ID
X	001 001
Y	001 001 001
Z	001 001 001 001
A	001 001 002
B	001 001 003
C	001 001 003 001
D	001 001 003 002
E	001 002
F	001 002 001

The ID indicates that the titles are in a hierarchy, so, for instance, title X would be a top level title, whereas title Y, A, and B, are direct children to parent title X. Title Z is a child to parent title Y, and so on and so forth.

The Excel-sheet contains about 1 400 rows of information like this.

Now, I want to import this Excel-sheet into our JIRA where the ID is going to be used in issue linking (i.e. Y, A, and B "relates to" X; Z "relates to" Y, etc.) However, JIRA will only accept the issue linking if the information is presented like this:

Title	ID	issue link
X	001 001
Y	001 001 001	001 001
Z	001 001 001 001	001 001 001
A	001 001 002	001 001
B	001 001 003	001 001
C	001 001 003 001	001 001 003
D	001 001 003 002	001 001 003
E	001 002
F	001 002 001	001 002

So my question is, how does one do the above in Excel? Is there any way to convert text into columns and then combine them somehow into a format like the above? I have, as I said, 1400 rows of this stuff, but I don't mind some manual work.

I have attached an example sheet of how I would like the information to be presented.

Formulas or VBA welcome!

The attached should help you do this based on your example data.

You need to copy your Sort ID list from your real file into the tables as per instructions on the file.
Then you can copy the result back into your file (as values so you don't mess up any original formatting).

Hopefully instructions make sense.

The first table on my file will give you the parent ID (although the top level ones get given a false parent ID, 001 001 is given 001).

Or you can just pop in the below formula into C2 and copy down.

=IF(LEN(B2)>7,LEFT(LEN(B2)-4),"")

Originally Posted by popon

I'm an idiot, sorry
All good now, thank you so much for your help!

No problem, don't forget to mark this thread as solved if you're happy it's resolved.