Forum Discussion
dliber2792
Jun 11, 2024Copper Contributor
Form to Excel Filter function for dental procedure log
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 tha...
dliber2792
Jun 13, 2024Copper Contributor
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.
Martin_Angosto
Jun 14, 2024Iron Contributor
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