Excel - COLUMN Function Problem - Expert Solution

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
Excelchat Expert 13/06/2018 - 02:14
HI welcome!
User 13/06/2018 - 02:14
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
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc