Why would you not want the "delete me"?

It designates which cells are extraneous so's you can follow the rest of Dave's
instructions.

If left blank, how would you find the cells later?

But if you want that, just get rid of delete me from between the double quotes.

=IF(COUNTIF($C$2:C2,C2)1,"",COUNTIF($C$2:$C$999,C 2))


Gord Dibben MS Excel MVP


On Mon, 11 Feb 2008 20:14:42 GMT, "saman110 via OfficeKB.com" <u35670@uwe
wrote:

How can I have your formula without this part "Delete me" ?


Dave Peterson wrote:
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

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.
[quoted text clipped - 25 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200802/1