How to run reports based on mutiple criteria met? Index match?

[YUSATrain]

x-posted to MrExcel: https://www.mrexcel.com/forum/excel-...dex-match.html

--------
Good Morning everyone!

Thanks for taking the time to try and help. I very much appreciate it.

This has been a rather constant headache for a few weeks. I am attempting to create a training tracker in which reports can be ran based on multiple criteria. Currently, it is setup with VLOOKUP formulas and a drop-down menu to pull the data to helper cells, then charts which are directed to that data to automatically populate based on which training is selected in the menu.

However, what I need to it to, is to be able to run reports based on multiple criteria. Say, Training Topic, Department and Hire Date. Ideally, it would only pull the data from the "Training Data" tab that matched all 3 of those criteria, this limiting the graph to only training completed on this topic, on this day, with this department.

I have looked into index-match formulas, but it appears that those are not going to work as it pulls one piece of data based on multiple criteria. (i.e First Name, Last Name and DOB returns the salary of that individual) I need it to pull all data in the list that matches the criteria in such a way that the graph can utilize it. Is this simply outside of the capabilities of excel? I've already succumbed to the fact that I'm probably going to have to completely rework this sheet one way or another so any insight into the issue or solution would be much appreciated.

Here is the excel sheet if you would like to see what I am talking about. REDACTED (File is now attached directly, it was too large due to the cover page containing a high-res photo.)

Thanks!
Chance

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[YUSATrain]

The above has been fixed. My apologies. The file was too large due to the fact that I had a high-res picture on the cover page. It has been removed and now fits within the filesize constraints. Thanks.

[JeteMc]

Hello YUSATrain and Welcome to Excel Forum.
I am returning a very simplified version of the file with my thoughts of how to accomplish the objective stated in post #1.
Data Validation is used to chose both the department and training course.
I guessed that you wanted to group by date of hire as in those employees hired since 2010, those hired between 2000 and 2010 etc. as opposed to only those hired on a specific date, many of which include times, resulting in singular outcomes (not very useful statistically IMO).
The formula for Passed (Failed is similar) is:

Please Login or Register  to view this content.

The formulas for the percentage ranges are similar to:

Please Login or Register  to view this content.

I made some changes in data layout as I don't see the need to type in both numeric values for attendance and score, however I think that a solution is workable if that data structure is important.
Let us know if you have any questions.

[YUSATrain]

Thank you very much, JeteMc! That is wonderful. I appreciate you taking the time to rework the data into a usable example, that helps me out a ton. I will have to look at this further and dissect it a bit in order to modify my original sheet in such a way to utilize the formula's that you've provided to me. I'm about chest-deep in other things at the moment before I head out for the weekend. Busy busy Friday for me. However, I will be looking at this much more in-depth on Monday.

As for the attendance data and score data, it is important that both are present as it's been requested that the training tracker be able to generate reports for both attendance (scheduled, completed and no shows) as well as the scores for the assessment tests provided after the associates have completed the training. The reports are supposed to be available as a whole, as well as for a particular department or date of hire/date of training. This allows us to better analyze the data in order to show completion and performance dept vs dept or associate tenure. Much as you've done for the score percentage data. I think that the layout you have here will be plenty to spark some similar ideas for the attendance data.

However, if you've got any ideas for a better way to mark down attendance aside from my "1, 2, 3" format I am more than happy to listen. Perhaps it would be better kept to a letter value such as P, A and N for Passed, Attended and No Show respectively.

Thank you again for your efforts to help me with this issue! I will definitely be adding rep+ for what it's worth!

[JeteMc]

Thanks for the feedback. After you have a chance to take a closer look at the file/formulas on Monday, or whenever, get back to us and we can work on fine tuning/broader application. Hope you have a blessed day.

[YUSATrain]

Okay, so my day has subsided just a slight bit. Enough to allow me to take a closer look at the sheet that you attached above. I see now how the different arrays link together, although unfamiliar with the =SUMPRODUCT formula... With a quick Google search it appears that this formula can be used much the same with letters as it is with numerical values. However, I seems to have the syntax incorrect or something. It is only returning zeros.

I have copied your formula containing the arrays down a few rows in order to apply the same theory to tracking the attendance. I intend to utilize "A", "P" and "N".

I have modified the formula to read

Please Login or Register  to view this content.

Do you see what could be the issue with this formula? It's probably simple, but my inadequacies with this particular formula is leaving me blind to what the problem is. I have also re-attached the form that you sent me with my additions for reference.

Thanks!

[JeteMc]

Your formula works fine. The reason for the zeros is that the date in J2 is 1/1/2010 and the only alphabetic value in the "Rejects MRS-V" column is the "N" which occurs with an employee hired in 2004. If the date in J2 is changed to 1/1/2004 then cell J18 displays 1. Of course the other "P's" aren't counted because they aren't in the "Rejects MRS-V" column.
Let us know if you have any questions.

[YUSATrain]

I see! Solely due to the fact that I was hitting that 3 o' clock hump, I failed to notice that I had not moved the lookup values down to the new section in the formulas and thus was trying to modify it on the lower of the two. Now that I'm looking at the formula again after the weekend, I understand how this works now concerning the "array=value" part of the formula, so I will be able to modify that portion to do what I need it to.

I think that I will be able to adapt this to my sheet without much issue. I just have one more question, is there a way to either select multiple values or ignore a filter? For example, say that I wanted to see data for all of the departments, not just one. Is that possible through data validation or SUMPRODUCT?

Thanks again! This has been incredibly helpful, before you introduced me to the SUMPRODUCT formula I was all but convinced that what I was trying to do was simply beyond the constraints of excel.

[JeteMc]

To get the values in J5:J10 the formulas are modified as in the following (J5):

Please Login or Register  to view this content.

Note: the only difference between the value_if_true and value_if_false is that the first criteria array and criteria (B$3:B$21=J$1) is removed from the value_if_true instance.
All formulas have been modified in the attached copy of the file.
Let us know if you have any questions.

[YUSATrain]

Thank you! I have fully implemented this into my sheet as well as the ability to filter by the Date of Training. I cannot express how grateful I am to you JeteMc. You have done what I thought to be impossible and I learned quite a bit from the experience.

I have attached the finished Training Tracker to this reply, sensitive information redacted, of course. Hopefully someone along the way will stumble on to this thread and be able to make use of our work here in the future. I know that I am certainly happy to have the headache subside surrounding this project.

The forum will not allow me to add more reputation to your profile until I do the same for some others, so with that I suppose I'll be perusing the forums a bit more. With any luck, maybe I'll stumble upon an unanswered question that I can help with.

I will go ahead and mark this thread as solved! Thanks a ton again, JeteMc!

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.