word doc to excel and select 10 random cells, how

  • Thread starter Thread starter Patrick Fagan
  • Start date Start date
P

Patrick Fagan

Hello. I have a list in MS Word format of a few hundred TV stations
categorized by state.

1) I would like to import this file, which is in three-column format in MS
Word, into Excel.

2) I would like to have Excel randomly select 10 of these TV stations.

I know #1 is relatively easy, although my importing does not look right when
I attempt it. However, is #2 possible? Does anybody have a macro that will
do this for me?

Here is the link to the MS Word file. It is 96kb:

http://workingpapers.org/tester/TVSTATIONS.doc
 
copying the list sounds like a one time operation. Just select your data, do
edit=>Copy, then go to excel, and paste it into the worksheet. Doing it
from you link resulted in a single column of data.

With the data in Excel (data starts in A1), put =Rand() in B1 then drag
fill down the column.

Now select both columns and do Data=>Sort and use Column B as the first
column. Select the data in A1 to A10.

that gave me:

KMPH
WTTO
WWAC
WNOL
KISU
KOMU
WUFT
WHPX
KTTU
WGCU


resort if you get any state names in the top 10, or remove them before
sorting.

Each time you sort, the numbers generated by rand will be regenerated with a
new set, so you can get a new sort order.
 
Hi and thanks. I can't find the file. Can you send it to me directly through
email?
 
Hello and thank you. I have uploaded my Excel file to this address:
http:/workingpapers.org/tester/tvstations.xls
The file is 193kb.

Here is what you will see and what I need:

Sheet 1 is fine. It is just a common list of the data (TV Stations).
Sheet 2 appears to be fine also. The TV stations stay in alphabetical order
and when I use the Data Sort, just the numbers change. This means each
station has a new random number.....I hope.

Sheet 3 is where I am having all kinds of trouble. You will see just a list
of the TV Stations in alphabetical order because I deleted/cleared my
attempts at trying the RAND formula. This is what I need for Sheet 3:

1) The TV stations need to stay in the A column. When I use Data Sort, I
need them randomly ordered. Because the whole column should be randomly
ordered, I can just count the first 10, 15, or 20 stations that I need
because this will be randomly selecting them.

2) I need them randomly ordered each time I use the Data Sort-column A. For
some reason when I try it using RAND, the stations will sort only one time
no matter how many times I hit Data Sort.

3) I could care less if the numbers for RAND are showing in sheet 3, as I
only care about the TV Stations being randomly sorted. In fact, it would
probably look better if the numbers were invisible.

Thank the both of you (Tom and Zantor) for your valuable assistance.




Tom Ogilvy said:
What help do you need Patrick. You want me to mail you a file with your
data in it? I doubt what was offered was much beyond that.
Click to expand...
 
My goodness. That was a great macro you sent me. You really ought to get
this up on your website or that Excel forum for people to download. I am
going to put it up on mine in a few weeks, but the only people who use my
site are college professors. You aren't going to get much exposure from my
site!

If anybody wants this xls file that includes a macro to sort randomly a list
of 1,260 items by 10, email me and I will send it to you. Make sure your
subject line matches the one in this post.
 
Back
Top