Count value pairs in Excel

Question

I have a set of data in excel where each row has (amongst other things) two values and I want is to generate a sorted list of all pairings.

Example data:

Val1  Val2
------------
A     B
B     A
C     A
B     C
A     B
C     A
etc..

Example output:

Val1  Val2  Count
------------------
A     B     2
B     A     1
B     C     1
C     A     2

Is there a way to do this without writing VBA code.

Edit: I should add that my dataset is growing and I would prefer a solution that would update automatically as new data is entered.

Answer 1

The simpliest way is to concatenate the two columns together, perform do a COUNTIF() on that column and finally remove all duplicate rows.

Answer 2

Assume: Row 1 is header row, and row 2 is the first row of data (A B).

Steps

1. Enter in D2: =A2 & "|||" & B2 and copy down to D3:D999
   (Use some rare string like "|||")

2. Enter in C2: =COUNTIF(D:D, D2) and copy down to C3:C999

3. Copy column C:C, then Paste Special as value to column C:C (Alt-E > S > V > Enter)

4. Select column A:D, then sort by column D

5. Enter in E2: =IF(OR(ROW(D2)=2, C2=1, AND(C2>1,D2<>D1)), "Keep", "Delete") and copy down to E3:E999

6. Turn on AutoFilter (Alt-D > F > F)

7. Select "Delete" in column E drop-down list

8. Select row 2:999, then right cilck > Delete
   (Some #REF! may be shown in column E, which is normal)

9. Turn off AutoFilter (Alt-D > F > F)

10. Delete dummy column D:E