ExcelBanter - Writing a query

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Writing a query (https://www.excelbanter.com/excel-discussion-misc-queries/254735-writing-query.html)

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

Perhaps this formula coppied down...

=IF(COUNTIF(A:A, A1)=1, "", COUNTIF(A:A, A1))
--
HTH...

Jim Thomlinson

"Pierre" wrote:

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

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]

On Thu, 28 Jan 2010 13:45:01 -0800, Pierre
wrote:

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