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.