Listing numbers from a column without organizing them

[leprkon]

I am not sure if my title made sense, but basically I was wondering if there is a function other than SMALL that will list the numbers out for me without organizing them from smallest to largest.

Please Login or Register  to view this content.

This is the formula that I am using right now, but SMALL will list the numbers out in the array from smallest to largest. Is there a way to list them in order of appearance from column E?

Thanks ahead of time! I am kind of a noob when it comes to excel.

[FlameRetired]

Could you upload a sample file?

[leprkon]

Could you upload a sample file?

The VBA work around is kind of a pain, and I am not very good at coding....

haha, it would be cool if I could have the numbers generated in column E be selected randomly and displayed in a ListBox somehow. That is what I am attempting to do with VBA. I am trying to do a raffle, and having the numbers show up numerically would kindof defeat the purpose of doing the raffle.

I could either go with the VBA route, or figure out a way to have the numbers line up in a random order from column E.

I don't know if I am making sense...this is all a bit over my head!

Nonetheless, here is the file that I am working with!
musigma2.xlsx


Basically, I have the random numbers generated, but I wanted to select from them randomly, or list them randomly...

[FlameRetired]

The explanation is confusing. Are you wanting to extract numbers from column E and make a consolidated list somewhere?

[leprkon]

Sorry, I guess I am not too good at explaining this.

Let's start over - I have the list of numbers randomly generated in column E, but they aren't necessarily random in that they are ordered sequentially. Would there happen to be a way to list the numbers in a random order? The numbers are already listed in a dimensional array using this formula -

=IF(F1+$Z$2>$B$5,"",SMALL($E$1:$E$1000,F1+$Z$2))

but when I list it that way, it is listed in order of their appearance.

I guess the majority of the confusion is that I had made changes to what I was originally working with since the first post. As it stands, the excel file that I attached to my previous post is what I am trying to work with.

[FlameRetired]

OK. Let's try something different. This uploaded file has a column G of RAND() numbers; next to that is a column of numbers 1-16; with both

selected/highlighted and the active cell G1, repeatedly click on one of the sort A-Z or Z-A buttons. This will re-randomize column G and carry the

randomized sort over to column H. Is this something you can use for a starting point?

[AlanY]

i took the liberty to change your formula similar, hope that meet your requirement.

[leprkon]

OK. Let's try something different. This uploaded file has a column G of RAND() numbers; next to that is a column of numbers 1-16; with both

selected/highlighted and the active cell G1, repeatedly click on one of the sort A-Z or Z-A buttons. This will re-randomize column G and carry the

randomized sort over to column H. Is this something you can use for a starting point?

i took the liberty to change your formula similar, hope that meet your requirement.

Thanks Flame! I had actually started doing that also! I appreciate it though!

Alan! You did it! That was exactly what I was looking for! I had never used INDIRECT before, but I am glad that you were able to apply it to my table so well! I appreciate the help!

Thanks again Flame and Alan!!

[AlanY]

you're welcome

[FlameRetired]

You're welcome.

[FlameRetired]

i took the liberty to change your formula similar, hope that meet your requirement.

Good solution AlanY. Very slick!!