Form to Excel Filter function for dental procedure log

[dliber2792]

Hi everyone! First time user here that can't figure out a complex problem in my mind:

I am in a dental residency with other coresidents where we have to create a log of all the dental procedures that we have completed. We would like to create a Microsoft Form that we can input the information for our procedures  (ie: which location, patient age, how many of X procedure...). That data would show up on an excel sheet under one tab that has all of our aggregate inputs but then there are individual tabs for each of the residents that filters out our procedures that we have completed. That way, we can individually see our progress under my own tab while the program director can see the aggregate in tab 1. I was given a step by step in the past but it's not working for me as to how to complete this. I'm very stuck! 

Hoping someone can understand what I am trying to do and help out please! We would really appreciate it! Happy to describe more if there is some confusion.

Thank you so much! 

[#2]

Hi @dliber2792 ,

You might be interested to introduce yourself to functions such as FILTER().

What I would suggest is to have a FILTER function in each of the individual tabs, filtering the whole dataset from the aggregate tab based on, for instance, the name of each resident. This would slightly change depending on how your data is structured.

I have created a sample document for you to check how FILTER function works in an example that might be suitable for you. Your aggregate data can either be written directly in a set range or rather in a structured Excel table. I have created an example for both possibilities, but I highly recommend that you convert your aggregate data into an Excel structured table. In this way, if there is an addition of logs, the filter function will still cover these new entries as you are referencing a whole column of a table and the ranges put in the formulation will automatically adjust.

Hope at least this gives you a beginning.

Martin

[#3]

Hi @Martin_Angosto 

This is actually fairly close if not exactly to the idea of what I need!! I'm not sure how or what functions you put in to be able for this transfer of data, but do you have an idea of what I can google or search on youtube for a step by step? The hard part is phsyically knowing what the functions that I have to put in to make this work. But this is exactly what I need. 

[#4]

way, we can individually see our progress under my own tab while the program director can see the aggregate in tab 1. I wa


Do you need everyone has his own view?
https://techcommunity.microsoft.com/t5/microsoft-forms/want-to-create-a-form-for-hiring-process-but-...

[#5]

Hello @dliber2792 

I am more than happy to share a quick step by step on this.

The FILTER function, which is the main function of my solution is aimed to filter a range of data based on a criteria you choose. What you want to do is to divide (or filter) a large data set into different tabs of a workbook. Each of these tabs will contain information of a specific resident in this case.

The logic behind then is to simply filter a large data set based on a chosen criteria that, in this case, will be the name of a resident. In that manner, the output will be a new -smaller- dataset containing values/data only for a certain match criteria (name of the resident), all extracted from the original full data set.

The FILTER() function has the following syntax:

=FILTER(array,include,[if_empty])

Where:

- array will be the full range of our original large data set to be filtered.

- include will be our criteria (the way we define what will be included. It is basically telling the function what to include in order to filter.

- if_empty is an optional argument which in this case we do not use.

In the workbook I attached I had provided you with different options to do this. As you could see, there is the option to work with a structured Excel table or rather with a range. The syntax for both options is the same, so I will directly explain the structured table one as it is the one I recommend using.

My formula defines:

=FILTER(Table1,Table1[Staff]="Resident 1")

As you can see, the "array" argument is filled with "Table1". Table1 is the name of my structured table in Excel and therefore I define my entire range to filter as it. I simply selected all data from the table (excluding the headers!). Then we have the "include" argument, which is filled with Table1[Staff]="Resident 1". What I do is to tell the function to include in the filtered table the column named Staff when it is exactly equal to the text "Resident 1". What I did is to select the entire column Staff (again, without the header).

The other formula that I put in my attached file is a simple VSTACK() function, which does nothing but to stack vertically two arrays. In this case, I stacked the headers with the previous filter function in order to make it more visual and have a complete correct structure. You can simply write the headers manually and then type the filter function in the row below, though. I just preferred to have it in a one-formula manner.

You can find more information about FILTER() and VSTACK() functions here:

https://support.microsoft.com/en-au/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759

https://support.microsoft.com/en-us/office/vstack-function-a4b86897-be0f-48fc-adca-fcc10d795a9c

I hope this helped!!

If you find it useful enough and it has solved your problem, please consider marking any of the replies as a best solution in order to help future users 🙂

Martin