Hey guys, thank you so much for your help in the forum.
I have two columns to sort, one is group number and another is serial number:
Group Serial Number
1 10
1 2
1 88
2 98
1 50
2 70
2 30
And I wish to arrange the serial number column into a separate column just like the following using only excel formulas:
Arrangement
2
10
50
88
30 (we have reached group 2 now)
70
98
I have been googling high and low and I haven't been able to find a solution. Anyone can show me the light?
Select your data and in Data tab click Sort button.
You can add more levels.
Untitled.jpg
Sorry but I want to do it using formulas. I know this thing exists, but for my research I need the data to be sorted immediately without manual sorting.
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).
I think the above data sufficiently demonstrates my need. Basically I cannot change the order of the data given. I can only rearrange the second column in the desired order in another column.
Thank you!!!
I think the above data sufficiently demonstrates my need. Basically I cannot change the order of the data given. I can only rearrange the second column in the desired order in another column.
Thank you!!!
Here's a link to the file:
https://www.dropbox.com/s/sqfdzummiv...cing.xlsx?dl=0
Guys, help?
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!!!
Last edited by ziwang; 04-01-2016 at 06:07 AM.
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 parttakes:Formula:Please Login or Register to view this content.
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.
Last edited by zbor; 04-01-2016 at 06:39 AM. Reason: typo
here is what might be simpler. does use a helper row. Result in col.E
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks