Search code examples
excelcsvjira

I have a list of several values. I need to understand which of them is parent and which is child. How to do it in Excel?

I have a list in Excel like this:

enter image description here

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:

enter image description 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: https://confluence.atlassian.com/jirakb/how-to-import-issues-with-hierarchy-from-a-csv-into-jira-software-1087512458.html

Solution

  • Here is one option using Excel ms365 functions:

    enter image description here

    Formula in B2:

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