CODEWITHSUNDEEP
filtergoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
TC76
TC76

Reputation: 860

"Merge" two sheets AND do math in one column

I have two sheets that have basically the same layout (a couple of columns are rearranged). What I'm wanting to do is take the data from one sheet and combine it with another. The trick that's keeping me from just FILTERing it and being done with it is that the "Qty" column needs to do some math.

The first sheet lists all items. The second lists "adjustments". So I might reduce one item by 50% or 100%. The goal is that the math will appear on this third/merged sheet and if an item is reduced by 100%, it will be completely removed.

projectTasks!A,B,C,D,E,G
projectTasksAdj!A,B,C,D,F,E
Adding projectTasks!E to projectTasksAdj!F

Main data sheet: projectTasks Adjustments: projectTasksAdj Merged sheet: reportsTasks

The columns are out of order in projectTasksAdj just for searching purposes.

Here's my sheet

Thank you very much for any guidance!

Upvotes: 0

Views: 49

Answers (2)

player0
player0

Reputation: 1

=ARRAYFORMULA(QUERY({A2:C4; 
                     B8:B9, A8:A9, C8:C9*-1}, 
 "select Col1,Col2,sum(Col3) 
  where Col1 is not null 
  group by Col1,Col2 
  label sum(Col3)''", 0))

0

Upvotes: 1

TC76
TC76

Reputation: 860

=arrayformula(QUERY(QUERY({projectTasks!A3:D,value(projectTasks!E3:E),projectTasks!F3:G;projectTasksAdj!A3:D,projectTasksAdj!G3:G,projectTasksAdj!I3:I,projectTasksAdj!E3:E},"Select Col1, Col2, Col3, Col4, Sum(Col5), Col6, Col7 where Col1 is not null group by Col1, Col2, Col3, Col4, Col6, Col7 label Sum(Col5) ''",0),"Select * where Col5>0",0))

Upvotes: 0

Related Questions