Hey folks, I’m in a bit of a pickle here. I’m trying to move a ton of test steps from Silk into Jira as stories and sub-tasks. The export I got is a mess and it’s giving me a headache.
Here’s what I’m dealing with:
- A CSV with columns for Parent (story), Test Name (sub-task), Step Name, Action, and Result
- Each story has multiple tests, and each test has multiple steps
- The number of tests per story and steps per test varies
What I want to do is create a Description column that I can use in Jira. It should look like this:
| Step Name |
Action |
Result |
| Step 1 |
Do X |
Result |
| Step 2 |
Do Y |
Result |
| Step 3 |
Do Z |
Result |
I’ve tried using CONCATENATE, but I’m stuck. I can only get it to work for two rows at a time. I know there’s gotta be an easier way to do this.
Any ideas? Even a nudge in the right direction would be awesome. I’m open to using pivots or any other Excel magic you might know. Thanks!
Having faced similar challenges, I found that Power Query in Excel 2016 and later is quite efficient at merging multiple rows into a single Jira field. You can load your CSV data into Power Query, group it by the parent identifier and test name, and then aggregate the step details into a formatted structure. Functions like List.Transform and Text.Combine help in creating a table-like output without repeating steps, and this method handles varying numbers of tests or steps robustly.
If Power Query is not an option, VBA can be used to cycle through the rows and build the description. Testing your solution on a smaller dataset first is always a good practice.
As someone who’s been in the trenches with data migration, I feel your pain. Have you considered using a combination of TEXTJOIN and FILTER functions? This approach can be a game-changer for consolidating multiple rows.
Here’s a method that’s worked wonders for me:
- Create a unique identifier for each story-test combination.
- Use FILTER to grab all rows for each identifier.
- Apply TEXTJOIN to concatenate the filtered rows into your desired format.
The formula can look intimidating at first, but it’s quite powerful once you get the hang of it. It’ll handle varying numbers of steps like a champ.
If you’re not comfortable with complex formulas, a simple macro could do the trick too. It’d loop through your data, building the table as it goes.
Remember, whatever method you choose, always back up your data first. You don’t want to lose hours of work to a misplaced comma!