excelexcel-formulajira

Excel concatenate advice for combining rows into a single field for jira bulk upload

I have a bit of a random question. I'm trying to convert hundreds of steps out of silk into Jira stories and sub-tasks. Unfortunately the export I've gotten is very unwieldy and is proving to be harder than I first expected.

CSV Structure:

Parent (story) Test Name (sub-task) Step Name Action Result
Big Test 1 Test 1 Step 1 Do this thing Outcome
Step 2 Do this next thing Outcome
Step 3 Do this other thing Outcome
Big Test 1 Test 2 Step 1 Do this thing Outcome
Step 2 Do this thing Outcome
Big Test 2 Test 1 Step 1 Do this thing Outcome
Step 2 Do this thing Outcome

Etc. this goes on for a while. The number of Test Names to Big Test is variable as is the number of steps per test.

What I'm wanting is to get a description column that will double as the Description in Jira that is pre-formatted
eg
Parent = Story summary
Test Name = Sub-task summary attached to story
Description =

Step Name Action Result
Step 1 Do this thing Outcome
Step 2 Do this next thing Outcome
Step 3 Do this other thing Outcome

I've tried adding multiple columns into the source table and using a concatenate to get to somewhat of the structure I need but I can't figure out a way to do it without adding heaps of columns to add the previous value above it.
I extended the Test Name down so its the same for each of the steps.

The most working thought I've got at the moment is along the lines of:

="||"&[Step Name]&"||"&[Action]&"||"&[Result]&"||"&char(10)

To return:

||Step 1 ||Do this thing ||Outcome ||

Then using an IF to check if the test name is the same as the row above and then concatenate those together but I can only get this to work for 2 rows. I've then had to add columns to check if there is to be another row etc. which is... not great.

I know I'm missing something somewhere that will make this easier. Any advice will be appreciated even if its just pointing me in the right direction. I had serious issues trying to google what I meant. Ideally I don't want to be creating these items individually as it will be a nightmare.

I am investigating trying something with a pivot and then going from there but struggling with that too.

I know this is probably worded bad but its the best I could describe it

Solution

  • Honestly, I am not sure what you want to do either. but I think you want description for each story to be a string (rather than a table as in your question, but since you didn't create that markdown yourself, so probably better to clear that up with a screenshot)

    If you have MS365, you can use below:

    enter image description here

    =LET(
    descriptions, C2:C8 & "||" & D2:D8 & "||" & E2:E8,
    stories, SCAN("", A2:A8, LAMBDA(a, v, IF(v = "", a, v))),
    sub_tasks, SCAN("", B2:B8, LAMBDA(a, v, IF(v = "", a, v))),
    unique_stories_and_tasks, UNIQUE(HSTACK(stories, sub_tasks), FALSE, FALSE),
    grouped, BYROW(
        unique_stories_and_tasks,
        LAMBDA(x,
            LET(
                story, INDEX(x, 1, 1),
                sub_task, INDEX(x, 1, 2),
                filtered, FILTER(descriptions, (stories = story) * (sub_tasks = sub_task), ""),
                TEXTJOIN(CHAR(10), TRUE, filtered)
            )
        )
    ),
    HSTACK(unique_stories_and_tasks, grouped)
)

    =C2:C8 & "||" & D2:D8 & "||" & E2:E8 concatenates step name, action and result.

    =SCAN("", A2:A8, LAMBDA(a, v, IF(v = "", a, v))) fills in the blank row in story column.

    =SCAN("", B2:B8, LAMBDA(a, v, IF(v = "", a, v))) fills in the blank row in sub task column.

    Then the main grouping logic is in BYROW:

    For each story + sub task, it filters out the relevant steps(or descriptions), and concatenates them with char(10)