Format Excel sheets export by JIRA-(JIRA custom reports)

Question

I need to generate custom report in jira with transition time tracking details. this jira plugin can generate the time tracking details. but I need to add these time tracking details to the jira default all field export report.without copy paste the values I need to find a way to merge these data.

jira default all field report

report can be generate by plugin I mentioned above (Sheet A)

what I needed data from above report (Sheet B)

then I need to merge this table into the jira default all field export report

what I have found

1.write macro code

2.converting these data into sql then use sql queries.

is there any other ways to do this? or should I select a above option? or what would be the easiest way to achieve this?.

Answer 1

After searching may ways to get an answer for the question I have found a way to solve this using macros.

Step 1: I have created a new empty work sheet called "Sheet B" in the same Excel work book generated by the plugin.

Step 2: write a macro code for Sheet A for generate the data into Sheet B`

Function FindColumn(StartColumn As Integer, EndColumn As Integer, WantedString As String) As Integer

Dim LastColumn As Integer
Dim ReturnValue As Integer

i = 0
ReturnValue = 0

    LastColumn = EndColumn - StartColumn
    Do While i <= LastColumn

    If (Worksheets("Sheet A").Cells(1, i + StartColumn) = WantedString) Then
        ReturnValue = i + StartColumn
        Exit Do
    End If
    i = i + 1
    Loop

    FindColumn = ReturnValue


End Function

Sub NewTime()

SetAll1to1ColumnsTime

End Sub

Sub SetAll1to1ColumnsTime()
Dim CurrentLine As Integer
Dim NROfColumns As Integer
Dim NROfLines As Integer
Dim Str As String
Dim Str2 As String
Dim Str3 As String


    NROfColumns = Worksheets("Sheet A").UsedRange.Columns.Count

    NROfLines = Worksheets("Sheet A").UsedRange.Rows.Count


    CurrentLine = 2

    Do While CurrentLine <= NROfLines


        Column = FindColumn(1, NROfColumns, "Issue Key")
        Worksheets("Sheet B").Cells(CurrentLine, 1).Value = Worksheets("Sheet A").Cells(CurrentLine, Column).Value

        Column = FindColumn(1, NROfColumns, "Status")
        Worksheets("Sheet B").Cells(CurrentLine, 2).Value = Worksheets("Sheet A").Cells(CurrentLine, Column).Value

        Column = FindColumn(1, NROfColumns, "Status")
        Str = Worksheets("Sheet A").Cells(CurrentLine, Column).Value

            If Str = "Development Done" Then

                Str2 = Worksheets("Sheet A").Cells(CurrentLine, 4).Value
                Worksheets("Sheet B").Cells(CurrentLine, 4).Value = Str2

            End If

            If Str = "Find Solution" Then

                Str3 = Worksheets("Sheet A").Cells(CurrentLine, 3).Value
                Worksheets("Sheet B").Cells(CurrentLine, 3).Value = Str3

            End If


        CurrentLine = CurrentLine + 1
    Loop


    End Sub

Step 3:Then I write a macro code for the generated Sheet B for format the data as I needed.
Generated Sheet B using macro code

this is the macro code for Sheet B

Sub mergeCategoryValues()
Dim lngRow As Long

With ActiveSheet
    lngRow = .Cells(65536, 1).End(xlUp).Row
    .Cells(1).CurrentRegion.Sort key1:=.Cells(1), Header:=xlYes

    Do
        If .Cells(lngRow, 1) = .Cells(lngRow - 1, 1) Then
            .Cells(lngRow - 1, 4) = .Cells(lngRow - 1, 4) & "" & .Cells(lngRow, 4)
            .Rows(lngRow).Delete
        End If

        lngRow = lngRow - 1
    Loop Until lngRow = 1
End With
End Sub

work Done! Note:Do not mind about the column B