Format / copy paste comma separated data in one cell to other with other values

Hello - I have lots of rows (1000+) of data with 5 columns. Attached file shows the type of manipulation I would like to do from comma separated data in one of the columns of my data to separated in rows with other data staying same. Another twist is that, this column has values separated by "-" in which case I will need all the values in between that range (including the numbers).

Once this step is completed, my goal is to compare this data with another set of data to make sure that data in column D matches between the two data sets for unique combinations of column A,B and C (which I think I can do once I have this data formatted properly).

Thank you for help in advance.

Regards,
Jay

I thought putting steps might make it easier for someone to help me. So here it is:

So on the attached file here are the steps that I would like to complete:
1. Select all rows with values in column C as range1 (C1: to C4 in the example file)
2. For each cell in range1, separate out values separated by comma and then paste them in "desired" worksheet in C1 to C10 (there are 10 comma separated values)
3. Copy A2, B2 and D2 (corresponding to current row in range1) from "current" sheet to "desired" worksheet from A1 to A10, B1 to B10, D1 to D10 respectively (same value repeat in each column)
4. Then next cell in range1 but paste in "desired" worksheet at the bottom of previous action
5. Once this for loop is completed, identify all the cell in column C that contain "-" as range2
6. Then for each value in range2, add no. of rows determined by subtracting second number from first number and entering those values in sequence (in the attached example 224,225,226,227,228,229,230 - these are total of seven values instead of 6) and copy A, B, D column values down.

Thank you,
Jay

Thank you Amitkr3855 but I seem to get #NAME and #value error. For what I need to do, I am not sure if a formula will work.
I have been trying to come up a macro to do what I need to do and I have come up with below but when I get to the else statements in if loop I get a "application defined or object defined error on this line: Sheet3.Range("C" & current_row).Select

This is still work in progress but if someone has any suggestions on how to make it concise or better would be appreciated in addition to the help with the error.
Attached file that I have been working on.

Please Login or Register  to view this content.