Need help building website filter/sort for Excel data

Hello all,

I am coming to the end of a lengthy research project and am seeing the fruits of my work over the passed 24 or so months. Through a lot of surveying, both in person and digitally, my peers and I have collected a large (20,000+ responses) pool of data. Hard work does pay off, but now we are in a bit of a dilemma. The data is fantastic and the findings/correlations are really interesting (of course I am biased); best of all it is very easy to manipulate in Excel. I can quickly use a Pivot Table or custom sort on the data to highlight key findings.

Yet, we are now looking for a way to publish our data online and have people come to their own conclusions about our findings. We will be presenting at some conferences and would love to have a few workstations set up where the user can enter in certain criteria, hit a "Search" button, and then have the results come up without needing to know anything about Excel.

I've attached a set of example data and a very crude screen shot of what the end goal is. For example: I know nothing about Excel but want to find out what women, between the ages of 30 and 35, who have less than or equal to 2 children thinks the minimum wage is. From the data in Excel, I see there are two responses that match the criteria, $7.50 and $9. I would want to type the same info into the crude screen shot and come up with the same result. It would be totally acceptable / potentially good to have the response to all questions come up instead of just "$7.50" and "$9.00". If that is easy to select for then would be fantastic and the team and I could figure what is best.

A legend from the screenshot:

A = a drop down menu that could come directly from a data validation in Excel if there was one there.
B = a drop down for operators (less than, greater than, equal to, etc...).
C = a free data input field for numbers or text if appropriate. Ideally, once I learn how to build the data out to be able to search it, it would be ideal to search other area's than just numbers [ex: locations]
D = a free data input field that is only required if the operator selected is "between".

I am looking for ideas as well as too see if this is something that could fairly easily be done or would involved advanced programming knowledge.No one in the group is a programmer by nature while a few of us may dabble in Python or a different language. I'm not even sure what a first step would be but am guessing it would involve working out of .csv extension. Any help would be HUGELY appreciated!!

Please feel free to ask any clarifying questions. The real data is a lot more robust but if I can learn on this basic model I feel confident I could extend the scope.

Thanks again!!!

Hi RSK70

I am surprised that no one has picked up on this as uou have a pretty good (I was going to say perfect) example of what pivot tables are for.

I have set up the pivot table for minimum wage. For additional views on minimum wage, click on the pivot table area which will bring up the "Pivot table field list" on the right side of the screen. Right click on minimum wage and "Add to values". Click on the values at the bottom right and go to "Value field setting" where you can select (say) stddev (but why you would want to, I cannot imagine!)

You can then set up a front sheet which looks like your image where the user can select what goes in (locking all other cells might be a good idea) and then can be shown what it produces (without having to scroll across lots of columns.

You also might want to set up cell(s) that show the total population results, rather than the filtered figures.

You can have endless hours of fun, trying to decide what people will want to see.

Let me know if you require any further explanations of the foregoing.

I am no pivot table expert - so anyone else should feel free to make suggestions.

Regards
Alastair

Hi RSK70

I may have been a little precipitous in my reply.

I have now started a front sheet so you can see how it works. Again, I have only done the results for Minimum Wage. Others can easily be added.

A word of warning - the data validation lists must contain only values that appear in the main list. If you try to input (say) an age that is not in the pivot table the system will stop and complain. So long as the value is in the list, everything is OK.

Enter the filters and press the submit button for an instantaneous result. I have not got round to the entering a range - but hey, must leave something for the others.

Regards
Alastair

Thanks so much for the help Alastair! It is definitely a start as well as a wake up call that I need to step my game up and learn how to make macros. I appreciate the template, I've copied it off and am trying to manipulate it with somewhat success. Had a great idea on paper but didn't work with the PivotTable haha. I tried setting up operators to build a range and then plug the range in but no dice.