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