Row numbers containing specific value within a column

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

Ron Coderre said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
Did you try building the sample model first to make sure that the basic
concept and formulas are functional?

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

XL2002, WinXP


carl43m said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top