1

I asked a question on StackOverflow: Google Sheets: Join two tables together

I had to figure out how to join two tables together dynamically. I got an answer from @MattKing on how to solve my problem and his solution worked on the sample data that I had created. However, his solution merges two tables together in a single query() and I can't understand how that works so I need help understanding that formula. I can't implement it on my actual sheet unless I understand how it works.

Sample sheet where the formula is implemented: https://docs.google.com/spreadsheets/d/1nKQXHwVO8KjsOy5qvjzh-s-YYRUYlAoH-3aXYbwcKsA/edit?usp=sharing

The formula that I need to understand: =ARRAYFORMULA(QUERY({A3:G;{H4:I,IFERROR(ROW(H4:H)/{0,0,0,0}),J4:J}},"select Col1,Col2,SUM(Col3),SUM(Col4),SUM(Col5),SUM(Col6),SUM(Col7) where Col1 is not null group by Col1,Col2 label SUM(Col3)'Impressions',SUM(Col4)'Clicks',SUM(Col5)'Cost',SUM(Col6)'Leads',SUM(Col7)'Offline Leads'"))

Part of that formula that I'm confused about: QUERY({A3:G;{H4:I,IFERROR(ROW(H4:H)/{0,0,0,0}),J4:J}}

I'd appreciate your help in figuring out this awesome formula!

player0
  • 124,011
  • 12
  • 67
  • 124
Abhay
  • 827
  • 9
  • 34

1 Answers1

2

you can see what's going on if you run this formula:

=ARRAYFORMULA({A3:G10; {H4:I10, IFERROR(ROW(H4:H10)/{0,0,0,0}), J4:J10}})

but basically it's a way of offsetting columns with a division of zeros which will output #DIV error because anything divided by 0 is considered as illogical in google sheets. so... 

=ROW(H4:H)

will give you row numbers on each row starting from 4 eg.:

4
5
6
...

that is then divided by 4 columns / 4 zeros which results in array of:

#DIV | #DIV | #DIV | #DIV
#DIV | #DIV | #DIV | #DIV
#DIV | #DIV | #DIV | #DIV
...

that is wrapped in IFERROR so all #DIV errors are nullified which means you are left of with 4 empty columns

so we have whatever is in H4:I then we continue with 4 empty columns and then the last column is J4:J so this way you got 7 columns in total

then these 7 columns are put in array again with whatever is in A3:G columns like:

A:A   B:B   C:C   D:D   E:E   F:F   G:G
H:H   I:I   #DIV  #DIV  #DIV  #DIV  J:J

the reason why you need this to be like presented above is because QUERY requires aggregation of columns when you use operations like sum, min, max, avg

player0
  • 124,011
  • 12
  • 67
  • 124