I’ve got a Google Sheets file with two separate tables. One table is on Sheet 2 and shows runners’ times for Track A. The other table is on Sheet 3 and has times for Track B. Both tables have the same layout with names in one column and times in another.
I want to create a new table on Sheet 1 that combines these two automatically. This new table should only display each runner’s fastest time, regardless of which track it was on. It should also mention which track the fastest time was achieved on.
Both tracks are the same length, so times can be compared directly. Is there a way to set this up so it updates automatically whenever the original tables change? I’m not sure how to approach this problem efficiently in Google Sheets.
Any help or formulas would be greatly appreciated. Thanks in advance!
hey grace, i’ve dealt with this before! you can use QUERY function to pull data from both sheets, then ARRAY_CONSTRAIN to limit results. something like:
=QUERY({Sheet2!A:B; Sheet3!A:B}, “SELECT Col1, MIN(Col2), 'Track ’ & CASE WHEN Col2 = MIN(Col2) THEN ‘A’ ELSE ‘B’ END GROUP BY Col1”)
might need tweaking but should get u started. good luck!
I’ve actually implemented something similar for a local running club I’m part of. Here’s what worked for us:
We used a combination of QUERY and ARRAYFORMULA functions. The key is to combine both datasets and then extract the information you need. Here’s a formula that should do the trick:
=ARRAYFORMULA(QUERY({Sheet2!A:B, ARRAYFORMULA(“A”); Sheet3!A:B, ARRAYFORMULA(“B”)}, “SELECT Col1, MIN(Col2), Col3 WHERE Col1 is not null GROUP BY Col1, Col3 LABEL MIN(Col2) ‘Fastest Time’, Col3 ‘Track’”))
This pulls data from both sheets, finds the fastest time for each runner, and indicates which track it was on. It’ll update automatically when you change the source data.
Just make sure your column headers match in both original sheets. You might need to tweak the column references depending on your exact layout. Hope this helps!
I’ve encountered a similar challenge in my work. Here’s a solution that should work well:
Use QUERY to combine data from both sheets, then ARRAYFORMULA with MIN to find the fastest time. You can achieve this with:
=ARRAYFORMULA(
QUERY({Sheet2!A:B,ARRAYFORMULA(“A”);Sheet3!A:B,ARRAYFORMULA(“B”)},
“SELECT Col1, MIN(Col2), MAX(Col3)
WHERE Col1 IS NOT NULL
GROUP BY Col1
ORDER BY MIN(Col2) ASC
LABEL MIN(Col2) ‘Best Time’, MAX(Col3) ‘Track’”)
)
This formula will automatically update when you modify the original tables. It combines data, finds the best time, and indicates the track. Adjust column references as needed for your specific setup.