Strangeness when randomizing a column of text

[Pallando_II]

I'm working with two columns: Col-A contains the formula "=1000*RAND()", formatted to produce integers from 100 to 999. Col-B contains the names of the components of my mp3 collection, one file per cell and about 350 cells running down Col-B.

My objective is to easily create randomized playlists, (*.m3u).

With Excel 2007, I select the two columns A & B and then run through: DATA/SORT & FILTER/SORT AZ: Sort by (Col-A); Sort on (VALUES); Order (SMALLEST to LARGEST). Seems simple enough.

The results are not what I expected: In Cols A&B are newly-randomized columns, and every time I "DATA/SORT" both Cols-A&B re-randomize in no particular or repeating order. I should be happy because I have an easily created playlist, but I expected B to be unchanged until I sort them again together using the newly random-numbered Col-A as the ordering column on the alphabetized (or not) Col-B.

Can someone tell me what's happening? Why do both columns re-randomize with every successive "DATA/SORT"?

[tamthat]

If you don't want B to be unchanged, you should be "sort & filter" only column A!

[Pallando_II]

Thanks for your answer. Your double negative threw me until I realized you likely meant "If I don't want B to be CHANGED..." But my question states that I WANT B to be changed, randomizing my list. And in fact, it DOES change - as desired - but why?

Although Cols A & B are selected, I'm sorting (Col-A), on (VALUES), (SMALLEST to LARGEST). Recall Col-A contains "=1000*RAND()" and Col-B is an alphabetical list. With this setup, I wouldn't expect Col-B to change until a second SORT.

[shg]

Column A recalculates after the sort. If you don't want that to happen, you'd need to copy and paste as values.

[Pallando_II]

YES! That was it. I figured it out just before I read your post. After a random recalc, Col-A of the last row was empty. So I just put a "1" in. After the next recalc, that line appeared at the top in row 1. Conundrum solved and rep added. Thanks for your time and energy.

[shg]

You're welcome.