Selecting a batch of cells

[NathanC]

I've been doing this manually, too, but every repetitive process should have an automation

Continuing this statistics project, I have my column of numbers from 0001 to 2,858. I'm supposed to randomly select 339 of the cells for a sample.

However, I can't use any of Excel's random functions this time, because they want me to make my selection based on numbers from a random-number table.

So, I will have a list of numbers, like this: 0019 2139 0762 0115 1729 etc.

What I want to do is select and copy exactly those cells. I could search them one by one, but I'd like to just copy/paste the list of random numbers and have them all selected at once--or by batches of 10 or so.

Possible?

[ChemistB]

If you already have the numbers, why can't you just type or paste them in and you'd be finished? Why would you need to pull them out of Column A?

[NathanC]

If you already have the numbers, why can't you just type or paste them in and you'd be finished? Why would you need to pull them out of Column A?

Oops...I forgot to add the important part. The numbers aren't really the important part--they data they number (in the cell to the right) is.

So actually, what I need to do is a Search and Replace to add some symbol in front of the number so I can then Sort, and get my sample of randomly selected data. Does that make sense?

[NathanC]

So I guess the question should be, is there any way of automatically editing those specific cell numbers to identify them by adding a preceding symbol, so I can then do a Sort and get my sample?

[ChemistB]

I would just do a VLOOKUP

If your new list of random numbers is in G1:G339, in H1
=VLOOKUP(G1,$A$1:$A$2857,2, FALSE)
If your numbers in A are stored as Text, try
= VLOOKUP(TEXT(G1,"0000"), $A$1:$2857,2,FALSE)

You can then copy Column H and Paste Special > Values whereever you wish
Would that work for you?

[NathanC]

I would just do a VLOOKUP

If your new list of random numbers is in G1:G339, in H1
=VLOOKUP(G1,$A$1:$A$2857,2, FALSE)
If your numbers in A are stored as Text, try
= VLOOKUP(TEXT(G1,"0000"), $A$1:$2857,2,FALSE)

go

You can then copy Column H and Paste Special > Values whereever you wish
Would that work for you?

Going to give it a try!

[NathanC]

Ok, I'm so noob I didn't even know what VLOOKUP was, but I went and checked it out.

I tried "= VLOOKUP(TEXT(G1,"0000"), $A$1:$2857,2,FALSE)" as you recommended, and it only says the formula contains an error--that was probably something I did wrong, but I don't know what.

Then I decide to test "=VLOOKUP(G1,A1:C2858,2,FALSE)," and that worked wonderfully--from H2 through H9. On H9 it begins to return "#N/A."

Any idea what the problem might be?

[ChemistB]

The error means that the VLOOKUP did not find a match. It could be a few reasons. Based on your formula, we'll start with this...

Okay, you want to lock your range (A1:C2858) in place using $. When you copy formula's from place to place, the cell references change. Often this is a good thing, like if I have in C1 = A1 + B1 and I copy that to C2, I'd like the formula to change to =A2+B2. Sometimes you don't want the reference to move and then you use anchors ($) to lock them in place. So try

= VLOOKUP(G1, $A$1:$C$2858,2,FALSE)

[ChemistB]

My mistake on this formula. I typoed. Should be

= VLOOKUP(TEXT(G1,"0000"), $A$1:$C$2858,2,FALSE)

[NathanC]

Ah, beautiful! Your second formula dragged right on through perfectly. The formula you gave in no. 8 worked fine from H1 to H9, but quit cold from 10 on--but this one worked wonderfully. I understand now about locking in place, as well.

I'm marking this thread as solved, but I'm still curious to know why the regular VLOOKUP method only worked throughthe 9th cell, while the other worked consistently.

[ChemistB]

It's definitely related to the fact that the values in column A were text and not numeric. Excel must have easily been able to convert the first 9 to numeric but after that, no dice. Glad you got it working