|
turning a worksheet inside out
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? |
turning a worksheet inside out
"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 |
turning a worksheet inside out
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 |
turning a worksheet inside out
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 |
turning a worksheet inside out
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 |
turning a worksheet inside out
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 |
turning a worksheet inside out
"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 |
turning a worksheet inside out
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 |
turning a worksheet inside out
your help has been awesome - much more than I had hoped for.
i sent you the email as you offered. thanx again don your time with family and car were better spent then helping me - I do appreciate it though "Billy Liddel" wrote: 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 |
| All times are GMT +1. The time now is 09:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com