Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

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

example screenshot

share|improve this answer

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

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .