Macro to reorder duplicates (with condition that no two can be after the other)

You may try the following:

Please Login or Register  to view this content.

change the constants at the beginning - column where your dates areL dates = "C"
column where data sits - may be left from column with dates or if dates are leftmost write the same, for instance: fromcolumn = "C"
numcolumns = 5 (so in my example I tested it on data written in A:E). If you have only dates column (no other info associated with date then use 1
and finally: where real information starts - in test file I had no header, so fromrow = 1 was just what was needed.

Enjoy

PS. I tried it for several cases and the final message is added just for very weird data (like half of the set one date, second half second date).
Otherwise the macro is reasonably effective and quick. Of cousre for complicated workbooks it could be wise to switch Excel to manual recalculation mode before the macro and back to automatic after.
Also for really big datasets transfering all data into array and then swaping array rows would probably improve speed, but you were referring to hundreds, so working within worksheet shall be fine..

Hi John,
Unfortunately ... I think it does not work in some simple cases
see attached test file

Thank you, Kaper! It seems to work, but there were two places where duplicates were still together. I'm pretty sure it was something that I did, but this is what I changed in the code:

Const dates = "A", fromcolumn = "A", numcolumns = 4, fromrow = 1

The data with the dates is in column A, there are 4 columns of data that needs to stay with those dates A:D, the first row has the names of those columns. Did I do this correctly? Thanks for the help.


There were still duplicates in John's approach, but I did not change the code to fit my data so that may be why