Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / November 2006

Tip: Looking for answers? Try searching our database.

Row numbers containing specific value within a column

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
carl43m - 27 Nov 2006 17:36 GMT
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 Coderre - 27 Nov 2006 18:19 GMT
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
[quoted text clipped - 12 lines]
> Thanks
> Carl
carl43m - 27 Nov 2006 18:32 GMT
Thanks--I'll go try it out today.
Carl

> See if this example gets you headed in the right direction...
>
[quoted text clipped - 46 lines]
> > Thanks
> > Carl
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.