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.
