Autosort numbers from low to high

**Zyphon** · 02-05-2008, 07:49 AM

Hi Guys,

I have a spreadsheet with a random number generator and I wondered how I could sort these numbers lowest to highest.

Cells A2 to A11 contain the random number formula, Cells B2 to B11 contain the control so that no duplicates are generated.

What I need is a formula in Cells C2 to C11 to now sort the number in ascending numerical format.

Can anyone help me please?

Thanks in advance.

**EdMac** · 02-05-2008, 08:06 AM

Hi Michael

Put this in C2

=Large(B2:B11,10)

This will give you the smallest

In C3
=Large(B2:B11,9

Will give the 2nd smallest
etc.

Ed

Edited to correct cell refs and change order from decending to ascending

**Zyphon** · 02-05-2008, 08:17 AM

Hi Ed,

Thanks for your help.

I take it you meant =Large(A2:A11,1) in Cell C2?

This not seem to be working correctly sometimes it has duplicate numbers and other time it comes up with numbers that are not even in the random generated cells.

Ideally this is what I need say Cells A2 to A4 had these values: A2=25, A3=40, A4=5. Then in C2 to C4 it should have: C2=5, C3=25, C4=40 and so on.

What is the best way to do this?

Thanks.

**EdMac** · 02-05-2008, 09:29 AM

Michael,

Can you post an example of your sheet, you need to zip it and then upload

Ed

**mikerickson** · 02-05-2008, 09:58 AM

One problem is getting the results of the RAND function to stay constant long enough to be sorted or have Large/Small applied to them.

**Zyphon** · 02-05-2008, 10:57 AM

@mikerickson

Yes I think that is the problem, I have uploaded my sheet hopefully you guys may be able to shed some light onto my problem.

Thanks.

**EdMac** · 02-05-2008, 11:37 AM

Hmmmmm

I see what you mean! I'm afraid I can't offer any help - I think mikerickson is right and it might not be possible.

Ed

**Zyphon** · 02-05-2008, 11:58 AM

Originally Posted by EdMac

Hmmmmm

I see what you mean! I'm afraid I can't offer any help - I think mikerickson is right and it might not be possible.

Ed

That's ok, thanks for letting me know and for trying to help.

**Luckierjack** · 02-05-2008, 02:00 PM

I created a file that does an automatic sort every time you press F9 (Calc). I made the sheet calculate manually. It is very simplistic, but I hope it helps.

Also, I hope the upload works, since this is only my second post on the group.

Luckierjack

**ChemistB** · 02-05-2008, 04:33 PM

I would suggest that once you have generated your random numbers, you highlight them, copy and Paste special values so that they are locked in place.

EdMac's formula should work to put them in order.

ChemistB

**ChemistB** · 02-05-2008, 05:01 PM

I just looked at your spreadsheet and there's some definite problems with the formulas. When I updated them with F2 in each cell, they brought back 0.

1. Just because the value showing is a whole number doesn't mean that Excel is looking at a whole number, if you want to round, you'll need to use the ROUND function.

2. I used column B to look to see if there are any duplicate numbers (when using the ROUND function). You can't tell the formula itself to recalculate if there are duplicates (you'd need VBA for that). I think that is what you were trying to do with your IF statement in column A.

3. If there are any duplicate values, then you hit F9 until there aren't any. From that point, I'd copy and paste special column A. Once those are locked, then hit F9 again to see what's in Column C.

I attached my file. Let us know if you have any questions.

ChemistB

**Luckierjack** · 02-05-2008, 08:30 PM

I see your point. That is why I did it to 4 digits. It leaves the chance of a duplicate number to 1 in 10,000.

Luckierjack

**Zyphon** · 02-06-2008, 08:33 AM

@Luckierjack

Thanks for your help and for the attaching your spreadsheet. I like the method you have used but I need only nubers between 1 to 49 so I will have to tweak it.

@ChemistB

Thank you for your input also and for amending my origin, I like what you have done. Is it possible to make Excel recalculate until we have 0 duplicates in Cell B12 or would you need VBA code for that?

Thanks.

**ChemistB** · 02-06-2008, 11:18 AM

You would need VBA code for that. I think it would be a simple "While" loop if you wanted to go that direction.

ChemistB

**Dav** · 02-06-2008, 11:29 AM

If you went down that route it would be just the same as the sheet you have set up, but pressing a button to generate the random numbers

Is this what you want?

Regards

Dav

**Luckierjack** · 02-06-2008, 04:24 PM

My pleasure.

**Zyphon** · 02-07-2008, 07:30 AM

Originally Posted by Dav

If you went down that route it would be just the same as the sheet you have set up, but pressing a button to generate the random numbers

Is this what you want?

Regards

Dav

Hi Dav,

Thank you so much for your input also this is excellent! I appreciate your help this piece of VBA works really well and no dupes in site.

Thanks