I would use a couple of helper columns:

First, I'd add headers in A1 and B1
In C1, put a header of "Combo"
In D1, put a header of "Count"

In C2, put this formula:
=A2&"--"&B2

In D2, put this formula:
=IF(COUNTIF($C$2:C2,C2)1,"Delete me",COUNTIF($C$2:$C$999,C2))

Then select C2:D2 and drag down as far as your data goes.

Then convert columns C:D to values
Select column C:D
edit|copy
followed by
edit|paste special|values

Select column D
Data|Filter|Autofilter
Filter to only show the "delete me" rows
Select those visible cells
edit|delete row
Remove the autofilter (data|filter|autofilter again)

Delete column C




"saman110 via OfficeKB.com" wrote:

I have numbers in two columns like A and B. How can I get the number of
duplicates in column C and delete the rest.

I have:

A B
256 256
256 256
256 256
345 345
789 789
631 879
436 789
523 282
523 282

I want:

A B C
256 256 3
345 345 1
789 789 1
631 879 1
436 789 1
523 282 2

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200802/1

--

Dave Peterson