CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
anwwwar
anwwwar

Reputation: 417

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

I am trying to make a simple cash book.

enter image description here

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

Upvotes: 0

Views: 265

Answers (2)

Harun24hr
Harun24hr

Reputation: 37155

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.

enter image description here

Upvotes: 2

Mehmet Güngören
Mehmet Güngören

Reputation: 2784

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: enter image description here

Upvotes: 1

Related Questions