Merging records from google sheets column

Question

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.

player0 player0 · Accepted Answer · 2020-08-23T09:46:03

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)''")

Merging records from google sheets column

1 Answers