Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.