I have a worksheet - with days down column and tasks across rows and names in
the cells. I want to create a list of names by tasks signed up for.

example
taska taskb
mon Bob Ted
tues Alice Bob
....

and make an assignment list like

Bob taska taskb
Ted taskb
Alice taska


can I do that?

"Don" wrote:

I have a worksheet - with days down column and tasks across rows and names in
the cells. I want to create a list of names by tasks signed up for.

example
taska taskb
mon Bob Ted
tues Alice Bob
...

and make an assignment list like

Bob taska taskb
Ted taskb
Alice taska


can I do that?

or the final list can look like
Bob taska
Ted taskb
Alice taska
Bob taskb

Don
One way - you have to kid Excel find ount how many names there are using the
COUNTA function e.g =COUNTA(B2:C3), this is the number we work with

The formulas go in column B but before that we need to enter something into
column A that we can count a space will do - then copy this down the number
of rows you counta function gave you.

I entered this in B6:
=IF(COUNTA($A$6:A6)<=COUNTA($B$2:$C$3)/2,INDEX($B$2:$B$3,MATCH(B2,B$2:B$3,0)),INDEX($C$2: $C$3,MATCH(C2,C$2:C$3,0)))

and C6
=IF(COUNTA($A$6:$A6)<=COUNTA($B$2:$C$3)/2,$B$1,$C$1)
and copied down

This results in:

Bob taska
Alice taska
Ted taskb
Bob taskb


Regards
Peter
"Don" wrote:

"Don" wrote:

I have a worksheet - with days down column and tasks across rows and names in
the cells. I want to create a list of names by tasks signed up for.

example
taska taskb
mon Bob Ted
tues Alice Bob
...

and make an assignment list like

Bob taska taskb
Ted taskb
Alice taska


can I do that?

or the final list can look like
Bob taska
Ted taskb
Alice taska
Bob taskb

Billy,
thank - I am trying to generalize so let me see I get it. see embedded

"Billy Liddel" wrote:

Don
One way - you have to kid Excel find ount how many names there are using the
COUNTA function e.g =COUNTA(B2:C3), this is the number we work with

the rectangle is from B1 to J24 so the following yields 174
=COUNTA($B$1:$J$24)


The formulas go in column B but before that we need to enter something into
column A that we can count a space will do - then copy this down the number
of rows you counta function gave you.

I entered this in B6:

B6 just a convienent cell?

If I use B30
=IF(COUNTA($A$6:A6)<=COUNTA($B$2:$C$3)/2,INDEX($B$2:$B$3,MATCH(B2,B$2:B$3,0)),INDEX($C$2: $C$3,MATCH(C2,C$2:C$3,0)))

for my example
=IF(COUNTA($A$30:A30)<=COUNTA($B$2:$J$24)/2,INDEX($B$2:$B$24,MATCH(B2,B$2:B$24,0)),INDEX($C$ 2:$C$24,MATCH(C2,C$2:C$24,0)))


and C6

mine is C30
=IF(COUNTA($A$6:$A6)<=COUNTA($B$2:$C$3)/2,$B$1,$C$1)

=IF(COUNTA($A$6:$A6)<=COUNTA($B$2:$J$24)/2,$B$1,$C$1)



I must be making a mistake :o( I need more help!


and copied down

This results in:

Bob taska
Alice taska
Ted taskb
Bob taskb


Regards
Peter
"Don" wrote:

"Don" wrote:

I have a worksheet - with days down column and tasks across rows and names in
the cells. I want to create a list of names by tasks signed up for.

example
taska taskb
mon Bob Ted
tues Alice Bob
...

and make an assignment list like

Bob taska taskb
Ted taskb
Alice taska


can I do that?

or the final list can look like
Bob taska
Ted taskb
Alice taska
Bob taskb

Don

the rectangle is from B1 to J24 so the following yields 174
=COUNTA($B$1:$J$24)

I'm afraid my method will not work with nine columns (there is a limit of 7
nested if statements).

You might like to repost with more representative data so everyone can look
at this.

I'll try to fill in some data and try again.

Regards
Peter
"Don" wrote:

Billy,
thank - I am trying to generalize so let me see I get it. see embedded

"Billy Liddel" wrote:

Don
One way - you have to kid Excel find ount how many names there are using the
COUNTA function e.g =COUNTA(B2:C3), this is the number we work with

the rectangle is from B1 to J24 so the following yields 174
=COUNTA($B$1:$J$24)


The formulas go in column B but before that we need to enter something into
column A that we can count a space will do - then copy this down the number
of rows you counta function gave you.

I entered this in B6:

B6 just a convienent cell?

If I use B30
=IF(COUNTA($A$6:A6)<=COUNTA($B$2:$C$3)/2,INDEX($B$2:$B$3,MATCH(B2,B$2:B$3,0)),INDEX($C$2: $C$3,MATCH(C2,C$2:C$3,0)))

for my example
=IF(COUNTA($A$30:A30)<=COUNTA($B$2:$J$24)/2,INDEX($B$2:$B$24,MATCH(B2,B$2:B$24,0)),INDEX($C$ 2:$C$24,MATCH(C2,C$2:C$24,0)))


and C6

mine is C30
=IF(COUNTA($A$6:$A6)<=COUNTA($B$2:$C$3)/2,$B$1,$C$1)

=IF(COUNTA($A$6:$A6)<=COUNTA($B$2:$J$24)/2,$B$1,$C$1)



I must be making a mistake :o( I need more help!


and copied down

This results in:

Bob taska
Alice taska
Ted taskb
Bob taskb


Regards
Peter
"Don" wrote:

"Don" wrote:

I have a worksheet - with days down column and tasks across rows and names in
the cells. I want to create a list of names by tasks signed up for.

example
taska taskb
mon Bob Ted
tues Alice Bob
...

and make an assignment list like

Bob taska taskb
Ted taskb
Alice taska


can I do that?

or the final list can look like
Bob taska
Ted taskb
Alice taska
Bob taskb

thanks for the quick response...

The column header is
[Day] [Breakfast Provider] [Breakfast Server 6-7am] [Lunch Provider] [Supper
Provider 5:30pm] [Supper Servers 6 or 6:30pm] [Early Hosts (5pm-11pm)]
[Overnight
Hosts (11pm-7am)] [Day Hosts] [Laundry]

and the rows
Mon
Tues
Wed
Thurs
Fri
Sat
Sun

and of course the cells have people names in it

this is a sign-up spreadsheet for a week long homeless shelter we are
hosting at our church. I wanted to pull the names out of the spreadsheet and
the task they signed up for - extra credit to pull the days out too.

does that help?




"Billy Liddel" wrote:

Don

the rectangle is from B1 to J24 so the following yields 174
=COUNTA($B$1:$J$24)

I'm afraid my method will not work with nine columns (there is a limit of 7
nested if statements).

You might like to repost with more representative data so everyone can look
at this.

I'll try to fill in some data and try again.

Regards
Peter
"Don" wrote:

Billy,
thank - I am trying to generalize so let me see I get it. see embedded

"Billy Liddel" wrote:

Don
One way - you have to kid Excel find ount how many names there are using the
COUNTA function e.g =COUNTA(B2:C3), this is the number we work with

the rectangle is from B1 to J24 so the following yields 174
=COUNTA($B$1:$J$24)


The formulas go in column B but before that we need to enter something into
column A that we can count a space will do - then copy this down the number
of rows you counta function gave you.

I entered this in B6:

B6 just a convienent cell?

If I use B30
=IF(COUNTA($A$6:A6)<=COUNTA($B$2:$C$3)/2,INDEX($B$2:$B$3,MATCH(B2,B$2:B$3,0)),INDEX($C$2: $C$3,MATCH(C2,C$2:C$3,0)))

for my example
=IF(COUNTA($A$30:A30)<=COUNTA($B$2:$J$24)/2,INDEX($B$2:$B$24,MATCH(B2,B$2:B$24,0)),INDEX($C$ 2:$C$24,MATCH(C2,C$2:C$24,0)))


and C6

mine is C30
=IF(COUNTA($A$6:$A6)<=COUNTA($B$2:$C$3)/2,$B$1,$C$1)

=IF(COUNTA($A$6:$A6)<=COUNTA($B$2:$J$24)/2,$B$1,$C$1)



I must be making a mistake :o( I need more help!


and copied down

This results in:

Bob taska
Alice taska
Ted taskb
Bob taskb


Regards
Peter
"Don" wrote:

"Don" wrote:

I have a worksheet - with days down column and tasks across rows and names in
the cells. I want to create a list of names by tasks signed up for.

example
taska taskb
mon Bob Ted
tues Alice Bob
...

and make an assignment list like

Bob taska taskb
Ted taskb
Alice taska


can I do that?

or the final list can look like
Bob taska
Ted taskb
Alice taska
Bob taskb