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;