How to join data from different sheets using Query in Google Sheets

Question

I am trying to make a simple cash book.

As shown in the picture, from the cash-In and Cash-Out sheets i need to consolidate and display in the monitor sheet according to the selected party.

It should be sorted by date.

sheet link: https://docs.google.com/spreadsheets/d/1BwtcDIJv_CiZ-7Ae8qgLr4Azttnn7pBIOoxxMxdi2fk/edit?usp=sharing

Do NOT share spreadsheets/images as the only source of data, to avoid closure of the question. Make sure to add input and expected output as plain text table to the question. Click here to create a table easily, which are easier to copy/paste as well. Also, note that your email address can also be accessed by the public, if you share Google files. — TheMaster
– TheMaster, Commented Jan 1, 2023 at 10:14

Harun24hr · Accepted Answer · 2023-01-01 11:06:34Z

2

Try this query-

=QUERY({QUERY('Cash In'!A2:C,"select A, C,0 where B='" & B1 & "'");
QUERY('Cash Out'!A2:C,"select A, 0, C where B='" & B1 & "'")},
"where Col1 is not null order by Col1 label Col1 'Date', Col2 'Cash In', Col3 'Cash Out'")

See sheet harun24hr.

edited Jan 1, 2023 at 11:06

answered Jan 1, 2023 at 10:59

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

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

Comments

Mehmet Güngören · Accepted Answer · 2023-01-01 10:58:53Z

This formula will solve your problem. First goto Monitor sheet then delete A3,B3 and C3 cells. Then put this formula on A3 cell.

=QUERY(UNIQUE({query('Cash In'!A1:C, "select A, C, 'Cash In' WHERE A is not null AND B ='"&$B$1&"'", 0);query('Cash Out'!A1:C, "select A, C, 'Cash Out' WHERE A is not null AND B ='"&$B$1&"'",0)}), "SELECT Col1, SUM(Col2) WHERE Col1 is not null group by Col1 PIVOT Col3 order by Col1 Label Col1 'Date'")

Get Cash In values query('Cash In'!A1:C, "select A, C, 'Cash In' WHERE A is not null AND B ='"&$B$1&"'", 0) and add a virtual column with Cash In with selected filter on B1 cell at Monitor sheet
Get Cash Out values query('Cash Out'!A1:C, "select A, C, 'Cash Out' WHERE A is not null AND B ='"&$B$1&"'",0)} and add a virtual column with Cash Out with selected filter on B1 cell at Monitor sheet
Then merge two data in a single result. (Vertically merge) UNIQUE({data_frame1, data_frame2})
In last step transpose PIVOT and sort by date column

Result:

Collectives™ on Stack Overflow

How to join data from different sheets using Query in Google Sheets

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related