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;


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

    Files

    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.

    ABCDEFGHIcat;dog;sheepMon,Tue,Wed