Please help in Reformatting data in Excel

[Palraj]

Hi,

Can you please suggest how to reformat the data?
Sample input canbe found in Sample input.xlsx
This is only sample data. Only 2 rows are displayed. In the actual file, I have around 30,000 rows to be reformatted.

Expected output sample can be found in Expected Output.xlsx

Can you please help?

Regards,
Pal.

[bmouse]

Not sure if I can manage it, but I will try something.

What is the maximum number of years one row can have? In your example you have 7 years for row 2, can there be more?

[Palraj]

Thanks for replying.
Yes, it could be more. Number of years in the second column vary from 1 to 54.

[sktneer]

Try this to see if you get the desired output....

Please Login or Register  to view this content.

[bmouse]

In that case, any approach I can produce wouldn't work, simply because of the huge amount of data, sorry.

[Palraj]

Hi,

It worked fine for partial inputs. Thanks a ton for the help.

In case, if the | separated values are present in column 'E', how to modify the macro? Can you please let me know?
I tried replacing 'D' with 'E' in the code. But it didn't work.

Regards,
Pal

[Catman50]

Good Day mate, I have attached your example Input sheet and presented code to manipulate the data as you requested This example transfers the data to Sheet2 on the same workbook, It should work for your larger DataFile with just a little tweaking. ie naming Column A and Column E as suggested and inserting the Formula To calculate the Number of Rows of Data you Have. The macro inserts another Formula in the sheet in the Column E. If you need help saving the Data to another workbook just holla!

[JOHN H. DAVIS]

Another:

Please Login or Register  to view this content.

[Catman50]

Slight rewrite of Macro included in "Sample_Input"
Instead of placing a formula in every cell in Column E
It Only does so in cells adjacent Data

Please Login or Register  to view this content.

[event21]

Hi -

Here is another solution.

Please Login or Register  to view this content.

event