Forum Discussion
Re: Analyse survey results completed twice over time of support
I'm not looking to compare year on year but rather start to end so it could be 1 year or it could be 1 month.
If I'm understanding your "Unique Reference" comparisons correctly, to take just one example, 2CN, you're talking about a first test at 1:25 p.m. on 9/29/22 as compared with a second completion approximately TWO HOURS later, certainly not a year, but not even close to a month. I.e., a meaningless gap.
That's what confuses me......is the data not real?
For a normal Pivot Table, you want all of the data to be in a single table. You then want to have a single column in that table that differentiates instances. Power Query and Power Pivot Tables (I have no experience with either of them) make it possible to combine separate tables into a single Pivot or single query. But for such things as the Pivot Table, you don't need to do that.
I've just realised I can add this into changes part of the table on the End tab too and then show the change in 2 columns rather than the 1 VLookup with the sum. Again though, it feels like there's probably a better way.
One of the delightful things about Excel (and maybe it's a frustrating thing too, at times) is that there are always going to be multiple ways to get from point A to point B, different functions that can be used, different graphs to illustrate results. My sense (from what you've written as well as from the spreadsheet you've shared) is that you are the best one to address your need. You clearly are willing to play around with the data (in a healthy way), to structure it this way and that, to see whether or not this way is clearer than that. In all candor, that's what I'd have to do too.
I was involved in a tele-conversation just last week with some of the other regular posters [those who answer questions here], and we agreed that sometimes we need to just encourage people--like you--who are asking questions in this forum, ...encourage you to just play around, experiment. There truly isn't "one best way" to go from your raw data to a clear conclusion. There are multiple ways...if I were sensing in you a person who didn't know how to write a formula, it'd be a different case, but you are clearly comfortable with VLOOKUP, tables, line charts.
If I were to give you a suggestion (just to make your formulas easier to read), it would be to use some simpler headings (keep the wordy ones for final reports) in the tables and formulas. Maybe become familiar with named ranges, the relatively new LET function, maybe FILTER. I've mentioned Pivot Tables, and if you're not familiar with them, you should be. But mostly I'd encourage you to keep going and learn on your own. Come here with specific questions if you can't make a function work, or don't know what function you need to accomplish a given purpose; your question about the best way to analyze YOUR data, though, is a question that you are best qualified to answer.