Discussion Sort decending/acending list with macro in Excel
https://techcommunity.microsoft.com/t5/excel/sort-decending-acending-list-with-macro/m-p/3057661#M129390
<P>Hello</P><P>We are trying to do some calculations but something goes wrong. In column J you can see some numbers and in some cells we have equal numbers. We want to sort the numbers in column L and K related to the numbers in column J. We want to run the calculations first in J followed by L and K. The numbers in L and K should sort ascending when we run our macro. If you look in the cells in J39 and J40 we have the same numbers (17). But the numbers in L39, L40 should sort ascending. The right calculation should give 2 in L39 and 4 in L40. For K 39 should that be 4 and and 15 in K40. The same problem occurs when we have several equal numbers in column J.</P><P> </P><P>I hope someone can take a little time to solve our problem</P><P> </P><P>Best regards </P><P> </P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Skärmklipp1.PNG" style="width: 939px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/338725i746F7F7302A0C36A/image-size/large?v=v2&px=999" role="button" title="Skärmklipp1.PNG" alt="Skärmklipp1.PNG" /></span></P><P> </P><P>Sub sortera()<BR /><BR />Dim counter As Integer</P><P>counter = 3<BR />For i = 2 To 4<BR /><BR />For i2 = 3 To 15<BR /><BR />Cells(counter, 13) = counter - 2<BR />Cells(counter, 10) = Cells(i2, i)<BR />Cells(counter, 11) = i2<BR />Cells(counter, 12) = i<BR /><BR />counter = counter + 1<BR />Next i2<BR /><BR />Next i<BR /><BR />Call SortMultipleColumns</P><P>End Sub</P><P>Sub SortMultipleColumns()</P><P>Range("J3:L41").Sort Key1:=[J3], Order1:=xlDescending, _<BR />Key2:=[L3], Order2:=xlAscending, _<BR />Key3:=[K3], Order3:=xlAscending<BR /><BR />Call placeNewRank</P><P>End Sub</P><P><BR />Sub placeNewRank()</P><P>For i = 3 To 41<BR />Cells(Cells(i, 11), Cells(i, 12) + 4) = Cells(i, 13)<BR />Next i</P><P>End Sub</P>Tue, 11 Jan 2022 19:34:58 GMTaRangerpete2022-01-11T19:34:58ZSort decending/acending list with macro
https://techcommunity.microsoft.com/t5/excel/sort-decending-acending-list-with-macro/m-p/3057661#M129390
<P>Hello</P><P>We are trying to do some calculations but something goes wrong. In column J you can see some numbers and in some cells we have equal numbers. We want to sort the numbers in column L and K related to the numbers in column J. We want to run the calculations first in J followed by L and K. The numbers in L and K should sort ascending when we run our macro. If you look in the cells in J39 and J40 we have the same numbers (17). But the numbers in L39, L40 should sort ascending. The right calculation should give 2 in L39 and 4 in L40. For K 39 should that be 4 and and 15 in K40. The same problem occurs when we have several equal numbers in column J.</P><P> </P><P>I hope someone can take a little time to solve our problem</P><P> </P><P>Best regards </P><P> </P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Skärmklipp1.PNG" style="width: 939px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/338725i746F7F7302A0C36A/image-size/large?v=v2&px=999" role="button" title="Skärmklipp1.PNG" alt="Skärmklipp1.PNG" /></span></P><P> </P><P>Sub sortera()<BR /><BR />Dim counter As Integer</P><P>counter = 3<BR />For i = 2 To 4<BR /><BR />For i2 = 3 To 15<BR /><BR />Cells(counter, 13) = counter - 2<BR />Cells(counter, 10) = Cells(i2, i)<BR />Cells(counter, 11) = i2<BR />Cells(counter, 12) = i<BR /><BR />counter = counter + 1<BR />Next i2<BR /><BR />Next i<BR /><BR />Call SortMultipleColumns</P><P>End Sub</P><P>Sub SortMultipleColumns()</P><P>Range("J3:L41").Sort Key1:=[J3], Order1:=xlDescending, _<BR />Key2:=[L3], Order2:=xlAscending, _<BR />Key3:=[K3], Order3:=xlAscending<BR /><BR />Call placeNewRank</P><P>End Sub</P><P><BR />Sub placeNewRank()</P><P>For i = 3 To 41<BR />Cells(Cells(i, 11), Cells(i, 12) + 4) = Cells(i, 13)<BR />Next i</P><P>End Sub</P>Tue, 11 Jan 2022 19:34:58 GMThttps://techcommunity.microsoft.com/t5/excel/sort-decending-acending-list-with-macro/m-p/3057661#M129390aRangerpete2022-01-11T19:34:58ZRe: Sort decending/acending list with macro
https://techcommunity.microsoft.com/t5/excel/sort-decending-acending-list-with-macro/m-p/3057743#M129402
<P><LI-USER uid="1272378"></LI-USER> </P>
<P>Could you attach a small sample workbook or make it available via OneDrive, Google Drive or similar, so that we can experiment?</P>Tue, 11 Jan 2022 21:02:18 GMThttps://techcommunity.microsoft.com/t5/excel/sort-decending-acending-list-with-macro/m-p/3057743#M129402Hans Vogelaar2022-01-11T21:02:18ZRe: Sort decending/acending list with macro
https://techcommunity.microsoft.com/t5/excel/sort-decending-acending-list-with-macro/m-p/3059970#M129530
<P><LI-USER uid="127945"></LI-USER> does this work?</P><P> </P><P><A href="https://1drv.ms/u/s!Akq4T6vFl0myaQGCGq3ITdP1EeE?e=peaK4L" target="_blank">https://1drv.ms/u/s!Akq4T6vFl0myaQGCGq3ITdP1EeE?e=peaK4L</A></P><P> </P><P>Best regards</P><P>Peter</P>Wed, 12 Jan 2022 20:46:33 GMThttps://techcommunity.microsoft.com/t5/excel/sort-decending-acending-list-with-macro/m-p/3059970#M129530aRangerpete2022-01-12T20:46:33ZRe: Sort decending/acending list with macro
https://techcommunity.microsoft.com/t5/excel/sort-decending-acending-list-with-macro/m-p/3060071#M129536
<P><LI-USER uid="1272378"></LI-USER> </P>
<P>I have downloaded your sample workbook - thanks!</P>
<P>I see the problem; it appears to be a bug in the way Excel handles a mix of ascending and descending sort orders. I don't see an easy solution - let's see if someone else comes up with a bright idea.</P>Wed, 12 Jan 2022 22:14:01 GMThttps://techcommunity.microsoft.com/t5/excel/sort-decending-acending-list-with-macro/m-p/3060071#M129536Hans Vogelaar2022-01-12T22:14:01ZRe: Sort decending/acending list with macro
https://techcommunity.microsoft.com/t5/excel/sort-decending-acending-list-with-macro/m-p/3063938#M130110
<LI-USER uid="127945" login="Hans Vogelaar"></LI-USER><BR />My father found a solution. He found that it is not always enough to change from text to numbers via Format Cells. At first the list was in TEXT-formatted numbers and he changed these with Format Cells for Excel to format them as real numbers, but it was not enough. He had to use a formula called NUMBER VALUE for excel to accept the numbers, if you run the table through that formula it works.Mon, 17 Jan 2022 20:56:57 GMThttps://techcommunity.microsoft.com/t5/excel/sort-decending-acending-list-with-macro/m-p/3063938#M130110aRangerpete2022-01-17T20:56:57Z