Posts by Ste1605

    Hi there,

    I have attached a sample of the data and the issue.

    In rows 2-54 is my current data - note the data in columns J and K - there are multiple elements separated by a ;.

    In rows 8-28 is my desired results - the information in A-I is repeated based on the elements in J and K.

    I have tried to create the desired number of lines in the spreadsheet by adding a column and using the formula =SUMPRODUCT(LEN(K2)-LEN(SUBSTITUTE(K2,";","")))-1 - this counts the elements based on the ; character.

    This value is then used in a macro to create the number of blank rows needed but I cannot figure out to get the data into these rows;

    Sub Add_Rows()
      Dim r As Long
      Application.ScreenUpdating = False
      For r = Range("A" & Rows.Count).End(xlUp).Row To 4 Step -1
        If Cells(r, "A").Value > 0 Then Rows(r + 1).Resize(Cells(r, "A").Value).Insert
      Next r
      Application.ScreenUpdating = True
    End Sub

    Hi there,

    I have a data set where in column J and K I have data which is separated by ; - the data which is A-I is relatable to what is in these columns.

    I would like to delimit that data in both these columns which will have the same number of data elements in each and then create a line for each element which was delimited and add the data from A-I for each data element and repeat the process for over 8k rows.

    In the below example I would like the first line to only contain cat and Mon then a new line created and inserted with all information up to H with god and Tue and finally the process repeated with all information up to H with sheep and Wed. These columns will have a varying number of elements and it wont always be three.

    Thaks in advance.