Hi Don
Sorry for the low response but I was busy yesterday, family shop and the car
is OTR. I was unable to get this to work with formulas and have had to write
a macro to make it work.
Assuming that your layout on sheet2 was:
idx id2 Day Breakfast Provider Breakfast Server 6-7am Lunch Provider
1 1 Mon Bob Claire Kieran
8 1 Mon Ted Claire Marge
15 1 Mon Alice Jane Paul
2 2 Tue Betty Terri Kathy
ignore the two id columns I used these for sorting. The macro will produce
Volunteers Mon Tue
Alice "Breakfast Provider
Early Hosts (5pm-11pm) " "Breakfast Provider
Breakfast Server 6-7am "
Andy Day Hosts Early Hosts (5pm-11pm)
You will have to leave the index column in for the macro to work. Copy this
following code into a
VB Modue (ALT + F11, Insert Module). Return to the
worksheet ALT Q or file exit and run the macro from the Tools menu.
Sub rota()
Application.Goto Worksheets(1).Range("A1")
nr = Range("A1").CurrentRegion.Rows.Count
ncols = Range("A1").CurrentRegion.Columns.Count
'clear old rota
addr = Cells(nr, ncols).Address
Range("B2:" & addr).ClearContents
Application.ScreenUpdating = False
For a = 2 To nr
For b = 2 To 8
x = Cells(a, 1): y = Cells(1, b)
With Worksheets(2)
For j = 4 To 12
For i = 2 To 22
n = .Cells(i, j): m = .Cells(i, 3)
If n = x And m = y Then
If IsEmpty(Cells(a, b)) Then
Cells(a, b) = .Cells(1, j)
Else: Cells(a, b) = Cells(a, b) & Chr(10) & Chr(10) &
..Cells(1, j)
End If
End If
Next i
Next j
End With
Next b
Next a
Application.ScreenUpdating = True
End Sub
If you would like to e mail me at peter_atherton at hotmail dot com I'll
send you my worksheet. Do the obvious with the at and dot.
Regards
Peter
"Don" wrote:
"Don" wrote:
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
Mon
in case it matters = there are 3 of each day
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