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

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

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

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

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

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

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

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

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