How to combine data from multiple Google Sheets and organize/filter it?

Question

Here's my sample spreadsheet: https://docs.google.com/spreadsheets/d/1c-nXosPZvnEplFME6GHXlxuQUrVe4ImF5c5Go9_75DU/edit#gid=34769607

I use an API to import data from our time tracking app into Google Sheets. The "Project Details" sheet has most of the info about the projects, except for the hours spent on each project. So I have a "Project Hours" sheet that has the hours spent.

I want to combine these two things into one list in a separate "Main" sheet, as well as leave out columns that I don't care about. I really only care about the project name, the client name, the start date, end date, and total hours.

There must be a simple formula to use that gives me a clean list of all the projects and only shows me the data I care about, like QUERY or ARRAYFORMULA, but I'm not very familiar with those. Any help would be appreciated!

Erik Tyler · Accepted Answer · 2020-07-11 10:13:17Z

1

Craig. Yes, a QUERY with a VLOOKUP can accomplish this. I added a new tab (clearly marked as mine) to your example sheet. Being thorough, I wanted to include the column header in the last column as well, which makes the formula a bit longer than it would otherwise be in that it uses the IFERROR wrap on the VLOOKUP to assign the header for Row 1 (or otherwise leaves the last column blank if no match is found for a project name).

Here's the formula I used (though other approaches could certainly have been used as well):

=ArrayFormula({QUERY('Project Details'!A:Y,"Select B, Y, P, Q Where A Is Not Null"),IFERROR(VLOOKUP(QUERY('Project Details'!A:A,"Select * Where A Is Not Null"),'Project Hours'!A:E,5,FALSE),IF(ROW('Project Details'!A:A)=1,'Project Hours'!E$1,""))})

answered Jul 11, 2020 at 10:13

Erik Tyler

9,3952 gold badges9 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Craig Over a year ago

Hey Erik, sorry to revive this old thread, but I have a follow-up. Your formula has been working great! However, some projects in the Project Hours sheet show up twice, with different numbers in the total_hours column. This is due to the limitations of the API for the project tracking software we use. For those projects, the hours need to be added together, and VLOOKUP doesn't work for that. Do you have a suggestion for addressing the duplicate projects and adding the hours together?

Collectives™ on Stack Overflow

How to combine data from multiple Google Sheets and organize/filter it?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related