So I have a list of over a thousand people and I need to take a random sample of 50 or so to include in an e-mail blast.
Each distinct row corresponds to a certain person with corresponding demographical information in each column. I would like to randomly choose a certain number of rows and have them copied and compiled into a new worksheet.
If possible I'd like to be able to do this without macros as my VBA knowledge is limited and I'd like to be able to easily adapt the process for different population and sample sizes.
Can anyone help me out witht his problem? Thanks!
JD
Hello, it's KD here again:
I'm trying to create this sheet so all I have to do is enter the data and then have a VLOOKUP grab the right data and put it in the "Summary" section when I put the month in. Attached are two sheet views - 1st is the summary with the formulas that aren't working right and the 2nd is the data itself.
My understanding of the VLOOKUP formula is that if the row is unique it will pull the data from that row in the specified column. However, it only works for "September." Any other month put in cell B2 will pull incorrect data. Don't know what's wrong, and I KNOW I should know this!!
Sheet1 - Summary Section
B
C
D
E
F
G
H
I
J
K
L
M
N
O
2
September
3
4
5
Month
YEAR TO DATE
Full-Year
6
Current Year
Last Year
Act v Co
Act v LY
Current Year
Last Year
Last Year
7
151
111
36.0%
447
1076
-58.5%
1,109
Spreadsheet Formulas
Cell
Formula
B7
=VLOOKUP(B2,Q5:V16,6)
D7
=VLOOKUP(B2,Q23:V34,6)
F7
=+B7/D7-1
H7
=VLOOKUP(B2,Q5:V16,5)
J7
=VLOOKUP(B2,Q23:V34,5)
L7
=+H7/J7-1
O7
=U34
Sheet1 - Data Section
Q
R
S
T
U
V
2
INCREASE
3
(CURRENT PERIOD
4
CURRENT YEAR
CONSOLIDATED
MINUS PRIOR PERIOD)
5
January
0
0
0
0
0
6
February
0
0
0
0
0
7
March
0
0
0
0
0
8
April
0
0
0
0
0
9
May
0
0
0
0
0
10
June
3
36
137
176
176
11
July
4
41
176
221
45
12
August
4
101
191
296
75
13
September
4
250
193
447
151
14
October
0
0
0
0
-447
15
November
0
0
0
0
0
16
December
0
0
0
0
0
17
18
19
20
INCREASE
21
(CURRENT PERIOD
22
PRIOR YEAR
CONSOLIDATED
MINUS PRIOR PERIOD)
23
January
0
0
0
0
0
24
February
0
0
0
0
0
25
March
0
0
0
0
0
26
April
0
0
0
0
0
27
May
0
0
0
0
0
28
June
34
153
772
959
959
29
July
34
154
774
962
3
30
August
34
154
777
965
3
31
September
34
265
777
1076
111
32
October
0
0
0
0
-1076
33
November
0
0
0
0
0
34
December
42
283
784
1109
1109
Spreadsheet Formulas
Cell
Formula
U5
=SUM(R5:T5)
V5
=U5
U6
=SUM(R6:T6)
V6
=U6-U5
U7
=SUM(R7:T7)
V7
=U7-U6
U8
=SUM(R8:T8)
V8
=U8-U7
U9
=SUM(R9:T9)
V9
=U9-U8
U10
=SUM(R10:T10)
V10
=U10-U9
U11
=SUM(R11:T11)
V11
=U11-U10
U12
=SUM(R12:T12)
V12
=U12-U11
U13
=SUM(R13:T13)
V13
=U13-U12
U14
=SUM(R14:T14)
V14
=U14-U13
U15
=SUM(R15:T15)
V15
=U15-U14
U16
=SUM(R16:T16)
V16
=U16-U15
U23
=SUM(R23:T23)
V23
=U23
U24
=SUM(R24:T24)
V24
=U24-U23
U25
=SUM(R25:T25)
V25
=U25-U24
U26
=SUM(R26:T26)
V26
=U26-U25
U27
=SUM(R27:T27)
V27
=U27-U26
U28
=SUM(R28:T28)
V28
=U28-U27
U29
=SUM(R29:T29)
V29
=U29-U28
U30
=SUM(R30:T30)
V30
=U30-U29
U31
=SUM(R31:T31)
V31
=U31-U30
U32
=SUM(R32:T32)
V32
=U32-U31
U33
=SUM(R33:T33)
V33
=U33-U32
U34
=SUM(R34:T34)
V34
=U34-U33
Column A
Total AM
Mar 15, 2010
What I am trying to do is VLOOKUP ""Total AM"" when found take the 1 value below which the march date.
I l lbe happy if someone can help!
thanks
Audrey
Excel 2002
I have a worksheet with about 1500 VLOOKUP's on it. Its causing the sheet its looking at to take forever to "calculate" every time I make a change to it. It there a way to stop the vlookup's from calculating and only have them calculate once via a macro button? Or is there some other way of doing this, that I dont know?
Thanks for the help everyone.
HI All,
I have two three sheets of data which run into 60000 rows and i need to do a vlookup from other file which too has similiar kind of records. It take a lot of time to produce the result, as it keeps on calculating. Is there any other way to do it , so that it can be faster.
Please help
regards,
Vinod
hi guys
we have designed a macro to do a vlookup function through various sheets, the concern is that the time taken for the vlookup to run is very huge. If there is any shorter way to do this then please revert. The vlookup is running on around 4000 rows and the data table for reference is around 5000 rows.
the piece of code is atatched for your reference and views.
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-31],[Close.xls]Data!C6,1,0)),1,0)"
Hello all, this is my first time of using this - so, hopefully someone can
help.
I am looking to do the following:
I have a part number - for instance 12345
I have a number of open purchase orders for that part - which are all listed
on a rather large file with a number of other open purchase orders.
What I want to do is take all the open purchase orders for that particular
part and 'pull' them into another file. I would do this by looking up the
part number and pulling each induvidual line item of information into another
worksheet.
Vlookup only takes the first value it gets to - how can I make it take the
1st, 2nd, 3rd lines of information?
Thanks for your help.
Hi,
I have a range A2: B9
Column A Contains names
Column B (B2:B9) needs to be populated with numbers such that their sum is = 100%
Can anybody help me with this please?
Thanks,
Manoj
Hi,
I have a range A2: B9
Column A Contains names
Column B (B2:B9) needs to be populated with numbers such that their sum is = 100%
Can anybody help me with this please?
Thanks,
Manoj
My issue is this and i hope i'll do an ok job describing it.
In cell 1 i have a formula that has rand() as part of it so it is constantly changing. What i need to do is in cell 2, have excel calculate that if cell 1 is more than 100 (example 101), then in cell 2 excel will calculate 1 times a number X in cell 3. another example would be let's say cell 1 is 105, i need the output in cell 2 to be: cell 3 times 5. so whatever the difference between any number that's greater than 100 and 100 itself must be multiplied by cell 3.
I really need help with this one and i don't know where else to ask.
Thanks in advance.
