Formula to sort items based on two levels of detail (e.g. group no. and then serial no.)

Select your data and in Data tab click Sort button.
You can add more levels.

Untitled.jpg

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

We were waiting for example.
Please attach it within forum.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

Here is solution:

Formula:

Please Login or Register  to view this content.

confirmed with ctrl+shift+enter (not just enter)

Thank you! Allow me to explain the formula for the benefit of others:

{=IFERROR(

INT(1000*MOD(SMALL(

IF($A$3:$A$1000<>"",$A$3:$A$1000+$B$3:$B$1000/1000+ROW($B$3:$B$1000)/1000000)

,ROW(A6)),

1)),"")}

$A$3:$A$1000+$B$3:$B$1000/1000+ROW($B$3:$B$1000)/1000000 is a formula that generates a real number. You will get a result in the form of an array table that looks like below:

1.0120030000
1.0210040000
1.0240050000
1.0240060000
2.0210070000
1.0240080000
1.0280090000
1.0330100000
1.0400110000
1.0490120000
2.0220130000
2.0240140000
2.0260150000
2.0270160000
2.0310170000
2.0320180000
3.0120190000
3.0230200000
2.0390210000
2.0410220000
2.0500230000
3.0260240000
3.0270250000
3.0290260000
3.0320270000
3.0420280000
3.0470290000

The IF wrapper helps ensure that the cell is calculated ONLY when the cell is not blank

The SMALL function returns the nth row of the function, according to the order of the result you want your data displayed

The MOD function strips the group number at the front of each number in the array (e.g. "3" in "3.0470290000" and leaves ".0470290000")

The result (e.g. ".0470290000") is multiplied by 1000 to get the "47" out of the decimals

The INT function strips the decimals away from the 47

IFERROR takes care of any remaining errors in the cell

Thank you so much!!!

Well done with analyzing ziwang and thank you for explaining it to others.

Also not that in case of serial numbers over 1000 a dividing need to be modified.

Here are some more additional explanations:

this part

Formula:

Please Login or Register  to view this content.

takes:

A column is main group of sorting that will sort 1,2,3... in a ascending order.
B column is second criteria of sorting.
ROW(B3:B1000) is used in case of duplicates.


Therefore, those numbers:

3 47
3 47

can be considered as two numbers because they are in different rows.

final number will look as 3.0470290000 where 29 is 29th row.
That's why it's striped as explained above.