Count how many times certain text combinations occurs in certain columns

1
votes

I have a data set with multiple columns and roughly 1000 rows. I need to find out how many times certain combinations of columns can be found within the data set.

In my example below, columns A:B represents the raw data set. In C2 I have a formula that finds all non-unique combinations from columns A:B. What I need is a formula that counts how many times combinations in columns C:D are found within columns A:B. The desired output should be in ColE.

Pleas see this example!

2
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup

2 Answers

0
votes

you can do it all in one go... delete columns C, D, E and use this formula:

=ARRAYFORMULA(QUERY({A2:B, A2:A&B2:B}, 
 "select Col1,Col2,count(Col3) 
  where Col1 is not null 
  group by Col1,Col2 
  order by count(Col3) desc 
  label count(Col3)''"))

0

for a selected combination only use this formula in E2 cell:

=ARRAYFORMULA(IFERROR(VLOOKUP(C2:C&D2:D, QUERY({A2:A&B2:B}, 
 "select Col1,count(Col1) 
  where Col1 is not null 
  group by Col1  
  label count(Col1)''"), 2, 0)))

0

0
votes

It's always better to share a copy of your spreadsheet, but try entering in E1

={"Count"; ArrayFormula(IF(LEN(C2:C), VLOOKUP(C2:C&D2:D, query({A2:A&B2:B, A2:B}, "Select Col1, count(Col3) where Col1 <>'' group by Col1"), 2, 0),))}

and see if that works?

enter image description here

Note that you can create the same output (columns C, D and E) with a single formula

=query(ArrayFormula(query({A2:B, A2:A&B2:B}, "Select Col1, Col2, count(Col3) where Col1 <>'' group by Col1, Col2")), "where Col3 >1 label Col1 'Value 1', Col2 'Value 2'")

enter image description here