 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
The Problem
I'm recording attendance at an event which is attended by the same people each week. Using a Google Form the attendee completes they have attended. The form records their Organisation email address(must be logged in, so this is always unique to that person),it also records the time and date they submitted and their name (Which isn't always spelt the same!!)
So using this data I want to populate another sheet which is just a list of the email address and names down the side and the dates of the sessions across the top.
So ideally joe.bloggs@bob.com completes the form the 1/11/2021 this is recorded on the response sheet.
On the other sheet I want it to see this response and add a "1" where the date and email match
Does that make sense. At the moment I'm going down 120 responses and matching the email address and ticking the other sheet. I'm sure there is some fancy system that will do this but....
Thanks
You probably want to be using Vlookup.
It will look something like this (your columns will vary)
=VLOOKUP(A3,D$3:E$1000, 2, FALSE)
Ah to add to the confusion it is the same spreadsheet each week, so I have responses for the 1/11, 8/11, 15/11 ect
You could probably do a pivot table too with date Columns, email rows and count of date value.
Pivot table could be a winner thank you both!!
Which version of Excel?
If you've got access to power query extension, that is the best way to join your datasets. That can then be fed into a pivot table or used to dynamically populate a tab. Much better than fannying about with vlookups
In sheets you can do everything in appscript (JavaScript) but there is a learning curve and you need some dodges to handle dynamically extending data