**Question description:**

*This user has given permission to use the problem statement for this blog.*

I am working on scheduling rotation for a residency program. Lets say I have column â??Bâ?? titled July which has names of the rotations such as â??Cardiologyâ?, â??Nephrologyâ?, â??Floorsâ?, â??Vacationâ? etc. I have rows 1 to 65 with names of people.
I want to know in a different cell who is on cardiology in July and the formula should pull in all the names. How can I do this?

Solved by I. Y. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

User
13/06/2018 - 02:14

Hey

Excelchat Expert
13/06/2018 - 02:14

HI welcome!

User
13/06/2018 - 02:14

60

User
13/06/2018 - 02:16

Can you help me please?

User
13/06/2018 - 02:17

I've made the spreadsheet and need the formula

Excelchat Expert
13/06/2018 - 02:17

Ok, let me review your need

User
13/06/2018 - 02:17

In row 18 under Cardiology, I want to pull in all the names from B2:B9

Excelchat Expert
13/06/2018 - 02:18

I will take this off-line and prepare a suggested solution in EXCEL

User
13/06/2018 - 02:19

Thank you

Excelchat Expert
13/06/2018 - 02:24

[Uploaded an Excel file]

Excelchat Expert
13/06/2018 - 02:24

The best way to do this is to use the EXCEL table capability.

Excelchat Expert
13/06/2018 - 02:25

You can filter any row or column for the data you need.

User
13/06/2018 - 02:25

Hmmm. I know that could be done. But for the purposes of scheduling, I need to pull in the names

Excelchat Expert
13/06/2018 - 02:25

This is also easier to maintain

User
13/06/2018 - 02:26

I have many days and tabs and filters wont be feasible. Comparing and counting the rotations at the same time gets tricky in that case

User
13/06/2018 - 02:27

Hence, what I want to do is just pull in all the row names that match 'Cardiology'

User
13/06/2018 - 02:27

And so forth for Vacation, Nephrology

User
13/06/2018 - 02:27

It will make my life a LOT easier

Excelchat Expert
13/06/2018 - 02:27

The problem with trying to list with formulas, is you need to know how many rows, formulas are needed.

Excelchat Expert
13/06/2018 - 02:28

Need to pull data to a table

User
13/06/2018 - 02:28

Like row 2 to row 9

User
13/06/2018 - 02:29

There is no list function? or just return values from a row that matches 'Cardiology' in another column?

Excelchat Expert
13/06/2018 - 02:29

If you filter for Cardiology on 6/13, the result will be just those assinged.

Excelchat Expert
13/06/2018 - 02:30

Not really a list function in basic EXCEL that has open parameters (number of rows).

Excelchat Expert
13/06/2018 - 02:31

We can setup a PIVOT table that lets you isolate data a bit more (only month and department)

User
13/06/2018 - 02:32

Okay, that may help

User
13/06/2018 - 02:32

How do we do that?

Excelchat Expert
13/06/2018 - 02:35

I'm looking at the PIVOT and wont work well , since pivots want to count or add things.

User
13/06/2018 - 02:36

Ah shoot. I thought it would have been just a simple formula

Excelchat Expert
13/06/2018 - 02:36

The other option to use EXCEL as a database, as in this case, is to set up tables for each department.

User
13/06/2018 - 02:36

That is a good idea

User
13/06/2018 - 02:36

How can I do that?

Excelchat Expert
13/06/2018 - 02:37

I will set up and you can evaluate

User
13/06/2018 - 02:37

Thank you so much!

Excelchat Expert
13/06/2018 - 02:48

[Uploaded an Excel file]

Excelchat Expert
13/06/2018 - 02:48

this is just a variation of the table concept

Excelchat Expert
13/06/2018 - 02:48

but each tab is a separate department

Excelchat Expert
13/06/2018 - 02:49

given the time constraint , I set up 2 tables.

Excelchat Expert
13/06/2018 - 02:49

This is just a concept plan. you would need to adapt to your full shett.

User
13/06/2018 - 02:51

Oh thank you. Let me look into this.

User
13/06/2018 - 02:51

I think this may work

User
13/06/2018 - 02:51

Thank you so much!

Excelchat Expert
13/06/2018 - 02:51

You can also, look at Data Query capability in EXCEL.

Excelchat Expert
13/06/2018 - 02:52

It would take a lot to go thru on this chat line, but you are looking at database like functionality, and within EXCEL that is a likely place, But needs a lot of set up and query configuration.

Excelchat Expert
13/06/2018 - 02:53

Access database may really needed , depending on your data size.

User
13/06/2018 - 02:54

Hmmm

User
13/06/2018 - 02:54

If access is easier to setup, I don't mind

Excelchat Expert
13/06/2018 - 02:55

Not really easier, but more database functionality. However, there are exisitng templates that may already be very close to what you need.

User
13/06/2018 - 03:07

One last thing. If I go by the filter function, how can I move the filter to another row?

User
13/06/2018 - 03:07

My top row is locked with the dates. and I want the filter to be in the second row

Excelchat Expert
13/06/2018 - 03:08

The filter options are at the top of each column

Excelchat Expert
13/06/2018 - 03:08

Not rows

Excelchat Expert
13/06/2018 - 03:10

The name filter is set to suppress FALSE, these are all the rows that do not match the department name

Excelchat Expert
13/06/2018 - 03:10

You can FILTER for an individual or group of names as well , with FILTER selections on the top row

User
13/06/2018 - 03:13

Sorry I meant column

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*