Delimit two columns and add a loop based on the input from these columns

  • 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.

  • Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

  • 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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!