Nested If's with sorting and auto copy/paste routine
Author Message
Nested If's with sorting and auto copy/paste routine

Greetings long suffering readers of my miserable excel exploits (not to
foget my miserable grammar too)

Today we have a new and exciting development in the 1000's of rows/columns
spreadsheets I find myself playing with.

The Problem is
Column B contains either a 1 or a 0 (1 indicates a boy and 0 indicates a
girl).Columns C to H give the age of the respondent. The entries in the C
to H colums will consist of 0's except for in 1 of these columns will be a
1. If the 1 is in C then the age is 12 if in D 13, etc etc (no i didnt
design the survey - before you cast that stone). Now in later columns (like
VX and so on) there are text answers (as in freeform answers like "books
bite" and so on).
What I need to do now is to sort out what ages responded in which
way with the freeform answers. So essentially I must create an area on the
spreadsheet with a section like the following.

Age     Response 1      response 2    response 3     etc
12       answer           answer       answer       answer
12       answer           answer       answer       answer
13       answer           answer       answer       answer

The respondents age probably needs to be defined with some sort of nested
if statement, then based on the age the freeform text responses would just
(genius required apply within) be taken in some form of auto copy paste
from that respondents row (row that age was taken from) to the area on the
spreadsheet where this data is being put.
Being able to sort by the childs age would be a plus but not necessary as
would be being able to sort via sex within age but these are not
necessities (they would be awfully sweet though and any hints gratefully
accepted).
I have actually decided to enrol in an advanced excel course as it looks
like I am required to become an overnight minimum wage excel wizard!

--
Aardvark Hater
ICQ: 8440315
2:13pm  up 15 min,  1 user,  load average: 0.64, 0.28, 0.10

Mon, 09 Feb 2004 13:06:31 GMT
Nested If's with sorting and auto copy/paste routine
Hi

Greetings from Adelaide

First of put a row with the age each column refers to, now
insert a new column and in the first cell put the formula
=sumproduct(headingline,currentline)
this will multiply the age line by either 1 or 0 and add
the totals up, so as long as no-one has filled in more
than one age you will end up with an age for each line.

Copy the formula all the way down the column making sure
that the heading line is absolute.

You can then filter based on your new column.

Alan

Quote:
>-----Original Message-----
>Greetings long suffering readers of my miserable excel
exploits (not to
>foget my miserable grammar too)

>Today we have a new and exciting development in the

1000's of rows/columns
Quote:
>spreadsheets I find myself playing with.

>The Problem is
>Column B contains either a 1 or a 0 (1 indicates a boy
and 0 indicates a
>girl).Columns C to H give the age of the respondent. The
entries in the C
>to H colums will consist of 0's except for in 1 of these
columns will be a
>1. If the 1 is in C then the age is 12 if in D 13, etc
etc (no i didnt
>design the survey - before you cast that stone). Now in

later columns (like
Quote:
>VX and so on) there are text answers (as in freeform

answers like "books
Quote:
>bite" and so on).
>What I need to do now is to sort out what ages responded
in which
>way with the freeform answers. So essentially I must

create an area on the
Quote:
>spreadsheet with a section like the following.

>Age     Response 1      response 2    response 3     etc
>12       answer           answer       answer       answer
>12       answer           answer       answer       answer
>13       answer           answer       answer
answer

>The respondents age probably needs to be defined with

some sort of nested
Quote:
>if statement, then based on the age the freeform text

responses would just
Quote:
>(genius required apply within) be taken in some form of
auto copy paste
>from that respondents row (row that age was taken from)
to the area on the
>spreadsheet where this data is being put.
>Being able to sort by the childs age would be a plus but
not necessary as
>would be being able to sort via sex within age but these
are not
>necessities (they would be awfully sweet though and any
hints gratefully
>accepted).
>I have actually decided to enrol in an advanced excel
course as it looks
>like I am required to become an overnight minimum wage
excel wizard!

>--
>Aardvark Hater
>ICQ: 8440315
>  2:13pm  up 15 min,  1 user,  load average: 0.64, 0.28,
0.10

>.

Mon, 09 Feb 2004 15:13:24 GMT
Nested If's with sorting and auto copy/paste routine
Hi Aardaark Hater!

Save your work under a backup name (Just in case!)
Select all of your data table.
Data > Sort
In the top drop down select the age heading
In the middle one select the sex heading
Make sure the selection for with and without headings is correct.
OK

Looks like you might also find hiding columns useful.

Select a column bt clicking its letter
Format > Column > Hide

To redisplay use
Format > Column > Unhide

But careful with the above because I just love Aardvarks and this may be an
elaborate trap that destroys all your data and formats your hard drive. :)

hth

Quote:
> Greetings long suffering readers of my miserable excel exploits (not to
> foget my miserable grammar too)

> Today we have a new and exciting development in the 1000's of rows/columns
> spreadsheets I find myself playing with.

> The Problem is
> Column B contains either a 1 or a 0 (1 indicates a boy and 0 indicates a
> girl).Columns C to H give the age of the respondent. The entries in the C
> to H colums will consist of 0's except for in 1 of these columns will be a
> 1. If the 1 is in C then the age is 12 if in D 13, etc etc (no i didnt
> design the survey - before you cast that stone). Now in later columns
(like
> VX and so on) there are text answers (as in freeform answers like "books
> bite" and so on).
> What I need to do now is to sort out what ages responded in which
> way with the freeform answers. So essentially I must create an area on the
> spreadsheet with a section like the following.

> Age     Response 1      response 2    response 3     etc
> 12       answer           answer       answer       answer
> 12       answer           answer       answer       answer
> 13       answer           answer       answer       answer

> The respondents age probably needs to be defined with some sort of nested
> if statement, then based on the age the freeform text responses would just
> (genius required apply within) be taken in some form of auto copy paste
> from that respondents row (row that age was taken from) to the area on the
> spreadsheet where this data is being put.
> Being able to sort by the childs age would be a plus but not necessary as
> would be being able to sort via sex within age but these are not
> necessities (they would be awfully sweet though and any hints gratefully
> accepted).
> I have actually decided to enrol in an advanced excel course as it looks
> like I am required to become an overnight minimum wage excel wizard!

> --
> Aardvark Hater
> ICQ: 8440315
>   2:13pm  up 15 min,  1 user,  load average: 0.64, 0.28, 0.10

Mon, 09 Feb 2004 15:54:07 GMT
Nested If's with sorting and auto copy/paste routine

Quote:

> Hi

> Greetings from Adelaide

> First of put a row with the age each column refers to, now
> insert a new column and in the first cell put the formula
> =sumproduct(headingline,currentline)
> this will multiply the age line by either 1 or 0 and add
> the totals up, so as long as no-one has filled in more
> than one age you will end up with an age for each line.

> Copy the formula all the way down the column making sure
> that the heading line is absolute.

> You can then filter based on your new column.

> Alan

>>-----Original Message-----
>>Greetings long suffering readers of my miserable excel
> exploits (not to
>>foget my miserable grammar too)

>>Today we have a new and exciting development in the
> 1000's of rows/columns
>>spreadsheets I find myself playing with.

>>The Problem is
>>Column B contains either a 1 or a 0 (1 indicates a boy
> and 0 indicates a
>>girl).Columns C to H give the age of the respondent. The
> entries in the C
>>to H colums will consist of 0's except for in 1 of these
> columns will be a
>>1. If the 1 is in C then the age is 12 if in D 13, etc
> etc (no i didnt
>>design the survey - before you cast that stone). Now in
> later columns (like
>>VX and so on) there are text answers (as in freeform
> answers like "books
>>bite" and so on).
>>What I need to do now is to sort out what ages responded
> in which
>>way with the freeform answers. So essentially I must
> create an area on the
>>spreadsheet with a section like the following.

>>Age     Response 1      response 2    response 3     etc
>>12       answer           answer       answer       answer
>>12       answer           answer       answer       answer
>>13       answer           answer       answer
> answer

>>The respondents age probably needs to be defined with
> some sort of nested
>>if statement, then based on the age the freeform text
> responses would just
>>(genius required apply within) be taken in some form of
> auto copy paste
>>from that respondents row (row that age was taken from)
> to the area on the
>>spreadsheet where this data is being put.
>>Being able to sort by the childs age would be a plus but
> not necessary as
>>would be being able to sort via sex within age but these
> are not
>>necessities (they would be awfully sweet though and any
> hints gratefully
>>accepted).
>>I have actually decided to enrol in an advanced excel
> course as it looks
>>like I am required to become an overnight minimum wage
> excel wizard!

>>--
>>Aardvark Hater
>>ICQ: 8440315
>>  2:13pm  up 15 min,  1 user,  load average: 0.64, 0.28,
> 0.10

>>.

Greetings and thanks from Bowden/Brompton my neighbour Alan (small world
isn't is?)
--
Aardvark Hater
ICQ: 8440315
12:24pm  up 19:04,  1 user,  load average: 0.19, 0.16, 0.17

Tue, 10 Feb 2004 10:55:47 GMT
Nested If's with sorting and auto copy/paste routine

<snip>

Greetings from Bowden/Brompton. Small world mate - thanks

--
Aardvark Hater
ICQ: 8440315
2:47pm  up 21:27,  1 user,  load average: 0.02, 0.07, 0.05

Tue, 10 Feb 2004 13:19:13 GMT

 Page 1 of 1 [ 5 post ]

Relevant Pages

Powered by phpBB® Forum Software © phpBB Group