2

I am working on a Google sheet where I am using INDIRECT to get data from other tabs in the spreadsheet and list the unique ones followed by their frequencies.

There are multiple columns now with pair of word, count from each tab in the spreadsheet.

I want to merge the records such as all the unique value which are present in multiple sheets have single entry along with total frequency value equal to sum of all values in different tabs.

For e.g. col1 and col2 are prepared by using unique(indirect($tab)) and final_col here needs to be an in intersection of those two or multiple other columns (which i can hard code).

Can someone help suggesting what can be done here to get the final column.

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
CodeMonkey
  • 2,265
  • 9
  • 48
  • 94

1 Answers1

1

use:

=QUERY({A2:B4; C2:D4}, 
 "select Col1,sum(Col2) 
  where Col1 is not null 
  group by Col1 
  order by sum(Col2) desc
  label sum(Col2)''")

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • =QUERY({H2:I18; J2:K18, L2:M18}, "select Col1,sum(Col2) where Col1 is not null group by Col1 order by sum(Col2) desc label sum(Col2)''") I used above , however getting an error : In ARRAY_LITERAL, an Array Literal was missing values for one or more rows. Entire range here has values and is non null – CodeMonkey Aug 23 '20 at 18:23
  • @CodeMonkey instead of `H2:I18; J2:K18, L2:M18` use `H2:I18; J2:K18; L2:M18` notice the semicolons `;` – player0 Aug 23 '20 at 19:08
  • Gotcha it actually worked. Thank you so much. I have a follow up question to make it case sensitive. Should I create another question if you care about it ? And once again so much thanks @player0 – CodeMonkey Aug 30 '20 at 07:58