+ Reply to Thread
Results 1 to 30 of 30

Macro to sort data in a cell range on a row by row basis

  1. #1
    Registered User
    Join Date
    02-06-2018
    Location
    Dublin, Ireland
    MS-Off Ver
    Microsoft Office 365 Home Subscription
    Posts
    47

    Macro to sort data in a cell range on a row by row basis

    To anybody that views this thread, it is a continuance to similar previous help provided to me by the forumite " jindon ".
    He is aware that this thread is here and is actively working on it, just in case any others were kind enough to offer help. I would not wish to waste your precious time.

    Jindon Sample Data and requirements attached. Thanks for any help forthcoming.
    Attached Files Attached Files
    Last edited by Learner_Mike; 06-14-2019 at 12:56 AM.

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro to sort data in a cell range on a row by row basis

    OK, I think I understand the logic.

    I can not test my code as I don't have source workbook.
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    02-06-2018
    Location
    Dublin, Ireland
    MS-Off Ver
    Microsoft Office 365 Home Subscription
    Posts
    47

    Re: Macro to sort data in a cell range on a row by row basis

    Thanks jindon, but it does not work, it is deleting all the data in the cells of the rows it is supposed to sort.

    Also when I put the code in as a macro, I get line across after each "End Sub" , looking like I am recording 3 seperate macros in 1, is this ok

    In the workbook I uploaded "Sheet1" should allow you to test the macro as that is actual data

    Thanks.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro to sort data in a cell range on a row by row basis

    It is not deleting anything, just change the back ground color to normal.

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro to sort data in a cell range on a row by row basis

    Just for the test, if you change FormulaR1C1 to Value, you can see the value, not the formula
    Please Login or Register  to view this content.
    But, is this how you wanted?

  6. #6
    Registered User
    Join Date
    02-06-2018
    Location
    Dublin, Ireland
    MS-Off Ver
    Microsoft Office 365 Home Subscription
    Posts
    47

    Re: Macro to sort data in a cell range on a row by row basis

    As I said in the Details sheet the cells are all formatted, like the original Macro you previously provided, the formatting should not be effected when the Macro runs, horse one is yellow and each cell has the same formatting so if horse one is sorted and the data moved for example from cell C9 to E9 it will still be yellow as E9 has the same formatting as C9 based on the horse number in that cell .1 or .2 Only cells where the formula in them returns no data will remain BLACK and each cell is formatted for this also

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro to sort data in a cell range on a row by row basis

    Ok, this used excel sort functionality.
    YOu can get rid of mySort sub procedure.

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    02-06-2018
    Location
    Dublin, Ireland
    MS-Off Ver
    Microsoft Office 365 Home Subscription
    Posts
    47

    Re: Macro to sort data in a cell range on a row by row basis

    Look at worksheet named "Details" Row 52 is a picture of what cells beginning in C64 in worksheet named "Sheet1" look like before running the macro

    Look at worksheet named "Details" Row 53 is a picture of what cells beginning in C64 in worksheet named "Sheet1" should look like after running the macro

    I will run the new code above now and reply

    Thanks Jindon

  9. #9
    Registered User
    Join Date
    02-06-2018
    Location
    Dublin, Ireland
    MS-Off Ver
    Microsoft Office 365 Home Subscription
    Posts
    47

    Re: Macro to sort data in a cell range on a row by row basis

    No it is not working, it is changing some data on skip rows, C9,C12,C15 and so on all the way down these are SKIP rows and should be totally ignored by the macro

    It is also not sorting the data correctly,

    I have given examples of win row sorts and placed row sorts in the "Details" sheet, where I have sorted the data manually when you run the macro sheet1 should look exactly like my sorted examples when the macro is run

    If you read the "Details" sheet again carefully and look at the before and after examples it will save a lot of time and I will only be typing the same here that is already on Sheet1, I can't explain it any better here than what's already there

    Thanks Jindon

  10. #10
    Registered User
    Join Date
    02-06-2018
    Location
    Dublin, Ireland
    MS-Off Ver
    Microsoft Office 365 Home Subscription
    Posts
    47

    Re: Macro to sort data in a cell range on a row by row basis

    Using Excel sort functionality doesn't help if I remember from old macro

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro to sort data in a cell range on a row by row basis

    I knew that and just for the test.

    Are the rest working?

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro to sort data in a cell range on a row by row basis

    See the attached and run the code on Sheet1.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    02-06-2018
    Location
    Dublin, Ireland
    MS-Off Ver
    Microsoft Office 365 Home Subscription
    Posts
    47

    Re: Macro to sort data in a cell range on a row by row basis

    I am replying but it will take me awhile, if you want to leave it for now and check back in awhile, we can resume it then.

    Thanks ever so much Jindon, it's greatly appreciated.

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro to sort data in a cell range on a row by row basis

    OK, as I mentioned in PM, I'm also busy, so let's resume sometime tomorrow.

    By the way, if the code ask to locate the file for the formula due to the wrong file path, you can hit Ctrl + Break to go in to a debug mode and choose Quit or continue.

    Good luck.

  15. #15
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Macro to sort data in a cell range on a row by row basis

    Is there a reason for the password on the file
    One I have learnt over the last few months, " You are NEVER too old to learn ".
    Sometimes I forget to say " Thank You " for your assistance, so here I say " A BIG Thank You. "

  16. #16
    Registered User
    Join Date
    02-06-2018
    Location
    Dublin, Ireland
    MS-Off Ver
    Microsoft Office 365 Home Subscription
    Posts
    47

    Re: Macro to sort data in a cell range on a row by row basis

    It is still not sorting correctly, but the skip row is not being effected now, there's also a problem with the horses number, it seems to be putting high number horses ahead of low number horses, also seems to be doing single digit number horses, then double digit numbers in batches if you see what I mean

    goes 1 to 9, then 10 to 19, then 20 to 21, then 30 to 39, then 40

    instead of 1 straight thru to 40

    If you read the below a few times and look in depth at my "details" worksheet examples, it will save you a lot of time I feel, everything below was already given in the "details" sheet so you have my manually sorted rows as examples of what the end result should be

    we are aiming to sort as follows;

    in a win row and a place row there are effectively 3 pieces of data in each cell

    IN A WIN ROW (always starts in C10 and every 3 cells down, C10,C13,C15,C18 and so on down)

    We are sorting the horses in each WIN ROW from the left, best horse first (highest score) to worse horse last (lowest score).

    The top piece of data in a WIN ROW cell is its Win score so for example if horse 1 had 100.1 it has won 100% of its races its bottom piece of data would also have to be 100.1 because the bottom piece of data in a win row cell represents its place score (1st,2nd and thirds added together)

    If horse number 9 had the same data in its cell as horse number 1 the macro would place horse number 1 before horse number 9 after sorting to keep the sort uniform all the way down all win rows in the worksheet

    If in every race each horse had a different win score in its win row cell it would be easy as the macro would automatically sort them by the top piece of data it wouldn't have to look at the bottom piece of data in each win row cell and the horse number would go with the highest score automatically

    but because in reality some horses in a race will have the same top piece of data in their win row cell (the same win score) now we need to look at the bottom piece of data in the horses win row cell to sort them further

    so if horse number 6 (.6) and horse number 11 (.11) both had 20 as their top piece of data (the same percentage of races won) in their win row cell, we then look at at bottom piece of data in their win row cell (their top3 place score/percentage) to sort them further. If horse 11 had a bottom piece of data scoring 33 and horse number 6 had a bottom piece of data scoring 28. Horse 11 would go before horse 6 in the sorted row.

    The same for horses that in their win row have a (top data) win score of zero then they would be sorted by their (bottom data) place score ahead of each other, so horse 3 with 0.3 top and 12.3 bottom and horse 5 with 0.5 top and 18.5 bottom would mean that horse 5 would be ahead of horse 3

    horses with zero score on top and bottom would be sorted in the row by their number lowest horse number first.

    cells filled black mean that horse has never run and these cells sort/shift to the furthest right

    So a win row is sorted by its top piece of data (win score), do 2 or more horses have the same win score, if so, look at their bottom piece of data (place score), do 2 or more horses have the same win (top) and place (bottom) score, then put lowest horse number first, to keep worksheet uniform down the rows

    I think we should just sort the win rows first to get them right, I have given before sort and after sort win rows as examples in the "Details" sheet so you can see what a perfect sort should look like after the macro has been run.

    So if we now go from C9 with skip, Sort, skip, we can then amend the macro to sort the place rows as they sort a little differently as stated in the "details" worksheet once we have the win rows sorted.

    Thanks so much, Jindon.

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro to sort data in a cell range on a row by row basis

    Hahah, now I understand.

    I thought the decimals are the result of the calculation of %, but it is a Hourse Number....

    Replace GetSortVal function with the following and see the result.
    Please Login or Register  to view this content.

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro to sort data in a cell range on a row by row basis

    Try replace GetSortVal function with the following and see if it runs faster.
    Please Login or Register  to view this content.

  19. #19
    Registered User
    Join Date
    02-06-2018
    Location
    Dublin, Ireland
    MS-Off Ver
    Microsoft Office 365 Home Subscription
    Posts
    47

    Re: Macro to sort data in a cell range on a row by row basis

    Hi Jindon, latest code in workbook attached, not sorting correctly and still very slow. Thanks.
    Attached Files Attached Files

  20. #20
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro to sort data in a cell range on a row by row basis

    Due to the need of keeping the cell format etc, we need to use Excel sort functionality for such complicated sort condition.
    The slow speed possibly come from the formula re-calculation of each heading when the code put it back to the cell after the sort.

    So, we need to add extra rows to isolate each heading, so that we don't need to touch them and no re-calculation.

    Try new code on Sheet2 which I added the rows and they are hidden.
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    02-06-2018
    Location
    Dublin, Ireland
    MS-Off Ver
    Microsoft Office 365 Home Subscription
    Posts
    47

    Re: Macro to sort data in a cell range on a row by row basis

    Can you please leave the very last all black row in the worksheet visible the one with "END" across the row as in original, The macro is deleting this I need this to stay in the worksheet, I run 7 other macros and there is in-built code in each worksheet that refers to this row so it has to remain


    so the last 2 rows should be a hidden row and my original row with end in it

    Thanks

  22. #22
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro to sort data in a cell range on a row by row basis

    Select Row 272:274, right click, Unhide.
    Select Row 273, right click, Insert, right click again and Hide.

    Is that what you mean?
    How about the speed?

  23. #23
    Registered User
    Join Date
    02-06-2018
    Location
    Dublin, Ireland
    MS-Off Ver
    Microsoft Office 365 Home Subscription
    Posts
    47

    Re: Macro to sort data in a cell range on a row by row basis

    Sorry ignore my last post #21 I have inserted a workaround. Speed is fabulous. Thanks a lot. I will post another query shortly. I have another macro if you could alter it for me, it shouldn't take longer than minutes.

    Thanks Jindon, much appreciated.

  24. #24
    Registered User
    Join Date
    02-06-2018
    Location
    Dublin, Ireland
    MS-Off Ver
    Microsoft Office 365 Home Subscription
    Posts
    47

    Re: Macro to sort data in a cell range on a row by row basis

    Your sort macro is the last macro I run, but as you cannot reverse the running of a Macro, it will cause me a problem based on the following,

    After I run your macro, a horse could be taken out of the race for a number of reasons.

    This macro deletes Formula and Data but leaves formatting from a Cell where the first piece of text is a zero

    For Each cl in Range("C11:AFT661")
    If Left(cl.value,1) = 0 Then cl.ClearContents
    Next cl

    Can you alter it to only delete the Data in a cell leaving the FORMULA and FORMATTING in the cell untouched. The cell range is ok

    where the LAST piece of text in the cell is the horses number, you can leave the zero and I would change the zero above to whatever horse number including the Dot is a non runner, remembering horses can be between number .1 to number .40

    So if I replace the zero with .2 every cell where the bottom piece of data ends with a .2, the macro would leave the FORMULA and FORMATTING and just delete the Data in the cell

    It should differentiate between .2 and .20 , .1 and .11 .18 and so on and only delete exactly what the zero is replaced with between .1 and .40

    Then I can rerun your sort Macro without horse 2 as in example above

  25. #25
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro to sort data in a cell range on a row by row basis

    Can you alter it to only delete the Data in a cell leaving the FORMULA and FORMATTING in the cell untouched.
    That is impossible, unless you clear the source data in the other workbook.

    Am I missing something?

  26. #26
    Registered User
    Join Date
    02-06-2018
    Location
    Dublin, Ireland
    MS-Off Ver
    Microsoft Office 365 Home Subscription
    Posts
    47

    Re: Macro to sort data in a cell range on a row by row basis

    Ok, I will come back to you on that, Thanks for the amazing Sort Macro, I really appreciate it. I am signing out now, my master worksheets contain 800+ rows before I run a series of Macros to get them down to the Sort stage, I will insert the hidden rows and do some testing when I alter my existing Macros and get back to you. Thanks Again Jindon.

  27. #27
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro to sort data in a cell range on a row by row basis

    OK, one possibility is to change to formula with a switch somewhere on the sheet.

    e.g
    Take formula in C11,
    Now
    ='C:\Users\W\Music\[Data.xlsx]D'!$CN$423&" "&'C:\Users\W\Music\[Data.xlsx]D'!$CO$423

    If you add a switch like B1
    =If($B$1="Clear",If(Left('C:\Users\W\Music\[Data.xlsx]D'!$CN$423,1)="0","",'C:\Users\W\Music\[Data.xlsx]D'!$CN$423&" "&'C:\Users\W\Music\[Data.xlsx]D'!$CO$423),'C:\Users\W\Music\[Data.xlsx]D'!$CN$423&" "&'C:\Users\W\Music\[Data.xlsx]D'!$CO$423)

    Then when you change B1 to Clear, it will be "" in the cell where the cell starts with 0.

  28. #28
    Registered User
    Join Date
    02-06-2018
    Location
    Dublin, Ireland
    MS-Off Ver
    Microsoft Office 365 Home Subscription
    Posts
    47

    Re: Macro to sort data in a cell range on a row by row basis

    No you don't understand, I want a macro to clear every cell where the last piece of data ( the furthest right piece of text on the bottom piece of data in every cell ) all data is cleared from cell nothing to do with zero

    So sample macro is if first piece of text in cell, Left = 0 (zero)

    I want last piece of text in cell Right = .2 or .3 or .40

    so cell looks like for example

    100.2
    100.2

    so when I type .2 or whatever between .1 to .40 all cells in the worksheet that end with what I type are emptied of their data so horse number 2 is not running in race and disappears from worksheet, then I can re-run the sort macro to re-sort the worksheet without horse 2

    Thanks.
    Last edited by Learner_Mike; 06-15-2019 at 10:51 AM.

  29. #29
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro to sort data in a cell range on a row by row basis

    So you want to remove the formula and replace it with the edited constant result?

    If so, it can be done.

    I'm going off line for the day, so tomorrow

  30. #30
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro to sort data in a cell range on a row by row basis

    Try the attached.
    When the dialog box appear, you can enter any number and if you want to multiple numbers use comma in between numbers.
    If you empty the input box, it will show all.

    Added one conditional format on the top.

+ 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. [SOLVED] Macro To Sort Data in a cell range row by row
    By Learner_Mike in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 07-08-2018, 09:28 PM
  2. [SOLVED] Macro to sort data within a date range
    By jackyung in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-14-2018, 01:20 AM
  3. [SOLVED] Macro to sort data range
    By roversfan09 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-04-2016, 01:25 PM
  4. [SOLVED] Macro who copy the data from one sheet to another sheet basis of 2 other column cell value
    By Manish_Gupta in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-23-2015, 12:47 PM
  5. [SOLVED] Macro who copy the data from one sheet to another sheet basis of 2 other column cell value
    By Manish_Gupta in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-23-2015, 09:49 AM
  6. Filter& Sort on Cell basis
    By Parth007 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-26-2015, 04:03 AM
  7. Sort rows in another sheet on the basis of a column cell values
    By ROHAN999 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-02-2013, 02:52 PM

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