|
Row numbers containing specific value within a column
I have a list of 20 names in Column A of my spread sheet. Then I have 13
columns which represent 13 weeks (1/4 of a year). Each week there are 6 people assigned to be moderators and I place the letter M in 6 different rows within the column for the specific week. I also put other letters within the columns to alert me as to who are participants vs moderators in any individual week. If I want to generate a list of the 6 moderators each week is there a function I can use that will identify the 6 row numbers that have the letter M in them? Then I can use those row numbers to print myself a list of the names that correspond with those row numbers. My final goal is to be able to generate a list that will have 13 weeks down the left side and to the right of each week number will be the 6 names of the moderators for that week. Essentially a schudle for a quarter of the year. Thanks Carl |
Row numbers containing specific value within a column
See if this example gets you headed in the right direction...
With Sheet1, cells A1:N21 containing the data list Where A2:A21 contains Employee Names B1:N1 contains Wk01, Wk02,....Wk13 B2:N21 contains the grid of assigned employees On Sheet2.... A2:A14 contains Wk01, Wk02,....Wk13 B1:G1 contains 1,2,3,4,5,6 Put this ARRAY FORMULA* in B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="M",ROW(Sheet1!$B$2:$B$21)),B$1)) Or...alternatively....this ARRAY FORMULA* B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,MATCH($A2,Sheet1!$B$1:$N$1,0))="M",ROW( Sheet1!$B$2:$B$21)),B$1)) (Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter].) Copy B2 and paste into C2:G2 Then...Copy B2:G2 and paste into A3:G14 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: I have a list of 20 names in Column A of my spread sheet. Then I have 13 columns which represent 13 weeks (1/4 of a year). Each week there are 6 people assigned to be moderators and I place the letter M in 6 different rows within the column for the specific week. I also put other letters within the columns to alert me as to who are participants vs moderators in any individual week. If I want to generate a list of the 6 moderators each week is there a function I can use that will identify the 6 row numbers that have the letter M in them? Then I can use those row numbers to print myself a list of the names that correspond with those row numbers. My final goal is to be able to generate a list that will have 13 weeks down the left side and to the right of each week number will be the 6 names of the moderators for that week. Essentially a schudle for a quarter of the year. Thanks Carl |
Row numbers containing specific value within a column
Thanks--I'll go try it out today.
Carl "Ron Coderre" wrote: See if this example gets you headed in the right direction... With Sheet1, cells A1:N21 containing the data list Where A2:A21 contains Employee Names B1:N1 contains Wk01, Wk02,....Wk13 B2:N21 contains the grid of assigned employees On Sheet2.... A2:A14 contains Wk01, Wk02,....Wk13 B1:G1 contains 1,2,3,4,5,6 Put this ARRAY FORMULA* in B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="M",ROW(Sheet1!$B$2:$B$21)),B$1)) Or...alternatively....this ARRAY FORMULA* B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,MATCH($A2,Sheet1!$B$1:$N$1,0))="M",ROW( Sheet1!$B$2:$B$21)),B$1)) (Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter].) Copy B2 and paste into C2:G2 Then...Copy B2:G2 and paste into A3:G14 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: I have a list of 20 names in Column A of my spread sheet. Then I have 13 columns which represent 13 weeks (1/4 of a year). Each week there are 6 people assigned to be moderators and I place the letter M in 6 different rows within the column for the specific week. I also put other letters within the columns to alert me as to who are participants vs moderators in any individual week. If I want to generate a list of the 6 moderators each week is there a function I can use that will identify the 6 row numbers that have the letter M in them? Then I can use those row numbers to print myself a list of the names that correspond with those row numbers. My final goal is to be able to generate a list that will have 13 weeks down the left side and to the right of each week number will be the 6 names of the moderators for that week. Essentially a schudle for a quarter of the year. Thanks Carl |
Row numbers containing specific value within a column
Ron,
Thanks for your suggestion, I trid both of the formulas but both came back with n/a as an error message. I tried to rview the information on these functions in excel help but I was unable to find whatever I may be doing wrong to keep the formula from working. I tried to email you a copy of my spreadsheet but it came back as undeliverable. Carl "Ron Coderre" wrote: See if this example gets you headed in the right direction... With Sheet1, cells A1:N21 containing the data list Where A2:A21 contains Employee Names B1:N1 contains Wk01, Wk02,....Wk13 B2:N21 contains the grid of assigned employees On Sheet2.... A2:A14 contains Wk01, Wk02,....Wk13 B1:G1 contains 1,2,3,4,5,6 Put this ARRAY FORMULA* in B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="M",ROW(Sheet1!$B$2:$B$21)),B$1)) Or...alternatively....this ARRAY FORMULA* B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,MATCH($A2,Sheet1!$B$1:$N$1,0))="M",ROW( Sheet1!$B$2:$B$21)),B$1)) (Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter].) Copy B2 and paste into C2:G2 Then...Copy B2:G2 and paste into A3:G14 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: I have a list of 20 names in Column A of my spread sheet. Then I have 13 columns which represent 13 weeks (1/4 of a year). Each week there are 6 people assigned to be moderators and I place the letter M in 6 different rows within the column for the specific week. I also put other letters within the columns to alert me as to who are participants vs moderators in any individual week. If I want to generate a list of the 6 moderators each week is there a function I can use that will identify the 6 row numbers that have the letter M in them? Then I can use those row numbers to print myself a list of the names that correspond with those row numbers. My final goal is to be able to generate a list that will have 13 weeks down the left side and to the right of each week number will be the 6 names of the moderators for that week. Essentially a schudle for a quarter of the year. Thanks Carl |
Row numbers containing specific value within a column
Did you try building the sample model first to make sure that the basic
concept and formulas are functional? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, Thanks for your suggestion, I trid both of the formulas but both came back with n/a as an error message. I tried to rview the information on these functions in excel help but I was unable to find whatever I may be doing wrong to keep the formula from working. I tried to email you a copy of my spreadsheet but it came back as undeliverable. Carl "Ron Coderre" wrote: See if this example gets you headed in the right direction... With Sheet1, cells A1:N21 containing the data list Where A2:A21 contains Employee Names B1:N1 contains Wk01, Wk02,....Wk13 B2:N21 contains the grid of assigned employees On Sheet2.... A2:A14 contains Wk01, Wk02,....Wk13 B1:G1 contains 1,2,3,4,5,6 Put this ARRAY FORMULA* in B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="M",ROW(Sheet1!$B$2:$B$21)),B$1)) Or...alternatively....this ARRAY FORMULA* B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,MATCH($A2,Sheet1!$B$1:$N$1,0))="M",ROW( Sheet1!$B$2:$B$21)),B$1)) (Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter].) Copy B2 and paste into C2:G2 Then...Copy B2:G2 and paste into A3:G14 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: I have a list of 20 names in Column A of my spread sheet. Then I have 13 columns which represent 13 weeks (1/4 of a year). Each week there are 6 people assigned to be moderators and I place the letter M in 6 different rows within the column for the specific week. I also put other letters within the columns to alert me as to who are participants vs moderators in any individual week. If I want to generate a list of the 6 moderators each week is there a function I can use that will identify the 6 row numbers that have the letter M in them? Then I can use those row numbers to print myself a list of the names that correspond with those row numbers. My final goal is to be able to generate a list that will have 13 weeks down the left side and to the right of each week number will be the 6 names of the moderators for that week. Essentially a schudle for a quarter of the year. Thanks Carl |
Row numbers containing specific value within a column
Ron,
Yes, I built a spreadsheet with 20 names in column a from a2 to a21. Then I put 13 weeks across the top columns B1 thru N1. I then populated 6 people for each week with the letter m in the 13 weeks of columns. I then went to sheet two and set it up with the names of 13 weeks in column A rows 2-14 and in B1-G1 the numbers 1 thru 6. I then went to b2 and entered the first formula as an array formula and got n/a, so I then erased that formula and tried formula 2 and got n/a again. "Ron Coderre" wrote: Did you try building the sample model first to make sure that the basic concept and formulas are functional? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, Thanks for your suggestion, I trid both of the formulas but both came back with n/a as an error message. I tried to rview the information on these functions in excel help but I was unable to find whatever I may be doing wrong to keep the formula from working. I tried to email you a copy of my spreadsheet but it came back as undeliverable. Carl "Ron Coderre" wrote: See if this example gets you headed in the right direction... With Sheet1, cells A1:N21 containing the data list Where A2:A21 contains Employee Names B1:N1 contains Wk01, Wk02,....Wk13 B2:N21 contains the grid of assigned employees On Sheet2.... A2:A14 contains Wk01, Wk02,....Wk13 B1:G1 contains 1,2,3,4,5,6 Put this ARRAY FORMULA* in B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="M",ROW(Sheet1!$B$2:$B$21)),B$1)) Or...alternatively....this ARRAY FORMULA* B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,MATCH($A2,Sheet1!$B$1:$N$1,0))="M",ROW( Sheet1!$B$2:$B$21)),B$1)) (Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter].) Copy B2 and paste into C2:G2 Then...Copy B2:G2 and paste into A3:G14 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: I have a list of 20 names in Column A of my spread sheet. Then I have 13 columns which represent 13 weeks (1/4 of a year). Each week there are 6 people assigned to be moderators and I place the letter M in 6 different rows within the column for the specific week. I also put other letters within the columns to alert me as to who are participants vs moderators in any individual week. If I want to generate a list of the 6 moderators each week is there a function I can use that will identify the 6 row numbers that have the letter M in them? Then I can use those row numbers to print myself a list of the names that correspond with those row numbers. My final goal is to be able to generate a list that will have 13 weeks down the left side and to the right of each week number will be the 6 names of the moderators for that week. Essentially a schudle for a quarter of the year. Thanks Carl |
Row numbers containing specific value within a column
I'm a bit puzzled.....I followed what you last posted and the model works fine.
See if this helps: A clip from Sheet1, cells A1:C7 (blank) Wk01 Wk02 Name01 m (blank) Name02 (blank) m Name03 (blank) (blank) Name04 m (blank) Name05 (blank) m Name06 (blank) (blank) A clip from Sheet2, cells A1:D3 (blank) 1 2 3 Wk01 Name01 Name04 Name07 Wk02 Name02 Name05 Name08 The ARRAY FORMULA in Sheet2, B2 is: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="M",ROW(Sheet1!$B$2:$B$21)),B$1)) Commited with [ctrl][shift][enter] Did you do something different? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, Yes, I built a spreadsheet with 20 names in column a from a2 to a21. Then I put 13 weeks across the top columns B1 thru N1. I then populated 6 people for each week with the letter m in the 13 weeks of columns. I then went to sheet two and set it up with the names of 13 weeks in column A rows 2-14 and in B1-G1 the numbers 1 thru 6. I then went to b2 and entered the first formula as an array formula and got n/a, so I then erased that formula and tried formula 2 and got n/a again. "Ron Coderre" wrote: Did you try building the sample model first to make sure that the basic concept and formulas are functional? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, Thanks for your suggestion, I trid both of the formulas but both came back with n/a as an error message. I tried to rview the information on these functions in excel help but I was unable to find whatever I may be doing wrong to keep the formula from working. I tried to email you a copy of my spreadsheet but it came back as undeliverable. Carl "Ron Coderre" wrote: See if this example gets you headed in the right direction... With Sheet1, cells A1:N21 containing the data list Where A2:A21 contains Employee Names B1:N1 contains Wk01, Wk02,....Wk13 B2:N21 contains the grid of assigned employees On Sheet2.... A2:A14 contains Wk01, Wk02,....Wk13 B1:G1 contains 1,2,3,4,5,6 Put this ARRAY FORMULA* in B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="M",ROW(Sheet1!$B$2:$B$21)),B$1)) Or...alternatively....this ARRAY FORMULA* B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,MATCH($A2,Sheet1!$B$1:$N$1,0))="M",ROW( Sheet1!$B$2:$B$21)),B$1)) (Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter].) Copy B2 and paste into C2:G2 Then...Copy B2:G2 and paste into A3:G14 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: I have a list of 20 names in Column A of my spread sheet. Then I have 13 columns which represent 13 weeks (1/4 of a year). Each week there are 6 people assigned to be moderators and I place the letter M in 6 different rows within the column for the specific week. I also put other letters within the columns to alert me as to who are participants vs moderators in any individual week. If I want to generate a list of the 6 moderators each week is there a function I can use that will identify the 6 row numbers that have the letter M in them? Then I can use those row numbers to print myself a list of the names that correspond with those row numbers. My final goal is to be able to generate a list that will have 13 weeks down the left side and to the right of each week number will be the 6 names of the moderators for that week. Essentially a schudle for a quarter of the year. Thanks Carl |
Row numbers containing specific value within a column
"Ron Coderre" wrote: I'm a bit puzzled.....I followed what you last posted and the model works fine. See if this helps: A clip from Sheet1, cells A1:C7 (blank) Wk01 Wk02 Name01 m (blank) Name02 (blank) m Name03 (blank) (blank) Name04 m (blank) Name05 (blank) m Name06 (blank) (blank) A clip from Sheet2, cells A1:D3 (blank) 1 2 3 Wk01 Name01 Name04 Name07 Wk02 Name02 Name05 Name08 The ARRAY FORMULA in Sheet2, B2 is: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="M",ROW(Sheet1!$B$2:$B$21)),B$1)) Commited with [ctrl][shift][enter] Did you do something different? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, Yes, I built a spreadsheet with 20 names in column a from a2 to a21. Then I put 13 weeks across the top columns B1 thru N1. I then populated 6 people for each week with the letter m in the 13 weeks of columns. I then went to sheet two and set it up with the names of 13 weeks in column A rows 2-14 and in B1-G1 the numbers 1 thru 6. I then went to b2 and entered the first formula as an array formula and got n/a, so I then erased that formula and tried formula 2 and got n/a again. "Ron Coderre" wrote: Did you try building the sample model first to make sure that the basic concept and formulas are functional? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, Thanks for your suggestion, I trid both of the formulas but both came back with n/a as an error message. I tried to rview the information on these functions in excel help but I was unable to find whatever I may be doing wrong to keep the formula from working. I tried to email you a copy of my spreadsheet but it came back as undeliverable. Carl "Ron Coderre" wrote: See if this example gets you headed in the right direction... With Sheet1, cells A1:N21 containing the data list Where A2:A21 contains Employee Names B1:N1 contains Wk01, Wk02,....Wk13 B2:N21 contains the grid of assigned employees On Sheet2.... A2:A14 contains Wk01, Wk02,....Wk13 B1:G1 contains 1,2,3,4,5,6 Put this ARRAY FORMULA* in B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="M",ROW(Sheet1!$B$2:$B$21)),B$1)) Or...alternatively....this ARRAY FORMULA* B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,MATCH($A2,Sheet1!$B$1:$N$1,0))="M",ROW( Sheet1!$B$2:$B$21)),B$1)) (Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter].) Copy B2 and paste into C2:G2 Then...Copy B2:G2 and paste into A3:G14 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: I have a list of 20 names in Column A of my spread sheet. Then I have 13 columns which represent 13 weeks (1/4 of a year). Each week there are 6 people assigned to be moderators and I place the letter M in 6 different rows within the column for the specific week. I also put other letters within the columns to alert me as to who are participants vs moderators in any individual week. If I want to generate a list of the 6 moderators each week is there a function I can use that will identify the 6 row numbers that have the letter M in them? Then I can use those row numbers to print myself a list of the names that correspond with those row numbers. My final goal is to be able to generate a list that will have 13 weeks down the left side and to the right of each week number will be the 6 names of the moderators for that week. Essentially a schudle for a quarter of the year. Thanks Carl |
Row numbers containing specific value within a column
Ron,
I copied and pasted your formula directly into my spreadsheet model and then did the ctr/shift/enter to make it an array formula and it still didn't work. I then started with a new workbook from scratch and typed in the formula and I still get a value error. My wife tried it on her own with the second formula and that didn't work eitheer. Here is the snapshot of my model: wk01 wk02 wk03 carl tom m m rayp m raym m al m ralph harvey m m carol m Karl m bob m jerry m steve m larry m skip wade m jim m susan janet marcie m jackie m m 1 2 3 w1 #VALUE! w2 w3 w4 w5 w6 w7 w8 w9 w10 w11 w12 w13 =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="m",ROW(Sheet1!$B$2:$B$21)),B$1)) When I do a copy and paste it doesn't show the { } for an array formula but they are there in the spreadsheet. Is there a way for me to attach my model to a post here that you could see what might be happening? Carl "Ron Coderre" wrote: Did you try building the sample model first to make sure that the basic concept and formulas are functional? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, Thanks for your suggestion, I trid both of the formulas but both came back with n/a as an error message. I tried to rview the information on these functions in excel help but I was unable to find whatever I may be doing wrong to keep the formula from working. I tried to email you a copy of my spreadsheet but it came back as undeliverable. Carl "Ron Coderre" wrote: See if this example gets you headed in the right direction... With Sheet1, cells A1:N21 containing the data list Where A2:A21 contains Employee Names B1:N1 contains Wk01, Wk02,....Wk13 B2:N21 contains the grid of assigned employees On Sheet2.... A2:A14 contains Wk01, Wk02,....Wk13 B1:G1 contains 1,2,3,4,5,6 Put this ARRAY FORMULA* in B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="M",ROW(Sheet1!$B$2:$B$21)),B$1)) Or...alternatively....this ARRAY FORMULA* B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,MATCH($A2,Sheet1!$B$1:$N$1,0))="M",ROW( Sheet1!$B$2:$B$21)),B$1)) (Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter].) Copy B2 and paste into C2:G2 Then...Copy B2:G2 and paste into A3:G14 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: I have a list of 20 names in Column A of my spread sheet. Then I have 13 columns which represent 13 weeks (1/4 of a year). Each week there are 6 people assigned to be moderators and I place the letter M in 6 different rows within the column for the specific week. I also put other letters within the columns to alert me as to who are participants vs moderators in any individual week. If I want to generate a list of the 6 moderators each week is there a function I can use that will identify the 6 row numbers that have the letter M in them? Then I can use those row numbers to print myself a list of the names that correspond with those row numbers. My final goal is to be able to generate a list that will have 13 weeks down the left side and to the right of each week number will be the 6 names of the moderators for that week. Essentially a schudle for a quarter of the year. Thanks Carl |
Row numbers containing specific value within a column
I sent you my model spreadsheet with your formula on sheet2 --maybe
something will jump out you -- I have no idea what I am doing incorrectly. Carl "Ron Coderre" wrote: I'm a bit puzzled.....I followed what you last posted and the model works fine. See if this helps: A clip from Sheet1, cells A1:C7 (blank) Wk01 Wk02 Name01 m (blank) Name02 (blank) m Name03 (blank) (blank) Name04 m (blank) Name05 (blank) m Name06 (blank) (blank) A clip from Sheet2, cells A1:D3 (blank) 1 2 3 Wk01 Name01 Name04 Name07 Wk02 Name02 Name05 Name08 The ARRAY FORMULA in Sheet2, B2 is: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="M",ROW(Sheet1!$B$2:$B$21)),B$1)) Commited with [ctrl][shift][enter] Did you do something different? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, Yes, I built a spreadsheet with 20 names in column a from a2 to a21. Then I put 13 weeks across the top columns B1 thru N1. I then populated 6 people for each week with the letter m in the 13 weeks of columns. I then went to sheet two and set it up with the names of 13 weeks in column A rows 2-14 and in B1-G1 the numbers 1 thru 6. I then went to b2 and entered the first formula as an array formula and got n/a, so I then erased that formula and tried formula 2 and got n/a again. "Ron Coderre" wrote: Did you try building the sample model first to make sure that the basic concept and formulas are functional? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, Thanks for your suggestion, I trid both of the formulas but both came back with n/a as an error message. I tried to rview the information on these functions in excel help but I was unable to find whatever I may be doing wrong to keep the formula from working. I tried to email you a copy of my spreadsheet but it came back as undeliverable. Carl "Ron Coderre" wrote: See if this example gets you headed in the right direction... With Sheet1, cells A1:N21 containing the data list Where A2:A21 contains Employee Names B1:N1 contains Wk01, Wk02,....Wk13 B2:N21 contains the grid of assigned employees On Sheet2.... A2:A14 contains Wk01, Wk02,....Wk13 B1:G1 contains 1,2,3,4,5,6 Put this ARRAY FORMULA* in B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="M",ROW(Sheet1!$B$2:$B$21)),B$1)) Or...alternatively....this ARRAY FORMULA* B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,MATCH($A2,Sheet1!$B$1:$N$1,0))="M",ROW( Sheet1!$B$2:$B$21)),B$1)) (Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter].) Copy B2 and paste into C2:G2 Then...Copy B2:G2 and paste into A3:G14 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: I have a list of 20 names in Column A of my spread sheet. Then I have 13 columns which represent 13 weeks (1/4 of a year). Each week there are 6 people assigned to be moderators and I place the letter M in 6 different rows within the column for the specific week. I also put other letters within the columns to alert me as to who are participants vs moderators in any individual week. If I want to generate a list of the 6 moderators each week is there a function I can use that will identify the 6 row numbers that have the letter M in them? Then I can use those row numbers to print myself a list of the names that correspond with those row numbers. My final goal is to be able to generate a list that will have 13 weeks down the left side and to the right of each week number will be the 6 names of the moderators for that week. Essentially a schudle for a quarter of the year. Thanks Carl |
Row numbers containing specific value within a column
Sorry for the delay, Carl
Here are some of your Sheet 1 values: wk01 wk02 wk03 And here are some of your Sheet2 values: w1 w2 w3 See a slight difference?..... Thought so! Sheet2 should have the same values as Sheet1 Wk01, instead of w1 or even W01 ....the key is that the right 2 characters of the Sheet2 week references must be digits. Does that help? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, I copied and pasted your formula directly into my spreadsheet model and then did the ctr/shift/enter to make it an array formula and it still didn't work. I then started with a new workbook from scratch and typed in the formula and I still get a value error. My wife tried it on her own with the second formula and that didn't work eitheer. Here is the snapshot of my model: wk01 wk02 wk03 carl tom m m rayp m raym m al m ralph harvey m m carol m Karl m bob m jerry m steve m larry m skip wade m jim m susan janet marcie m jackie m m 1 2 3 w1 #VALUE! w2 w3 w4 w5 w6 w7 w8 w9 w10 w11 w12 w13 =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="m",ROW(Sheet1!$B$2:$B$21)),B$1)) When I do a copy and paste it doesn't show the { } for an array formula but they are there in the spreadsheet. Is there a way for me to attach my model to a post here that you could see what might be happening? Carl "Ron Coderre" wrote: Did you try building the sample model first to make sure that the basic concept and formulas are functional? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, Thanks for your suggestion, I trid both of the formulas but both came back with n/a as an error message. I tried to rview the information on these functions in excel help but I was unable to find whatever I may be doing wrong to keep the formula from working. I tried to email you a copy of my spreadsheet but it came back as undeliverable. Carl "Ron Coderre" wrote: See if this example gets you headed in the right direction... With Sheet1, cells A1:N21 containing the data list Where A2:A21 contains Employee Names B1:N1 contains Wk01, Wk02,....Wk13 B2:N21 contains the grid of assigned employees On Sheet2.... A2:A14 contains Wk01, Wk02,....Wk13 B1:G1 contains 1,2,3,4,5,6 Put this ARRAY FORMULA* in B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="M",ROW(Sheet1!$B$2:$B$21)),B$1)) Or...alternatively....this ARRAY FORMULA* B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,MATCH($A2,Sheet1!$B$1:$N$1,0))="M",ROW( Sheet1!$B$2:$B$21)),B$1)) (Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter].) Copy B2 and paste into C2:G2 Then...Copy B2:G2 and paste into A3:G14 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: I have a list of 20 names in Column A of my spread sheet. Then I have 13 columns which represent 13 weeks (1/4 of a year). Each week there are 6 people assigned to be moderators and I place the letter M in 6 different rows within the column for the specific week. I also put other letters within the columns to alert me as to who are participants vs moderators in any individual week. If I want to generate a list of the 6 moderators each week is there a function I can use that will identify the 6 row numbers that have the letter M in them? Then I can use those row numbers to print myself a list of the names that correspond with those row numbers. My final goal is to be able to generate a list that will have 13 weeks down the left side and to the right of each week number will be the 6 names of the moderators for that week. Essentially a schudle for a quarter of the year. Thanks Carl |
Row numbers containing specific value within a column
Ron,
Horray! It worked. Thanks so very much for your patience and help. One last question. Right now my model has weeks named as wk01,wk02,ect. When I do my final schedule I would like to be able to identify the exact date for each of the weeks, ie. Starting in 2007 we have meetings once a weeks and I would like to show week 1 as say Jan 2, week 2 as jan 9, week 3 Jan16, week6 as Feb 6. Is this something I can do with a minor modification to the formula you gave me? If it is not something easily modified I can work around it . Tyhanks again. Carl "Ron Coderre" wrote: Sorry for the delay, Carl Here are some of your Sheet 1 values: wk01 wk02 wk03 And here are some of your Sheet2 values: w1 w2 w3 See a slight difference?..... Thought so! Sheet2 should have the same values as Sheet1 Wk01, instead of w1 or even W01 ...the key is that the right 2 characters of the Sheet2 week references must be digits. Does that help? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, I copied and pasted your formula directly into my spreadsheet model and then did the ctr/shift/enter to make it an array formula and it still didn't work. I then started with a new workbook from scratch and typed in the formula and I still get a value error. My wife tried it on her own with the second formula and that didn't work eitheer. Here is the snapshot of my model: wk01 wk02 wk03 carl tom m m rayp m raym m al m ralph harvey m m carol m Karl m bob m jerry m steve m larry m skip wade m jim m susan janet marcie m jackie m m 1 2 3 w1 #VALUE! w2 w3 w4 w5 w6 w7 w8 w9 w10 w11 w12 w13 =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="m",ROW(Sheet1!$B$2:$B$21)),B$1)) When I do a copy and paste it doesn't show the { } for an array formula but they are there in the spreadsheet. Is there a way for me to attach my model to a post here that you could see what might be happening? Carl "Ron Coderre" wrote: Did you try building the sample model first to make sure that the basic concept and formulas are functional? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, Thanks for your suggestion, I trid both of the formulas but both came back with n/a as an error message. I tried to rview the information on these functions in excel help but I was unable to find whatever I may be doing wrong to keep the formula from working. I tried to email you a copy of my spreadsheet but it came back as undeliverable. Carl "Ron Coderre" wrote: See if this example gets you headed in the right direction... With Sheet1, cells A1:N21 containing the data list Where A2:A21 contains Employee Names B1:N1 contains Wk01, Wk02,....Wk13 B2:N21 contains the grid of assigned employees On Sheet2.... A2:A14 contains Wk01, Wk02,....Wk13 B1:G1 contains 1,2,3,4,5,6 Put this ARRAY FORMULA* in B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="M",ROW(Sheet1!$B$2:$B$21)),B$1)) Or...alternatively....this ARRAY FORMULA* B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,MATCH($A2,Sheet1!$B$1:$N$1,0))="M",ROW( Sheet1!$B$2:$B$21)),B$1)) (Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter].) Copy B2 and paste into C2:G2 Then...Copy B2:G2 and paste into A3:G14 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: I have a list of 20 names in Column A of my spread sheet. Then I have 13 columns which represent 13 weeks (1/4 of a year). Each week there are 6 people assigned to be moderators and I place the letter M in 6 different rows within the column for the specific week. I also put other letters within the columns to alert me as to who are participants vs moderators in any individual week. If I want to generate a list of the 6 moderators each week is there a function I can use that will identify the 6 row numbers that have the letter M in them? Then I can use those row numbers to print myself a list of the names that correspond with those row numbers. My final goal is to be able to generate a list that will have 13 weeks down the left side and to the right of each week number will be the 6 names of the moderators for that week. Essentially a schudle for a quarter of the year. Thanks Carl |
Row numbers containing specific value within a column
Ron,
Thanks for your help. The spreadsheet si working as expected. I tried to make one modification. Instead of using wk01, wk02,etc as the labels for my 13 week schedule, I tried to put in actual dates like jan 2, jan 9,etc. When I do that then I get error messages. I tried using both a general format and a custom format with mmm-dd and it still doesn't work. Is there something unique to the formula that keeps it from working with dates? I can work with spreadsheet the way it is but I was just trying to upgrade my understanding of how to use the formulas. Thanks. Carl "Ron Coderre" wrote: Sorry for the delay, Carl Here are some of your Sheet 1 values: wk01 wk02 wk03 And here are some of your Sheet2 values: w1 w2 w3 See a slight difference?..... Thought so! Sheet2 should have the same values as Sheet1 Wk01, instead of w1 or even W01 ...the key is that the right 2 characters of the Sheet2 week references must be digits. Does that help? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, I copied and pasted your formula directly into my spreadsheet model and then did the ctr/shift/enter to make it an array formula and it still didn't work. I then started with a new workbook from scratch and typed in the formula and I still get a value error. My wife tried it on her own with the second formula and that didn't work eitheer. Here is the snapshot of my model: wk01 wk02 wk03 carl tom m m rayp m raym m al m ralph harvey m m carol m Karl m bob m jerry m steve m larry m skip wade m jim m susan janet marcie m jackie m m 1 2 3 w1 #VALUE! w2 w3 w4 w5 w6 w7 w8 w9 w10 w11 w12 w13 =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="m",ROW(Sheet1!$B$2:$B$21)),B$1)) When I do a copy and paste it doesn't show the { } for an array formula but they are there in the spreadsheet. Is there a way for me to attach my model to a post here that you could see what might be happening? Carl "Ron Coderre" wrote: Did you try building the sample model first to make sure that the basic concept and formulas are functional? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, Thanks for your suggestion, I trid both of the formulas but both came back with n/a as an error message. I tried to rview the information on these functions in excel help but I was unable to find whatever I may be doing wrong to keep the formula from working. I tried to email you a copy of my spreadsheet but it came back as undeliverable. Carl "Ron Coderre" wrote: See if this example gets you headed in the right direction... With Sheet1, cells A1:N21 containing the data list Where A2:A21 contains Employee Names B1:N1 contains Wk01, Wk02,....Wk13 B2:N21 contains the grid of assigned employees On Sheet2.... A2:A14 contains Wk01, Wk02,....Wk13 B1:G1 contains 1,2,3,4,5,6 Put this ARRAY FORMULA* in B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="M",ROW(Sheet1!$B$2:$B$21)),B$1)) Or...alternatively....this ARRAY FORMULA* B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,MATCH($A2,Sheet1!$B$1:$N$1,0))="M",ROW( Sheet1!$B$2:$B$21)),B$1)) (Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter].) Copy B2 and paste into C2:G2 Then...Copy B2:G2 and paste into A3:G14 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: I have a list of 20 names in Column A of my spread sheet. Then I have 13 columns which represent 13 weeks (1/4 of a year). Each week there are 6 people assigned to be moderators and I place the letter M in 6 different rows within the column for the specific week. I also put other letters within the columns to alert me as to who are participants vs moderators in any individual week. If I want to generate a list of the 6 moderators each week is there a function I can use that will identify the 6 row numbers that have the letter M in them? Then I can use those row numbers to print myself a list of the names that correspond with those row numbers. My final goal is to be able to generate a list that will have 13 weeks down the left side and to the right of each week number will be the 6 names of the moderators for that week. Essentially a schudle for a quarter of the year. Thanks Carl |
Row numbers containing specific value within a column
Carl
A few points: 1)The formula I posted is picking the last 2 characters from the week reference and using that value to offset Col_A on Sheet1 and look for "m"s. 2) Excel stores each date as the number of days the date is from 12/31/1899 01/01/1900 is number 1 12/08/2006 is 39,059 3)Since you want to use dates....the formula must also change. Using the same posted example, but with actual dates replacing week references, this is the new ARRAY FORMULA for Sheet2, B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,MATCH($A2,Sheet1!$B$1:$N$1,0))="M",ROW( Sheet1!$B$2:$B$21)),B$1)) That formula uses the MATCH function find the position of the column heading date on Sheet1 that matches the row heading date on Sheet2. Does that help? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, Thanks for your help. The spreadsheet si working as expected. I tried to make one modification. Instead of using wk01, wk02,etc as the labels for my 13 week schedule, I tried to put in actual dates like jan 2, jan 9,etc. When I do that then I get error messages. I tried using both a general format and a custom format with mmm-dd and it still doesn't work. Is there something unique to the formula that keeps it from working with dates? I can work with spreadsheet the way it is but I was just trying to upgrade my understanding of how to use the formulas. Thanks. Carl "Ron Coderre" wrote: Sorry for the delay, Carl Here are some of your Sheet 1 values: wk01 wk02 wk03 And here are some of your Sheet2 values: w1 w2 w3 See a slight difference?..... Thought so! Sheet2 should have the same values as Sheet1 Wk01, instead of w1 or even W01 ...the key is that the right 2 characters of the Sheet2 week references must be digits. Does that help? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, I copied and pasted your formula directly into my spreadsheet model and then did the ctr/shift/enter to make it an array formula and it still didn't work. I then started with a new workbook from scratch and typed in the formula and I still get a value error. My wife tried it on her own with the second formula and that didn't work eitheer. Here is the snapshot of my model: wk01 wk02 wk03 carl tom m m rayp m raym m al m ralph harvey m m carol m Karl m bob m jerry m steve m larry m skip wade m jim m susan janet marcie m jackie m m 1 2 3 w1 #VALUE! w2 w3 w4 w5 w6 w7 w8 w9 w10 w11 w12 w13 =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="m",ROW(Sheet1!$B$2:$B$21)),B$1)) When I do a copy and paste it doesn't show the { } for an array formula but they are there in the spreadsheet. Is there a way for me to attach my model to a post here that you could see what might be happening? Carl "Ron Coderre" wrote: Did you try building the sample model first to make sure that the basic concept and formulas are functional? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, Thanks for your suggestion, I trid both of the formulas but both came back with n/a as an error message. I tried to rview the information on these functions in excel help but I was unable to find whatever I may be doing wrong to keep the formula from working. I tried to email you a copy of my spreadsheet but it came back as undeliverable. Carl "Ron Coderre" wrote: See if this example gets you headed in the right direction... With Sheet1, cells A1:N21 containing the data list Where A2:A21 contains Employee Names B1:N1 contains Wk01, Wk02,....Wk13 B2:N21 contains the grid of assigned employees On Sheet2.... A2:A14 contains Wk01, Wk02,....Wk13 B1:G1 contains 1,2,3,4,5,6 Put this ARRAY FORMULA* in B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="M",ROW(Sheet1!$B$2:$B$21)),B$1)) Or...alternatively....this ARRAY FORMULA* B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,MATCH($A2,Sheet1!$B$1:$N$1,0))="M",ROW( Sheet1!$B$2:$B$21)),B$1)) (Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter].) Copy B2 and paste into C2:G2 Then...Copy B2:G2 and paste into A3:G14 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: I have a list of 20 names in Column A of my spread sheet. Then I have 13 columns which represent 13 weeks (1/4 of a year). Each week there are 6 people assigned to be moderators and I place the letter M in 6 different rows within the column for the specific week. I also put other letters within the columns to alert me as to who are participants vs moderators in any individual week. If I want to generate a list of the 6 moderators each week is there a function I can use that will identify the 6 row numbers that have the letter M in them? Then I can use those row numbers to print myself a list of the names that correspond with those row numbers. My final goal is to be able to generate a list that will have 13 weeks down the left side and to the right of each week number will be the 6 names of the moderators for that week. Essentially a schudle for a quarter of the year. Thanks Carl |
Row numbers containing specific value within a column
Thanks again for your excellent and timely help.
Carl "Ron Coderre" wrote: Carl A few points: 1)The formula I posted is picking the last 2 characters from the week reference and using that value to offset Col_A on Sheet1 and look for "m"s. 2) Excel stores each date as the number of days the date is from 12/31/1899 01/01/1900 is number 1 12/08/2006 is 39,059 3)Since you want to use dates....the formula must also change. Using the same posted example, but with actual dates replacing week references, this is the new ARRAY FORMULA for Sheet2, B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,MATCH($A2,Sheet1!$B$1:$N$1,0))="M",ROW( Sheet1!$B$2:$B$21)),B$1)) That formula uses the MATCH function find the position of the column heading date on Sheet1 that matches the row heading date on Sheet2. Does that help? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, Thanks for your help. The spreadsheet si working as expected. I tried to make one modification. Instead of using wk01, wk02,etc as the labels for my 13 week schedule, I tried to put in actual dates like jan 2, jan 9,etc. When I do that then I get error messages. I tried using both a general format and a custom format with mmm-dd and it still doesn't work. Is there something unique to the formula that keeps it from working with dates? I can work with spreadsheet the way it is but I was just trying to upgrade my understanding of how to use the formulas. Thanks. Carl "Ron Coderre" wrote: Sorry for the delay, Carl Here are some of your Sheet 1 values: wk01 wk02 wk03 And here are some of your Sheet2 values: w1 w2 w3 See a slight difference?..... Thought so! Sheet2 should have the same values as Sheet1 Wk01, instead of w1 or even W01 ...the key is that the right 2 characters of the Sheet2 week references must be digits. Does that help? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, I copied and pasted your formula directly into my spreadsheet model and then did the ctr/shift/enter to make it an array formula and it still didn't work. I then started with a new workbook from scratch and typed in the formula and I still get a value error. My wife tried it on her own with the second formula and that didn't work eitheer. Here is the snapshot of my model: wk01 wk02 wk03 carl tom m m rayp m raym m al m ralph harvey m m carol m Karl m bob m jerry m steve m larry m skip wade m jim m susan janet marcie m jackie m m 1 2 3 w1 #VALUE! w2 w3 w4 w5 w6 w7 w8 w9 w10 w11 w12 w13 =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="m",ROW(Sheet1!$B$2:$B$21)),B$1)) When I do a copy and paste it doesn't show the { } for an array formula but they are there in the spreadsheet. Is there a way for me to attach my model to a post here that you could see what might be happening? Carl "Ron Coderre" wrote: Did you try building the sample model first to make sure that the basic concept and formulas are functional? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, Thanks for your suggestion, I trid both of the formulas but both came back with n/a as an error message. I tried to rview the information on these functions in excel help but I was unable to find whatever I may be doing wrong to keep the formula from working. I tried to email you a copy of my spreadsheet but it came back as undeliverable. Carl "Ron Coderre" wrote: See if this example gets you headed in the right direction... With Sheet1, cells A1:N21 containing the data list Where A2:A21 contains Employee Names B1:N1 contains Wk01, Wk02,....Wk13 B2:N21 contains the grid of assigned employees On Sheet2.... A2:A14 contains Wk01, Wk02,....Wk13 B1:G1 contains 1,2,3,4,5,6 Put this ARRAY FORMULA* in B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="M",ROW(Sheet1!$B$2:$B$21)),B$1)) Or...alternatively....this ARRAY FORMULA* B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,MATCH($A2,Sheet1!$B$1:$N$1,0))="M",ROW( Sheet1!$B$2:$B$21)),B$1)) (Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter].) Copy B2 and paste into C2:G2 Then...Copy B2:G2 and paste into A3:G14 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: I have a list of 20 names in Column A of my spread sheet. Then I have 13 columns which represent 13 weeks (1/4 of a year). Each week there are 6 people assigned to be moderators and I place the letter M in 6 different rows within the column for the specific week. I also put other letters within the columns to alert me as to who are participants vs moderators in any individual week. If I want to generate a list of the 6 moderators each week is there a function I can use that will identify the 6 row numbers that have the letter M in them? Then I can use those row numbers to print myself a list of the names that correspond with those row numbers. My final goal is to be able to generate a list that will have 13 weeks down the left side and to the right of each week number will be the 6 names of the moderators for that week. Essentially a schudle for a quarter of the year. Thanks Carl |
Row numbers containing specific value within a column
You're very welcome.....I'm glad we could resolve your issue..
*********** Regards, Ron XL2002, WinXP "carl43m" wrote: Thanks again for your excellent and timely help. Carl "Ron Coderre" wrote: Carl A few points: 1)The formula I posted is picking the last 2 characters from the week reference and using that value to offset Col_A on Sheet1 and look for "m"s. 2) Excel stores each date as the number of days the date is from 12/31/1899 01/01/1900 is number 1 12/08/2006 is 39,059 3)Since you want to use dates....the formula must also change. Using the same posted example, but with actual dates replacing week references, this is the new ARRAY FORMULA for Sheet2, B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,MATCH($A2,Sheet1!$B$1:$N$1,0))="M",ROW( Sheet1!$B$2:$B$21)),B$1)) That formula uses the MATCH function find the position of the column heading date on Sheet1 that matches the row heading date on Sheet2. Does that help? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, Thanks for your help. The spreadsheet si working as expected. I tried to make one modification. Instead of using wk01, wk02,etc as the labels for my 13 week schedule, I tried to put in actual dates like jan 2, jan 9,etc. When I do that then I get error messages. I tried using both a general format and a custom format with mmm-dd and it still doesn't work. Is there something unique to the formula that keeps it from working with dates? I can work with spreadsheet the way it is but I was just trying to upgrade my understanding of how to use the formulas. Thanks. Carl "Ron Coderre" wrote: Sorry for the delay, Carl Here are some of your Sheet 1 values: wk01 wk02 wk03 And here are some of your Sheet2 values: w1 w2 w3 See a slight difference?..... Thought so! Sheet2 should have the same values as Sheet1 Wk01, instead of w1 or even W01 ...the key is that the right 2 characters of the Sheet2 week references must be digits. Does that help? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, I copied and pasted your formula directly into my spreadsheet model and then did the ctr/shift/enter to make it an array formula and it still didn't work. I then started with a new workbook from scratch and typed in the formula and I still get a value error. My wife tried it on her own with the second formula and that didn't work eitheer. Here is the snapshot of my model: wk01 wk02 wk03 carl tom m m rayp m raym m al m ralph harvey m m carol m Karl m bob m jerry m steve m larry m skip wade m jim m susan janet marcie m jackie m m 1 2 3 w1 #VALUE! w2 w3 w4 w5 w6 w7 w8 w9 w10 w11 w12 w13 =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="m",ROW(Sheet1!$B$2:$B$21)),B$1)) When I do a copy and paste it doesn't show the { } for an array formula but they are there in the spreadsheet. Is there a way for me to attach my model to a post here that you could see what might be happening? Carl "Ron Coderre" wrote: Did you try building the sample model first to make sure that the basic concept and formulas are functional? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: Ron, Thanks for your suggestion, I trid both of the formulas but both came back with n/a as an error message. I tried to rview the information on these functions in excel help but I was unable to find whatever I may be doing wrong to keep the formula from working. I tried to email you a copy of my spreadsheet but it came back as undeliverable. Carl "Ron Coderre" wrote: See if this example gets you headed in the right direction... With Sheet1, cells A1:N21 containing the data list Where A2:A21 contains Employee Names B1:N1 contains Wk01, Wk02,....Wk13 B2:N21 contains the grid of assigned employees On Sheet2.... A2:A14 contains Wk01, Wk02,....Wk13 B1:G1 contains 1,2,3,4,5,6 Put this ARRAY FORMULA* in B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,--RIGHT($A2,2))="M",ROW(Sheet1!$B$2:$B$21)),B$1)) Or...alternatively....this ARRAY FORMULA* B2: =INDEX(Sheet1!$A$1:$A$21,SMALL(IF(OFFSET(Sheet1!$A $2:$A$21,0,MATCH($A2,Sheet1!$B$1:$N$1,0))="M",ROW( Sheet1!$B$2:$B$21)),B$1)) (Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter].) Copy B2 and paste into C2:G2 Then...Copy B2:G2 and paste into A3:G14 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "carl43m" wrote: I have a list of 20 names in Column A of my spread sheet. Then I have 13 columns which represent 13 weeks (1/4 of a year). Each week there are 6 people assigned to be moderators and I place the letter M in 6 different rows within the column for the specific week. I also put other letters within the columns to alert me as to who are participants vs moderators in any individual week. If I want to generate a list of the 6 moderators each week is there a function I can use that will identify the 6 row numbers that have the letter M in them? Then I can use those row numbers to print myself a list of the names that correspond with those row numbers. My final goal is to be able to generate a list that will have 13 weeks down the left side and to the right of each week number will be the 6 names of the moderators for that week. Essentially a schudle for a quarter of the year. Thanks Carl |
| All times are GMT +1. The time now is 10:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com