Trying to figure out a formula where I have lets say 5 columns of text data. I want to count how many times any 2 columncombinations of data appears in a2 contiguous columns appear in each row.
For exampleExample data:
A,A,A,B,C
A,B,B,A,A
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | A | A | A | B | C |
| 2 | A | B | B | A | A |
In this case I want to Count A,A showing up twice, A,B showing up twice, B,C showing up once etc.the example data above there are 8 range pairs (4 ranges per row):
| Paired Cells 1 | Paired Cells 2 | Paired Cells 3 | Paired Cells 4 | |
|---|---|---|---|---|
| Row 1 | {A1:B1} |
{B1:C1} |
{C1:D1} |
{D1:E1} |
| Row 2 | {A2:B2} |
{B2:C2} |
{C2:D2} |
{D2:E2} |
ThanksFrom the paired ranges there are eight unique value pairs:
| Unique Paired Values | Count | Paired Cell Locations |
|---|---|---|
{A,A} |
3 | A1:B1 B1:C1 D2:E2 |
{A,B} |
2 | C1:D1 A2:B2 |
{B,C} |
1 | D1:C1 |
{B,B} |
1 | B2:C2 |
{B,A} |
1 | C2:D2 |
|   | Count Total: 8 | Paired Cells Total: 8 |
