Seprating Comma dlimited data in specific way

Hi, I have uploaded an excel file as an attachment which contains three sheets named "before", "after" and "RepresentativeData". The "before" sheet data should be converted to "after" sheet data. "RepresentativeData" sheet data is provided as a sample for your testing purpose and this would be actual data where operations would be performed.

Actually, in sheet "before" column A, each comma separated value correponds to other comma separated value in column B as a one to one correspondence. Now both corresponding values should be separated such that they are infront of each other as shown in "after" sheet in two columns. I hope you understand my problem. The whole operation could be performed on the same sheet so it is not compulsory that output should be on the new sheet.

Again Many thanks in advance for the help and your precious time.

Best Regards,

Hopefully this code works for you...

Please Login or Register  to view this content.

This code is working like a charm. Many thanks. Can you modify it so that it could just copy the data in other columns(aside from column A and Column B) as it is.

Like e.g if the row 1 contains ----> a,b,c,d 1,2,3,4 hello in columns A,B and C respectively then the word "hello" should print itself in all the new rows created accordingly.