HELP: survey data collection & analysis

[KevinGPO]

I need urgent help and am new to Excel. I've got 5 excel surveys which is
similar to:

http://www.surveymonkey.com/ExportEx...rtExamples.zip

I am using SurveyMonkey.com to do 5 surveys (which are all exactly the
same), however it's separated for 5 societies. Results can be
downloaded/exported as CSV files (as you can see in the ExportExamples.zip
file). So there are 5 of those ExportExamples.zip (above), one for each
society am surveying. How can I combine the 5 Condensed.csv files so it's
one big file?

I need the Condensed in one big file. Then I want to make Excel, or any
program, to randomly chose 2 winners/users from the combined-list. (It's for
a draw of 2 winners). Is this possible in Excel, or some other program? How
can I do this?

Many thanks
Kev

[Max]

Just some thoughts ..

> How can I combine the 5 Condensed.csv files so it's one big file?

Just copy > paste sequentially into a new sheet/book ?

For the purpose of the draw ..

Serialise the list by inserting a new col A,
Enter: 1 and 2 into say, A2:A3
(assuming the qualifiers' data starts in row2)
Select A2:A3, and drag to fill down all the way
(Make a note of the last serial# filled in col A, that's your last
qualifier)

For the draw you may not need to combine,
just serialise sequentially from one file to the other,
then make a note of the final serial# in the last, 5th file
(if the combined data exceeds 65k rows,
you can't put it into one sheet anyway)

> ... I want to make Excel .. to randomly chose
> 2 winners/users from the combined-list.

A simple way is to use RANDBETWEEN, and the F9 key

In a new sheet, put in say, C1: =randbetween(A1,B1)
Enter the first serial# into A1, the last serial# into B1
Hold down F9 key to recalc (this simulates "drawing")
for x seconds, release. The first winner is the number in C1.
Repeat to draw the second winner.

Then just go back to your serialised list to flesh out the 2 winners'
particulars

And if your draw rules state that winners are only entitled to one prize,
just re-draw the 2nd winner if the 2nd number drawn is a duplicate of the
1st number (this is possible with RANDBETWEEN, albeit remote if the range is
large), or if the number drawn (a non-duplicate) points to the same winner

Note that RANDBETWEEN requires the Analysis Toolpak
to be installed and activated.

Check the "Analysis Toolpak" box (via Tools > Add-Ins)

Chip Pearson's page has details on the ATP at:
http://www.cpearson.com/excel/ATP.htm
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"KevinGPO" <kevingpo@hotmail.com> wrote in message
news:expqBoD1FHA.612@TK2MSFTNGP10.phx.gbl...
> I need urgent help and am new to Excel. I've got 5 excel surveys which is
> similar to:
>
> http://www.surveymonkey.com/ExportEx...rtExamples.zip
>
> I am using SurveyMonkey.com to do 5 surveys (which are all exactly the
> same), however it's separated for 5 societies. Results can be
> downloaded/exported as CSV files (as you can see in the ExportExamples.zip
> file). So there are 5 of those ExportExamples.zip (above), one for each
> society am surveying. How can I combine the 5 Condensed.csv files so it's
> one big file?
>
> I need the Condensed in one big file. Then I want to make Excel, or any
> program, to randomly chose 2 winners/users from the combined-list. (It's
for
> a draw of 2 winners). Is this possible in Excel, or some other program?
How
> can I do this?
>
> Many thanks
> Kev
>
>

[Kevin GPO]

Thanks for your reply. Very useful.

I got a problem.
if email1@address.com entered survey 1, 2, 3, 4, 5 (all 5 surveys), then
when I combine the surveys, is there any way to extract the email
address uniquely? eg. it will only take that email address as appearing
once, instead 5 times?


*** Sent via Developersdex http://www.developersdex.com ***

[Max]

"Kevin GPO" wrote
> Thanks for your reply. Very useful.
> I got a problem.
> if email1@address.com entered survey 1, 2, 3, 4, 5 (all 5 surveys), then
> when I combine the surveys, is there any way to extract the email
> address uniquely? eg. it will only take that email address as appearing
> once, instead 5 times?

By your rules, are multiple entries allowed/disallowed in the first place ?
If it's disallowed, simply check & disqualify the winner(s) and re-draw.
And to check on the 2 winners' statuses,
you could use Data>Filter>Autofilter and filter on the email col
(no need to check all qualifiers/entries, just the 2 winners)

Anyway, one way to extract a "uniques" list is to use Advanced Filter

Assume the email address data is all in col A
Type a col label in A1 (if required)
Select col A
Click Data > Filter > Advanced Filter
[Click OK to the Excel prompt to use the first row as labels]

In the Advanced Filter dialog box:
----------------------------------------
Check "Copy to another location"
Ensure that the selected range correctly appears in the "List range:" box
Put for "Copy to:" : B1 (say)
Check "Unique records only"
Click OK

The unique list of items in col A will be extracted in col B
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
>
>
> *** Sent via Developersdex http://www.developersdex.com ***