Hello, This is my first message in this forum.
I am having a list something like :-
1
2
2
3
5
5
5
5
5
6
7
8
8
8
8
8
8
8
8
8
9
10
10
10
10
10
10
10
11
12
12
12
I want to sort it in ascending order but in group of 5 Like:
1
2
3
4
5
2
3
4
5
6
2
5
7
8
9
2
8
10
11
12
8
10
12
10
12
10
12
12
12
12
Please guide me, i am unable to think for a solution, Regards
Welcome to the forum.
There are instructions at the top of the page explaining how to attach your sample workbook.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
If you do post a workbook make sure your example is valid.
With the numbers you posted the ranked values contains numbers that are not in the original set. e.g. No 4 in original, 3 appears twice in rank but once in data set.
Thanks Andy for your reply, my workbook is big so just for explaining my problem I created a small example.
"3 appears twice in rank but once in dataset"----as i said i created the example manually so that was a mistake from my side, i am just trying to express my requirement
Create a small, sample workbook. Not hard to do.
Thanks for your advise. I have made a sample workbook & attached it, "sort.xlxs". Please go through it.
Regards.
Here is a vba solution using the UNIQUE and SORT formula. Not sure if you have those if you are really using 2013
It uses a column L and N to manipulate the data.
I think we need more information about the rules for populating the buckets.
Please Login or Register to view this content.
For example if there were seven 2s how would they get distributed? You would get one instance in each bucket but where would the seventh occurance end up?
With formula solution
D1=b3
D2:
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).Please Login or Register to view this content.
Drag down
Quang PT
I'm not sure that this formula based solution has much to recommend it over bebo021999's solution, other than possibly my step by step approach may be easier to understand, but having spent the time I'll post it anyway
The apparent algorithm that maps the input to the expected output:
Repeat until all numbers allocatedMove to the top of the list.Repeat until 5 numbers have been selected or the end of the list is reachedEndPick a number excluding any previously picked number and skipping repeated numbersEnd
There needs to be at least as many helper columns as there are returns to the top of the list in the above algorithm. For this data set 10 columns are needed. In D3 copied across and down to O32:
From these helper cells the required output can be generated by reading down the first column then the second column then the third and so on.Formula:
Please Login or Register to view this content.
Col-Q takes each helper column in turn and consolidates them into a single column. In Q3 copied down at least as far as the number of inputs (30) times the number of helper columns (10) plus 2 = row 302:
Formula:Please Login or Register to view this content.
Finally col-S removes the blanks from col-Q and reproduces the expected output. In S3 copied down to S32:
Formula:Please Login or Register to view this content.
See the attached workbook.
Last edited by GeoffW283; 09-29-2020 at 12:09 PM.
Geoff
Did I help significantly? If you wish, click on * Add Reputation to say thanks.
If your problem has been resolved please select ?Solved? from the Thread Tools menu
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks