From a multiline table I'm trying to extract (and count) all unique combinations between every two rows.
I have found a way by creating intermediate tables: 1) I use JOIN to create the combinations between each "city" and "zone" row. 2) I rearrange the result into one single column (as necessary for the following UNIQUE function) by entering a TRANSPOSE function every 7th row (because there are 7 columns). 3) I then use UNIQUE and COUNTIF in a third table for the final result.
Link to the example with data and desired result on first sheet, and my solution on the second sheet: Google Sheets file
As my final sheet will be rather large (~2000 rows, 40 columns, ~4000 unique combinations expected), my main problem with my own solution is the manual step to rearrange the city/zone combinations into one single column as preparation for the UNIQUE function.
Is there a way to achieve the same final result without the intermediate steps from my solution?
C11:
=ARRAYFORMULA(QUERY(TRANSPOSE(SPLIT(QUERY( TRANSPOSE( QUERY("☯"&QUERY(TO_TEXT(B1:H8),"skipping 2",0)&" "&QUERY(B2:H8,"skipping 2",0),,2^99)),,2^99),"☯"))," Select Col1, count(Col1) group by Col1",0))
Create two arrays with query's skipping, one containing cites and other containing zones
JOIN those arrays with a delimiter using Query headers
SPLIT and TRANSPOSE to create a single column
QUERY to create a frequency count.