Move Macro

[madness]

Hey guys I am working on an excel sheet which contains several thousand rows of data but every 19th row the data is in the wrong row and column and I need to move it up one row and right one column.

I have started to do this manually but i was wondering if it was possible if a macro could do this for me.

Thanks for your help

Alan

[TMS]

You don't say what column or what else is in the other rows. Assuming that you have free rein, with a formula:

E1: =IF(MOD(ROW(),18)=0,D2,"") and copy down

Adjust the column as required.


Regards, TMS

[madness]

Hey TMS thanks for the reply, sorry forgot to mention what i was working with, the column b is a combination of date and time fields which i will be separating on another date.

The data in every 19th row will be a time field each time being different from the last. the reason why it displays gets separated from the time field is because the data is converted from pdf documents and the formatting of the documents converts this way.

As i'm new will i have to change the =0 to anything specific to represent a time value.

If you want i can post a sample of what i'm working with.

Thanks

Alan

[TMS]

Yes, please post a small sample workbook ... With at least 38 rows of data to play with.


Regards, TMS

[madness]

Hey TMS

This is a small bit of how all the pdf documents that i will be working with will convert the data to excel.

Thanks

Alan

[TMS]

I'll get back to you later (this afternoon). Just about to board a plane but I have a revised formula and I'll put it into a VBA routine when I get home.

Regards, TMS

[madness]

Really appreciate than thank you for all your help.

Regards Alan

[TMS]

OK, the formula in cell C1 and copied down to the last row of data is:

=IF(B1="","",IF(--B1<1,"",IF(--B2<1,--B1+--B2,--B1)))

You then need to format the cells in column C as Date/Time and convert the formulas to values (or vice-versa). At that point, you can delete the original column B.



A VBA module to do all that is shown below:

Please Login or Register  to view this content.

Please see the attached updated sample workbook.


Regards, TMS

[madness]

Hey

Just tested that work works like a charm.

Legend gonna make it so much easier to sort out all those pdf's.

Thanks Again

Alan

[TMS]

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.