Row numbers containing specific value within a column

[Guest]

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

 

[Guest]

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

 

[Guest]

Thanks--I'll go try it out today.
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

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

 

[Guest]

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

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

Did you try building the sample model first to make sure that the basic
concept and formulas are functional?

***********
Regards,
Ron

XL2002, WinXP

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

:

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


:

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

 

[Guest]

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

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

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

Did you try building the sample model first to make sure that the basic
concept and formulas are functional?

***********
Regards,
Ron

XL2002, WinXP


:

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

:

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


:

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