Reputation: 417
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
Upvotes: 0
Views: 265
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.
Upvotes: 2
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'")
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
sheetCash 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
sheetUNIQUE({data_frame1, data_frame2})
PIVOT
and sort by date columnUpvotes: 1