Count how many times certain text combinations occurs in certain columns
Miika K
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.
player0
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)''"))
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)))
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
Related
Related Related
- 1
Pandas: Is there a way to count how many times a certain valued row occurs after another set of valued rows
- 2
Count how many different combinations add up to certain value
- 3
Count how many times a certain value per user has changed
- 4
Recursion function to count how many times a certain sequence appears
- 5
Look for a certain String inside another and count how many times it appears
- 6
Count how many times a column contains a certain value in Pandas
- 7
Play Framework: Count how many times a key exists in a JSON tree and how many times is set to a certain value
- 8
How to count how many times a string occurs
- 9
How to find how many times a certain string appears in the whole Excel workbook in certain columns?
- 10
How to count times of appearance of a certain word in a div full of text in JavaScript?
- 11
Is there any way to count the number of times a certain value occurs in TypeScript array?
- 12
Count how many times appears text across columns
- 13
How to count how many times a meta_value appears in a column by certain meta_key?
- 14
Excel - how to count how many times a word appears within a certain date range
- 15
How do I get a count of how many times a certain number is inside of a json file using php?
- 16
Is there a way to count how many times a function mock was called with a certain set of arguments?
- 17
R: Count how many times value has occured before within certain range of rows
- 18
Count how many times certain fields in records with repeating values are found (or not found) in another table
- 19
Count how many rows satisfy a certain filter
- 20
how to count how many times a string occurs in a column using pandas
- 21
XQuery: How to count how many times a value occurs in sequence
- 22
How to count how many times a number occurs in each column of a file?
- 23
Numpy, How to count how many times a string occurs
- 24
EXCEL: Can I get a count of a certain value from columns with certain headings, only from the columns with certain text?
- 25
count how many times each item occurs in a dictionary
- 26
Count how many times a time occurs within a date range
- 27
Count how many times a value occurs across tables
- 28
Count how many times specific element occurs in array
- 29
Python: Count how many times a word occurs in a file
Comments