Writing a query

P

Pierre

Hi all.

I'm having a 'mare trying to write a query that will mark duplicates as
duplicates and triplicates as triplicates in a sorted column - see below

30460508
30460508
30460509
30460509
30460512
30460512
30460512
30460514
30460514
30460514
30460516
30460516
30460516
30460520
30460520
30460520
30460522
30460522
30460522
30460524
30460524


Say this is Cl A - what I want as output in Col B is 2 against the
duplicates and 3 against the triplicates.......

Any help gratefully recied.

Cheers
 
J

Jim Thomlinson

Perhaps this formula coppied down...

=IF(COUNTIF(A:A, A1)=1, "", COUNTIF(A:A, A1))
 
C

Chip Pearson

Try the following formula in cell B1, copied down.

=IF(COUNTIF($A$1:$A$10,A1)=2,2,IF(COUNTIF($A$1:$A$10,A1)=3,3,""))

It will return 2 or 3 if a value in A1 occurs two or three times in
A1:A10. Otherwise, it returns an empty string. You can adapt it to
display whatever counts you need.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
 
Top