Impossible to Sort Solved

Question

Hello.  One of the toughest problems I've encountered in Excel:  I want to sort the dates in column B chronologically. Having dates from before 1900 in column A, I applied the following formula that was given to me, to the entire column. =IF(ISNUMBER(A1520),DATE(YEAR(A1520)+1000,MONTH(A1520),DAY(A1520)),(LEFT(A1520,2) & MID(A1520,3,LEN(A1520)-7) & VALUE(RIGHT(A1520,4))+1000)*1)  This allows me to obtain a "sortable" date in column B   However, when I try to sort, Excel gives me the message:     I lost a lot of hours yesterday trying to find an answer, read tons of posts, with no result.  My cells are not merged Their size is identical since I set the row height for the entire table upon creation.    Configuration: Windows / Firefox 40.0  -- Windows 8.1 Office 2013

Frenchie83 · Answer

Hello Excel doesn't invent anything. Would you mind merging cells A1 and B1 for the sake of readability to make just one and naming it "Date" just in case?

Vaucluse · Answer

Good morning I think we should insist that you check if any cells are merged, because Excel is quite precise in its message! To do this, select the entire sheet using the box with the white triangle to the left of the column header and unmerge everything; restart the sort and see Sincerely  -- To err is human, to persist is diabolical

Viking58 · Answer

Here are my macros.  Lines 1 and 2 are titles. Column A contains dates, some of which are before 1900, in the format Tuesday, August 25, 2015. Column B contains the dates + 1000 years in the format 28/08/2015. No calculations in the sheet.  BOTTOM BORDER CELL  Public Sub OK() Dim range As Range Set range = Range("A3:G3000") range.Borders(xlEdgeBottom).LineStyle = xlContinuous range.Borders(xlInsideHorizontal).LineStyle = xlContinuous End Sub  GO TO START OF TABLE  Private Sub CommandButton1_Click() Application.Goto Range("A1"), True End Sub  GO TO END OF TABLE  Private Sub CommandButton2_Click() Range("A" & Rows.Count).End(xlUp).Select End Sub  INSERT LINE ABOVE ACTIVE LINE  Private Sub CommandButton3_Click()  ActiveCell.EntireRow.Insert  Rows(ActiveCell.Row + 1).Copy Rows(ActiveCell.Row)  On Error Resume Next  Rows(ActiveCell.Row).SpecialCells(xlCellTypeConstants, 23).ClearContents End Sub  SEARCH  Private Sub CommandButton4_Click() Application.CommandBars("Edit").Controls.Item("Find...").Execute End Sub  SORT BY DATE  Private Sub CommandButton5_Click() ' Sort Macro ' Sort by date  Range("B3:B3000").Select  ActiveWindow.ScrollRow = 6  ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear  ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2"), _  SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal  With ActiveWorkbook.Worksheets("Sheet1").Sort  .SetRange Range("A3:G3000")  .Header = xlNo  .MatchCase = False  .Orientation = xlTopToBottom  .SortMethod = xlPinYin End With End Sub  HIGHLIGHT ACTIVE CELL + COLOR GROUP OF CELLS  Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Application.Intersect(Target, Range("A1")) Is Nothing Then 'Range'  Application.ScreenUpdating = False  ' Clear the color of all the cells  Cells.Interior.ColorIndex = 0  ' Highlight the active cell  Target.Interior.ColorIndex = 8  Range("C1:E1").Interior.Color = 10092543 'Sets the given range to the color: adjust the color with Microsoft Code...'  Range("A2:G2").Interior.Color = 16777164 'Sets the given range to the color: adjust the color with Microsoft Code...'  Range("F1:G1").Interior.Color = 13434828 'Sets the given range to the color: adjust the color with Microsoft Code...'  Application.ScreenUpdating = True End If End Sub  You will certainly discover errors or formulas to simplify...  Thank you all...  -- Windows 8.1 Office 2013

eriiic · Answer

Hello,  if Excel tells you so, it's for a reason. It rarely makes mistakes... If you sort on A:G, select those columns, merge and unmerge to tidy up.  Eric  P.S.: We can ALWAYS upload an example file. Just replace Mr. Martin with Mr. Potato and 12 instead of 150000  By trying continuously, we eventually succeed. So the more it fails, the more chances we have that it works. (the Shadoks) In addition to thank you (yes, it can be done!!!), remember to mark it as resolved. Thanks

Raymond PENTIER · Answer

Send back your second screenshot of the initial post, but also capturing the titles of rows and columns ...  -- It's great, retirement! Especially in the Caribbean ... :-) Raymond (INSA, AFPA, CF/R)

Frenchie83 · Answer

Hello I ran your SORT macro, it lacks the line that applies the sort ".Apply". Since you haven't provided us with any document to rely on for testing, I created a sheet with the 2 columns in A and B just like yours, ran the sorting macro and everything went smoothly. Private Sub CommandButton5_Click() ' Sort Macro ' Sort by date ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange Range("A3:G3000") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub If the error persists on your end and you do not make the effort to provide us with what everyone is asking for (a copy of your sheet without any confidential data) in order to help you, despite all the goodwill of this forum, we will not be able to do much for you. Best regards  PS: Have you copied your sheet (only the data) to another sheet and sorted it?

Impossible to Sort

6 réponses

Texture issues in enshrouded

Canon mb5350 - cartridge replacement impossible

Football manager (club management game)

Select next line csh foreach

Win32 kamso then rootkit detection

Win32:spyware-gen [trj] detected by avast

Unknown video error on the freebox player

Make it or break it

How to open a cbr file

Please log in with manager privileges.