0

i have a jira-export file, and need to get it in the 1NF to be able to use the pivot function.

the data comes like this:

|----|--------|---------------| | A | B | C | |----|--------|---------------| | 1 | BlaBla | C1 | |----|--------|---------------| | 2 | FooFoo | C1,C2,C3 | |----|--------|---------------| | 3 | LaLa | C3 | |----|--------|---------------|

and i need the data in this form: |----|--------|---------------| | A | B | C | |----|--------|---------------| | 1 | BlaBla | C1 | |----|--------|---------------| | 2 | FooFoo | C1 | |----|--------|---------------| | 2 | FooFoo | C2 | |----|--------|---------------| | 2 | FooFoo | C3 | |----|--------|---------------| | 3 | LaLa | C3 | |----|--------|---------------|

basically, the data in column C has to be looped and copy/pasted, if there are multiple entries.

any ideas?

Sub CopyData()
    Dim xrow As Long
    Dim cCell As Variant
    Dim strArray() As String
    Dim iCount As Integer

    xrow = 1
    Do while(Cells(xrow,"A") <> "")
        cCell = Cells(xrow,"C")  'get the value in column C
        strArray = Split(cCell,",") 'put the value(s) in an array (delimiter ",")
        iCount = UBound(strArray) - LBound(strArray)

    If iCount > 0 Then
        *??????*  'copy the row
        *??????*  'paste it below or at the end of the data-set with the different values in column C
        *??????*  'remove the row with the ","
    End If
    xrow = xrow + 1
    Loop
End Sub

Kind regards

davee
  • 156
  • 1
  • 10
  • 1
    and https://stackoverflow.com/questions/46884834/excel-vba-convert-comma-separated-values-to-different-columns – Slai Jan 15 '18 at 02:22
  • 1
    I can't find a good example, but Power Query alternative for Excel 2010+ https://datachant.com/2016/01/13/split-and-unpivot-comma-separated-values/ – Slai Jan 15 '18 at 02:25
  • 1
    @Slai ... and about 10,000 others :D I'm pretty certain there was another one (with LF as the delimiter) sometime within the last couple of days but can't find it at the moment. – YowE3K Jan 15 '18 at 02:31

0 Answers0