Results 1 to 6 of 6

Thread: Macro to Sort 4 days data, Keep Sequential Data and Delete rest

  1. #1
    Member
    Join Date
    Aug 2013
    Posts
    59
    Rep Power
    12

    Macro to Sort 4 days data, Keep Sequential Data and Delete rest

    I currently use Excel Version 2003 (It is okay, even if macro is for Excel2007)

    Data Range A:O

    Row data could differ from 2,000 to 6,000 rows (having 4 days data)

    Column O has Date Data in format dd-mmm-yy

    All data in its current form is arranged in Ascending order, based on Date Data in Column O

    I need a VBA Macro Procedure that could-

    1) Sort Data in Column A:O,
    first on Column B, then sort on
    Column D, then sort on
    Column O, then sort on
    Column C
    2) Above would line up 4 days dataset based on date, but few record could be for less than 4 days, say, only for 1st, 2nd and 3rd days, but not for 4th day, or for 1st,3rd,and 4th day, but not for 2nd day and so on, which should be deleted, as each record set should be for 4 days, in continuity. Shortly, Each data should be for minimum 4 days, if not, entire row should be deleted.

    For Clarity purpose in attached workbook having sample data,
    a) Sheet1 (Named as - RAWDATA) - Contains RAW data
    b) Sheet2 (Named as - Sorted on B D O C) shows, how data should be organised after sorting per criteria given in 1) above,
    c) Sheet2 (Named as - Sorted on B D O C) Highlighted rows in Grey Color shows where data is not for 4 days sequentially, and hence such row should be deleted
    d) Sheet3 (Output) Final Output data is given, just for sample basis, each data set for 4 days, arranged sequentially.

    Data in Sheet3 is just sample output, done manually currently, so restricted till rows 72

    Macro should process and bring the desired data in Sheet1 itself, where raw data was residing originally.
    Attached Files Attached Files

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Member
    Join Date
    Aug 2013
    Posts
    59
    Rep Power
    12
    Yes Sir, bit similar to that, but when I am sorting data, I feel, some of the data, that were meeting my criteria is also getting deleted, not sure, how. But , given that code, when I am attempting to sort data on Column, B, D, O, and then on C, it gives some vague result, or some silly mistake, I'm making. Can you plz append a few line to that code, that would sort data on Columns B, then on Column D, O, and lastly on Column C. Apology

    I guess, in current query, data is not sorted, which is first required to be done, and then code given at http://www.excelfox.com/forum/f2/ret...ete-rest-1606/ this should follow.
    Last edited by analyst; 01-08-2014 at 04:14 PM.

  4. #4
    Member
    Join Date
    Aug 2013
    Posts
    59
    Rep Power
    12
    I just tried arranging data (thru SORT) first, and then running code, you named as kTest, but getting some vague answer. I am relooking at my data structure, and find no flaw in your code, given earlier kTest.

    I've rechecked my data arrangment and have done sorting. KTest is perfectly alright. There was no intetion to create SPAM, please delete this thread. I apologise for my mistake.
    Last edited by analyst; 01-08-2014 at 04:23 PM.

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Your raw data and the sorted data is not matching anymore.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  6. #6
    Member
    Join Date
    Aug 2013
    Posts
    59
    Rep Power
    12
    I've goof up. Now sorted, have plugged in the macro to arrange data to sort properly, and then calling kTest to do rest of the things. Thanks.

    I regret to have consumed forum, time and resource, kindly excuse.

Similar Threads

  1. Replies: 10
    Last Post: 02-18-2014, 01:34 PM
  2. Replies: 6
    Last Post: 08-14-2013, 04:25 PM
  3. Replies: 6
    Last Post: 05-10-2013, 01:13 AM
  4. Excel Macro to Sort Data if a value changes in defined range
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 4
    Last Post: 09-05-2012, 10:31 AM
  5. Sort Data When a Header Is Clicked
    By Rasm in forum Excel Help
    Replies: 9
    Last Post: 08-01-2012, 06:46 AM

Posting Permissions

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