google-sheetsgoogle-sheets-querygoogle-sheets-macros
Copy cell data to second sheet based on matching data
I want to use data from a Google Form (Sheet 1) that is collecting in and out times and locations. I would like them in and out submission consolidated on a second sheet (Sheet 2) the end product. If email and date match place them in and out time stamp on a second sheet to be used for additional calculations and viewing. There are several examples out there and things that I have tried but nothing that I can find. This may not be possible in the current format if additional data is needed to filter it can be added. Thanks for the help in advance.
Sheet 1
A B C D
Timestamp Email Going to Out/In
1/29/2019 16:06:42 [email protected] Locker Out
1/29/2019 16:06:58 [email protected] In
1/29/2019 16:13:32 [email protected] Bathroom Out
1/29/2019 16:14:35 [email protected] Bathroom Out
1/29/2019 16:13:48 [email protected] In
1/29/2019 16:20:00 [email protected] In
Sheet 2
A B C D
Email Went to Out In
[email protected] Locker 1/29/2019 16:06:42 1/29/2019 16:10:58
[email protected] Bathroom 1/29/2019 16:13:32 1/29/2019 16:23:48
[email protected] Bathroom 1/29/2019 16:14:35 1/29/2019 16:20:00
Solution
={"Email", "Went to", "In", "Out";
{QUERY('Sheet 1'!A2:D,
"select B, C, A where C is not null and not D contains 'In'", 0),
ARRAYFORMULA(VLOOKUP(QUERY('Sheet 1'!A2:D,
"select A where C is null and D contains 'In'", 0), 'Sheet 1'!A2:D, 1, 0))}}
- Normalize Google Sheet table (1st step)
- Is there any way to do a bitwise shift right on a very large number in Google Sheets?
- How to calculate value based on tax bracket using Google sheet formula?
- Google script to dynamically addressing a cell in google sheet
- Auto numbering rows in GoogleSheet
- Error message while automating data entry from web app contact form to google sheet
- How do I send email to user by appending my domain name to the user name in Google Apps Script?
- How to split comma-separated values across multiple rows dynamically?
- Replace multiple strings in Google Spreadsheet with script
- How to count the amount of times a substring appears in a range of cells?
- Query with multiple data types
- Count If Range Matches Lookup in another tab
- Google Sheets Query where data in either of two columns matches the value in a cell populated by a multi-select dropdown
- Trying to combine words and math in a single cell in google sheets
- Counting the previous sample over multiple conditions throughout the range
- How to randomly select by type probability all items from a google sheet column range
- Sort result of function by RHS column
- Google Sheets Query - using another query to filter dates
- Skip formula if sheet does not exist in Google Sheets
- How to get data from public google sheet and more than 100 rows
- In Google sheets how can I Script the manipulation of the default Zoom?
- Google Sheets IMPORTDATA for xlsx files
- conditional formatting to highlight the highest value in a row compared to all the columns before it
- Google sheets summation formula, iterating until a new header on column A
- Using ARRAYFORMULA and INDIRECT to iterate rows and return as a block
- How to scroll to bottom of a google spreadsheet using selenium
- Calculate average days between dates continuously updating
- Conditional formatting in Google Sheets is recognizing some cell values that equal 0, not others
- Model / Year formula fails when a sequence starts stops then starts again
- Google Sheets: Sumif formula