How do I replace number with predefined text?

  • Thread starter Thread starter SM Mister Moe
  • Start date Start date
S

SM Mister Moe

I am essentially trying to randomly order 50 words from a predefine
base list of 400. I have used the following formula:

=ROUND((RAND()*400 + 1),0)

in 10 rows by 5 columns to generate 50 random numbers between 1 an
400. My question is how should I input my base list of 400 words int
Excel and then replace the random number with the correspondin
predefined word? So if the random number I generated was 247, and 24
corresponded to the word "cat", then "cat" would be displayed in m
table of 50 random words. If this is not the best approach to th
problem, I would be very happy to hear alternative solutions. An
assistance would be greatly appreciated.

Mo
 
You're not far off now. In cells A1:A400 on your sheet put 1,2,3,4,5......400

In B1:B400 put the associated words, and then just put your formula in a VLOOKUP
statement, eg:-

=VLOOKUP(ROUND((RAND()*400 + 1),0),A1:B400,2,0)

Other ways of doing it using OFFSET, eg:-

With just the words you want in cells A1:A400

=OFFSET(A1,ROUND((RAND()*400 + 1),0)-1,0) will offset from cell A1 by the amount
of the random number and give you whatever word is in that cell.
 
Try this simple set-up?

Put your base list of 400 words in A1:A400

Put in B1: =RAND(), copy down to B400
Name the range B1:B400 as : TBL

Select C1:C400

Put in the formula bar: =RANK(TBL,TBL)
Hold down Ctrl + Shift keys, press Enter
(It's an array formula)

Done correctly, Excel will wrap curly braces around
the formula, viz: {=RANK(TBL,TBL)}

In C1:C400 will be a random shuffle of the numbers 1 - 400
without repeats

Put in D1: =OFFSET($A$1,C1-1,0)
Copy D1 down to D400

In D1:D400 (a 1 col x 400 row grid) will be
a random shuffle of all the 400 words in A1:A400 without repeats

Put in E1: =INDIRECT("D"&5*ROW()-8+COLUMN()-1)
Copy E1 across to I1, then copy down to I80

The above will re-arrange what's in D1:D400
into a 5 col x 80 row grid in E1:I80, transposing
5 consecutive col cells from D1:D400 at a time "row-wise"

So in E1:I80 will be a random matrix
of all the 400 words in A1:A400 without repeats

Pressing F9 will regenerate another random shuffle in both grids above

Now you have a wide range of options with which
to select your random order of 50* words from the base list of 400.
*or for that matter, any number of words from 1 to all 400 !

For example, if you want the 50 words to be in a 5 col x 10 row grid,
just take any 10 consecutive rows from within E1:I80.

Or take any 50 consecutive col cells from within D1:D400.
 
Ken-

I just tried your solution, but I keep getting a ton of #N/A error. I
have 1 through 400 in A1 to A400, my words in B1 to B400, and pasted
your VLOOKUP formula in C1 to C400 but every other one is a #N/A
error.

Any suggestions would be very helpful.


Moe
 
Max-

I placed my words in A1:A400, =RAND() in B1:B400, and set up tha
{=RANK(TBL,TBL)} in C1:C400 just like you said. In D1:D400 I set u
=OFFSET($A$1,C1-1,0) also per your instructions and I got a rando
order of my 400 words. I copied =INDIRECT("D"&5*ROW()-8+COLUMN()-1
into E1 across to I1 and then highlight across E1 to I1 and copied i
down so the formula stretched from E1 to I80 and I got the result of
rows of 80 of my words in random order, without repeats. However, on
curious thing occur. In I80 and only I80, the cell value was 0. I wa
puzzled to say the least. Your method was very helpful. Any furthe
assistance would be great.


Thanks,

Mo
 
On second thought, scratch that. It seems I accidentally pasted on
cell to many. Disregard my last post, and thanks again for your hel
Max.


Sincerely,

Mo
 
Max-

I really like your solution because it does not cause repeats in th
data to be generated. I was able to figure out what the differen
numbers corresponded to in some of the simpler solutions but I wa
wondering if you could tell me where I might find the syntax for th
method you employed?

Thanks,

Mo
 
Moe, you're welcome ! Your feedback is appreciated.

As for your follow on Q, maybe you could try
checking up on OFFSET in Excel's Help
(via Help > Contents & Index > Index tab > Type: OFFSET)

-----------------------------------------
Syntax (From Excel Help)
OFFSET(reference,rows, cols,height,width)
-----------------------------------------

Here's a brief explanation:

Let's take D1: =OFFSET($A$1,C1-1,0)

The OFFSET formula in D1:

uses cell A1 on the same sheet as the anchor cell (starting reference),
viz. it's 1st param (Reference) is $A$1

The 2nd param (Rows) is the number returned by (C1-1)
which tells it to go up or down a certain number of rows from the anchor
cell A1
and return the value from the cell there

For example:

if (C1-1) evaluates to 2, it'll go 2 rows down from cell A1 ("+" values
means "down")
viz. it'll go down to cell A3 and return the value in A3 (A1 itself is in
row "zero")

if (C1-1) evaluates to -1, it'll go 1 row up from cell A1 ("-" values means
"up")
viz. this means an error, since A1 is already in the first row (you'll get
#REF! error)

if (C1-1) evaluates to 0, it'll just stay in cell A1 (row "zero") and return
value in A1

[ "C1-1" is used instead of "C1" alone in the Rows param
to ensure that "zero" movement, i.e stay and return cell A1 is covered ]

So what we're simply asking OFFSET in D1 to do is just to go up or down col
A
according to the value returned by (C1-1), and give us the value there.
(Remember we've put the 400 words in A1:A400)

Likewise for the rest of the OFFSETs in D2:D400

And since in C1:C400 we have a random shuffle of the numbers 1 - 400
without repeats, this means that the OFFSETs in D1:D400 will return,
between them, the full lot of the 400 words we have in A1:A400
 
Let's take D1: =OFFSET($A$1,C1-1,0)

Sorry, should have mentioned this in the reply earlier:

The zero in the 3rd param (Cols) for the OFFSET in D1
tells it to remain / stay in the same column as the anchor cell A1,
ie stay in col A.

The Col param operates in the same manner as the Rows param
but for "horizontal" movement relative to the anchor cell.
(Rows param is for "vertical" movement relative to the anchor cell)

Col param
 
Max-

Wow! Your explanation made A LOT more sense then the help file alone.
I appreciate your assistance in this matter. I feel confident I will
be able to implement this concept in future spreadsheet projects with
varying data dimensions without difficulty.


Thanks again,


Moe
 
Re: your queries on Ken's 2 formulas ....
=VLOOKUP(ROUND((RAND()*400 + 1),0),A1:B400,2,0)
=OFFSET(A1,ROUND((RAND()*400 + 1),0)-1,0)
Click to expand...

You've got to fix the table_array in Ken's VLOOKUP,
i.e. make it *absolute* refs: $A$1:$B$400
before you copy the VLOOKUP down or across

Otherwise the table_array references will change relatively when
you copy down / across, giving some very queer results !
(It's quite a common error - happens to me too! <g>)

Alternatively, use a named range for the table array in A1:B400,
e.g.: MyTable so it becomes
: =VLOOKUP(ROUND((RAND()*400 + 1),0),MyTable,2,0)

Similarly, fix the anchor reference in the OFFSET,
i.e. make it: $A$1
before you copy the OFFSET down or across

--
hth
Max
-----------------------------------------
Please reply in thread
Use xdemechanik <at>yahoo<dot>com for email
--------------------------------------------------------
SM Mister Moe > said:
Ken-

I just tried your solution, but I keep getting a ton of #N/A error. I
have 1 through 400 in A1 to A400, my words in B1 to B400, and pasted
your VLOOKUP formula in C1 to C400 but every other one is a #N/A
error.

Any suggestions would be very helpful.


Moe
Click to expand...

Ken Wright said:
You're not far off now. In cells A1:A400 on your sheet put 1,2,3,4,5......400

In B1:B400 put the associated words, and then just put your formula in a VLOOKUP
statement, eg:-

=VLOOKUP(ROUND((RAND()*400 + 1),0),A1:B400,2,0)

Other ways of doing it using OFFSET, eg:-

With just the words you want in cells A1:A400

=OFFSET(A1,ROUND((RAND()*400 + 1),0)-1,0) will offset from cell A1 by the amount
of the random number and give you whatever word is in that cell.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

-------------------------------------------------------------------------- --
Best wishes to all, and hope for a good New year :-)
--------------------------------------------------------------------------
Click to expand...
--
 
Sorry - Didn't get here yesterday - As Max said, I had forgotten to fix the
reference with $ signs.
 
Thanks so much! I just checked back here to refresh on the stuff yo
guys were kind enough to bring up, I'm working on another simila
project. I added the dollar signs to Ken's formula as Max suggested,
am using the following formula pasted down 400 cells in column C:

=VLOOKUP(ROUND((RAND()*400 + 1),0),$A$1:$B$400,2,0)

I am not getting as many errors. However, every once in a while a #N
shows up. I am thinking it may have to do with the fact the RAND i
present form could generate a 401 result (I think.) I was going t
solve it simply by changing 400 to 399 but I remembered reading tha
this would be flawed since the probability for a 1 or 400 result i
much lower than the other 398 numbers. How would you sugges
incorporating that INT function? Currently I have the numbers
through 400 in column A and my word list in column B. I am als
curious if this method does in fact rule out all possibilities o
repeat words in the random word list?

Thanks,

Mo
 
Moe,

Think your queries are already covered in JE's reply
to you in another thread:

Link: http://tinyurl.com/332t3

where it is stated that duplicates will not be prevented
(even with the use of INT function)
I just checked back here to refresh on the stuff you
guys were kind enough to bring up
Click to expand...

Besides using the excellent Google Search 6.0
from MVP Ron de Bruin:
http://www.rondebruin.nl/Google.htm

I'd usually & immediately copy > paste useful stuff
from the newsgroups into my "own" books
(can be word docs or excel files) for ease of reference.

Also good as a "just-in-case" the newsgroups' gems
somehow do not get into google's archives due to whatever reason
(the potential loss would be simply too awful to contemplate!)
 
I see you are right Max. Thanks for the reply. I have found that your
method is by far the best for my purposes, however, I am curious where
I might find an explanation for the use of the INDIRECT function, I
tried figuring it out from Excel help, but it wasn't very descriptive
if you know what I mean.

Thanks again.

Moe
 
Ok, here's an attempt to put some thoughts to paper.

Let's say we have in col D, row1 downwards
20 numbers 1,2,3,4,5,6 ... 20

And we want to re-arrange these numbers in col D
in a row-wise manner, say, 5 at a time
into cols E - I, so that it appears in E1:I4 as

1,2,3,4,5
6,7,8,9,10
11,12,13,14,15
16,17,18,19,20

Doing this "manually" via direct link formulae would
require putting:

in E1: = D1
in F1: = D2
in G1: = D3
in H1: = D4
in I1: = D5

in E2: = D6
in F2: = D7
in G2: = D8
in H2: = D9
in I2: = D10

and so on

Using INDIRECT() allows a way to frame something up in the start cell E1
which will give the same result as if we had used the link formula : =D1

If we put in E1: =INDIRECT("D1"), it's equivalent to putting in E1: =D1

But to enable us to simply copy E1 across and down to get the required
arrangement, another formula is needed inside INDIRECT(),
one which will increment the number next to "D":

In steps of 1 as we copy across columns, and,
In steps of 5* as we copy down rows

*This usually implies use of the "5" as the multiplier for the ROW()
i.e. we got to use 5*ROW() ....

The "D" is a constant here, so we don't have to worry about it

That's where ROW() and COLUMN() comes in, for use as incrementers,
either on its own, or more often, in various combinations of both together

Experimentation is needed to get a correct "magic" combination
of ROW() and COLUMN() which will fit the bill nicely <g>

Some points we can bear in mind:
-----------------------------------------
ROW() returns the row # of the cell it is in
ROW() remains constant as we copy across the row
ROW() is useful as an incrementer as we copy down the column
(If the step as we copy down is 5, use this "5" as a multiplier

COLUMN() returns the column # of the cell it is in
COLUMN() remains constant as we copy down the column
COLUMN() is useful as an incrementer as we copy across the row

So as a first try, we could say, put in E1: =INDIRECT("D"&COLUMN()-4)
which still returns the same result as if we had put in E1: =D1,
but allows us to copy E1 across,
with the resulting cell references nicely incremented: D1, D2, D3, D4 & D5
inside INDIRECT()

As our start cell E1 is in col E (i.e. col#5), "COLUMN()-4"
is used to evaluate and return "1" (We have to subtract "4")

Now we can copy E1 across to I1 and get the results for the first 5 cells
but we still can't copy down to get the rest.

Back to the excel sheet for further experimentation.

We'll throw in use of ROW() and COLUMN() together in various combinations
until finally, voila! we hit one combo which seems to fit the bill nicely
viz.: =5*ROW()-8+COLUMN()-1

That's how we'll finally arrive at a workable formula for E1:
=INDIRECT("D"&5*ROW()-8+COLUMN()-1)
which, when copied across to I1, then down to I4
would give us exactly what we're after

Hope the above gives you some ideas on use of INDIRECT()
and on the use of ROW() and COLUMN() inside INDIRECT
as incrementers

Check up more examples on use of INDIRECT() in Google's archives

Play & experiment around. You'll soon get the hang of using it !
----------------------------
In case you're interested, here's a couple of links
to some of my previous suggestions to posts
which use INDIRECT():

http://tinyurl.com/23v8c
http://tinyurl.com/2rljj
http://tinyurl.com/2242e
 
Back
Top