We help IT Professionals succeed at work.

2003 Excel formula

dddw
dddw asked
on
Hi,

I need help sorting the attached spreadsheet.  I need records to have the dates in sequential order.  For example, record 8 on the attached spreadsheet has two dates that are out of sequential order.  It should read 3/22/1996, 11/16/2000, 3/7/2002, 2/17/2005, 12/13/2007, & 9/9/2010.  The dates in rose color are out of order.  I've highlighted 3 records that have this problem as examples but it occurs throughout the data and appears random.

I'm sure there is an Excel formula that can correct this but not sure how.  Please help!

Thanks!
test2.xls
Comment
Watch Question

Mechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
I put the following formula in cell K2 and copied over to column Q and then down. It puts the dates in correct order (after you format the results as dates).
=IF(COUNT($D2:$J2)<COLUMNS($K2:K2),"",SMALL($D2:$J2,COLUMNS($K2:K2)))

You can prove this while the entire range is selected using conditional formatting using a "formula is" criteria of:
=AND(K2<>"",L2<>"",K2>L2)               pick a highlight color to show out of order dates

Author

Commented:
You are the greatest!  Thank you!!!!
Gerwin Jansen, EE MVETopic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2016

Commented:
Create a macro like this, you may have to change the range that I've set (based on your sample xls).

Sub SortRowsLtoR()

Dim RgToSort As Range
Dim RgRow As Range

Application.ScreenUpdating = False

Set RgToSort = Range(Range("D2:J2903"), Range("D2:J2903").End(xlDown))

For Each RgRow In RgToSort.Rows
    RgRow.Sort Key1:=Range(RgRow.Item(1).Address), Order1:=xlAscending, Orientation:=xlLeftToRight, OrderCustom:=1
Next

End Sub

Open in new window


Run the macro and all your rows are sorted from left to right.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.