I haven't been able to find something like this. Hoping I can get an answer in Google Sheets! I have a two columns with values pulled from the same list of 5 items. (Each row is an item that has up to two 'types' that come from a 'types list' with 5 values)
For this example:
| Types |
|---|
| A |
| B |
| C |
| D |
| E |
| Example Column 1 | Example Column 2 |
|---|---|
| A | B |
| B | A |
| B | C |
| B | |
| D | E |
| D | B |
I want to create another table like below that will count up how many times a particular combination occurs regardless of order. Results would appear as below:
| Type 1 | Type 2 | Count |
|---|---|---|
| A | B | 2 |
| B | C | 1 |
| B | E | 0 |
| D | E | 1 |
I need this formula to count any time both values appear in either column together.
Really hoping I can get this into a simple table format, because I have another table where I will need to do similar that will be more complex so I'm hoping to avoid longer form counting tables if it can be avoided.
Any help would be much appreciated!
CodePudding user response：
try:
=ARRAYFORMULA(TRIM(QUERY(IF(A:A<B:B, A:A&" "&B:B, B:B&" "&A:A),
"select Col1,count(Col1) where Col1 <> ' 'group by Col1
order by count(Col1) desc label count(Col1)''")))
CodePudding user response：
Use this formula
=ArrayFormula(
{ "Type1","Type 2", "Count";
SPLIT(UNIQUE(FILTER(QUERY({IF(A2:A<B2:B, A2:A&" "&B2:B, B2:B&" "&A2:A)}), LEN(QUERY({IF(A2:A<B2:B, A2:A&" "&B2:B, B2:B&" "&A2:A)}))>2)), " "),
COUNTIF(QUERY({IF(A2:A<B2:B, A2:A&" "&B2:B, B2:B&" "&A2:A)}), "="&
UNIQUE(FILTER(QUERY({IF(A2:A<B2:B, A2:A&" "&B2:B, B2:B&" "&A2:A)}), LEN(QUERY({IF(A2:A<B2:B, A2:A&" "&B2:B, B2:B&" "&A2:A)}))>2)))})
