+ Reply to Thread
Results 1 to 23 of 23

VB Code to reorganize data

  1. #1
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,382

    VB Code to reorganize data

    Hello:

    Please refer to attached file.
    I have data as shown from Column O onward.
    Each days clockIn-Out data for each employee is as shown and occurs every 4th column until Column EE (31st)
    I need VB Code to reorganize this data in Column A thru D as shown starting at Cell A3

    Let me know if you have any questions.
    Thanks.

    Riz
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,382

    Re: VB Code to reorganize data

    Hello:

    Can someone have a look at this.
    Thanks

    Riz

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: VB Code to reorganize data

    Hello Riz,

    The macro below has been added to the attached workbook. There is a button on the sheet to run it.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,382

    Re: VB Code to reorganize data

    Hi Leith:

    This works great, thanks.
    Now assume, i have date in Cell K1.
    I need a separate code just to paste for this particular date with the same logic as you have done.

    Just 1 day of data.
    The reason is that i have lots of Monthly data which i can use the above code but going forward i can use new code for just 1 days data (Faster).
    Please let me know if you have any questions.
    Thanks.

    Riz

  5. #5
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,382

    Re: VB Code to reorganize data

    Hi Leith:

    I am having small issue with the code with my data.
    Please see attached pic which shows where the data needs to start.
    In my file, date will be at Row 599.
    Start pasting at A600 down.

    Please let me know if you have any questions.
    Thanks.

    Riz
    Attached Images Attached Images

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: VB Code to reorganize data

    Hello Riz,

    In your last post...

    (1) Will the output still go to columns A:D?

    (2) Will the Clock-In/Out data still start in column O?

    (3) In in the first 3 rows, is there a cell pointing to the date you want to use?

  7. #7
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,382

    Re: VB Code to reorganize data

    Hello Leith or someone:

    Please ignore #5 above, i have resolved.
    I need help with #4.
    Please let me know if you have any questions.
    Thanks.

    Riz

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: VB Code to reorganize data

    Hello Riz,

    If you have the date in K1 for a single day, will the output still be in columns A:D ?

  9. #9
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,382

    Re: VB Code to reorganize data

    Hi Leith:

    Please ignore #5 above as i was able to modify code to take care with my data.
    However, as i have mentioned in #4, i want to do the same for single date which will be in cell K1.
    I have completed whole month, now going forward i would like to add the code so that it will paste at last row in column A.
    Please let me know if you have any questions.
    Thanks.

    Riz

  10. #10
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,382

    Re: VB Code to reorganize data

    Hi Leith :

    Yes, output will be in column A:D as usual.
    Thanks
    Riz

  11. #11
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: VB Code to reorganize data

    hELLO rIZ,

    Okay, this macro will check the contents of cell K1. If the cell does not contain a Date, it will exit silently. If the Date cannot be found, a message will be displayed.

    Please Login or Register  to view this content.
    Last edited by Leith Ross; 03-28-2016 at 09:34 PM.

  12. #12
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,382

    Re: VB Code to reorganize data

    Hi Leith:

    Small problem.
    It is overwriting at cell A3.
    Basically once i start using then obviosly column A:D will have data until Today()-1.
    Now Today() comes so new data needs to be pasted after the last data in column A.
    I hope this is clear and please let me know if any questions.
    Thanks
    Riz

  13. #13
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,382

    Re: VB Code to reorganize data

    Hi Leith:

    I was able to edit the code for my need.
    Once again thanks a lot and will contact if further issue.
    Thanks
    Riz

  14. #14
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,100

    Re: VB Code to reorganize data

    @Leith Ross,

    Very impressive and effective code in Post #3!

    I have added lines in Red, if that is O.K. with you?

    Please Login or Register  to view this content.
    Kind Regards.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  15. #15
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,382

    Re: VB Code to reorganize data

    Hello:

    Thanks a lot for such a great help to Leith and Winon.

    Very impressive work.
    Riz

  16. #16
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,100

    Re: VB Code to reorganize data

    Hi rizmomin,

    You are welcome, glad I could take the liberty of tweaking Leith's Code to assist you further.

    Also thank you for adding to my Reputation.

    @Leith Ross,

    Thank you for not taking any offence, Leith.

    You remain my mentor and the best!

    Best Regards.

  17. #17
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,382

    Re: VB Code to reorganize data

    Hi Winon & Leith:

    Would like 1 more feature on this code if possible.
    Instead of pasting the data to the next blank row, would like to paste every 50 rows.
    So basically for 1st of the month, start pasting as 600, 2nd of the month 650, 3rd of the month 700 and so on.
    Please let me know if you have any questions.
    Thanks.
    Riz

  18. #18
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: VB Code to reorganize data

    Hello Riz,

    This macro calculates the next row in column "A" using the following equation: 600 + ((Day Number - 1) * 50). The day number is taken from the date in "K1".

    Please Login or Register  to view this content.

  19. #19
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,382

    Re: VB Code to reorganize data

    Hi Leith:

    Thanks a lot...i need to make sure the date in cell K1 is compared in Row 599 only and pick up the data from that location cloumn Row 600 down.
    Please let me know if you have any questions.
    Thanks.

    Riz

  20. #20
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: VB Code to reorganize data

    Hello Riz,

    Sorry, I don't follow you. Can you provide an example of what you mean?

  21. #21
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,382

    Re: VB Code to reorganize data

    Hello Leith:

    Please refer to attached file.
    This is from my actual file.
    I will have date in cell C1
    Now go down to row 599.
    This row will have date and data.
    I will need the data in Column A:D from row 600 down.
    Allocate 50 rows for each date.
    Example :
    Row 600 to 649 will be for 3/1/2016
    Row 650 to 699 will be for 3/2/2016 and so on.
    Row 700 to 749 will be for 3/3/2016 and so on.

    Now depending on the date in cell C1, lookup the data ot match the date.
    Lets say Date in C1 = 3/3/2016
    In case paste the data from cell W600:Y649 and paste at B700 but add 3/3/2016 in column A (Cell A700 down until data for the day)

    Please let me know if you have any questions.
    Thanks.

    Riz



    Let me know if you have any questions.
    Thanks.
    Attached Files Attached Files

  22. #22
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: VB Code to reorganize data

    Hello Riz,

    Thanks for the workbook. That made it clear. Here is the revised and tested macro.
    Please Login or Register  to view this content.

  23. #23
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,382

    Re: VB Code to reorganize data

    Hi Leith:

    You are Super excellllllllllllllllllller.
    Thanks a lot for great help
    Riz

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. reorganize raw data..
    By BPHAN2 in forum Excel General
    Replies: 1
    Last Post: 07-23-2014, 05:20 PM
  2. I need to reorganize the data with VBA
    By baetienne in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-29-2014, 03:40 PM
  3. Reorganize data
    By Kayline in forum Excel General
    Replies: 6
    Last Post: 03-06-2013, 11:57 AM
  4. Reorganize data
    By ngpm515 in forum Excel General
    Replies: 0
    Last Post: 11-02-2011, 05:19 PM
  5. Reorganize a data set
    By RPADC in forum Excel General
    Replies: 3
    Last Post: 07-11-2010, 02:19 PM
  6. Reorganize data
    By pentiumeric in forum Excel General
    Replies: 13
    Last Post: 07-13-2009, 04:32 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1