• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Duplicate Occurence Number Count

Hi all,
My data in column A has duplicates. I am trying to write a formula in column B to identify the occurence number count of each duplicate. Desired results as follows;
A B
Red 1
Yellow 1
Green 1
Yellow 2
Blue 1
Red 2
Yellow 3
I thought this was going to be simple however I have been searching internet for over an hour now and all I can find are formulas which give me a total count of duplicates. Thanks in advance.
Matt
 

Hui

Excel Ninja
Staff member
I assume your data is in A2:Ax
In B2: =COUNTIF(A$2:A2,A2)
Copy down
 

Sunil Yadav

New Member
I need to count the number of invoices which are having bill type as F2 & F8.
Please help me with the formulae to do this.


Bill Type Invoice
F2 90309330
F8 90309350
F2 90309351
F8 90309352
F2 90309354
F2 90309357
F2 90309358
F2 90309359
F2 90309360
RE 90309362
F2 90309363
F2 90309363
F2 90309363
F2 90309364
F2 90309365
F2 90309366
F8 90311860
F8 90311861
F8 90311862
F2 90311863
F2 90311866
 

Debraj

Excel Ninja
Hi Sunil,

here another approach.. using Ctrl + Shift + Enter

=SUM(IF(A2:A22={"F2","F8"},1/COUNTIFS(B2:B22,B2:B22,A2:A22,{"F2","F8"})))
  • + Point..
    • If Invoice Number are in AlphaNumeric.. Still it will Work..
  • - Point..
    • In case of blank INVOICE #, it will fail..
unlike @Somendra Misra 's formula.. :)
 

Somendra Misra

Excel Ninja
Another alternative to overcome cons raised by @Debraj Dada....

=SUMPRODUCT(IF(FREQUENCY(IFERROR(MATCH(IF(($A$2:$A$5000="F2")+($A$2:$A$5000="F8"),$B$2:$B$5000),$B$2:$B$5000,0),"e"),IFERROR(MATCH(IF(($A$2:$A$5000="F2")+($A$2:$A$5000="F8"),$B$2:$B$5000),$B$2:$B$5000,0),"e")),1,0))

  • +ve Points.. Include both alphanumeric + blanks cases
  • Faster really fast
  • -ve Points...Longer formula
Regards,
 

Sunil Yadav

New Member
Hi Sunil,

here another approach.. using Ctrl + Shift + Enter

=SUM(IF(A2:A22={"F2","F8"},1/COUNTIFS(B2:B22,B2:B22,A2:A22,{"F2","F8"})))
  • + Point..
    • If Invoice Number are in AlphaNumeric.. Still it will Work..
  • - Point..
    • In case of blank INVOICE #, it will fail..
unlike @Somendra Misra 's formula.. :)

Thanks a lot Debraj,

The formula has worked for me. :)

Cheers.
 

Sunil Yadav

New Member
Hi Sunil,

See the attached file.

Solutions with two method.
1. Formula based (Yellow Cells)
2. Pivot Table.

Regards,
Hi Somendra,

Thanks for your post.

I wanted to do it by formula method, there were some duplicate value in coloumn invoice which should not be counted.

Kindly check the attachment and suggest formula to do it.
 

Attachments

Somendra Misra

Excel Ninja
Try Below array formula in F8 and copy down:

=SUMPRODUCT(IF(FREQUENCY(IF($A$2:$A$21=E8,IF(($B$2:$B$21="F2")+($B$2:$B$21="F8"),$C$2:$C$21)),$C$2:$C$21),1,0))

Enter with Ctrl+Shift+Enter.

Regards,
 

Sunil Yadav

New Member
Try Below array formula in F8 and copy down:

=SUMPRODUCT(IF(FREQUENCY(IF($A$2:$A$21=E8,IF(($B$2:$B$21="F2")+($B$2:$B$21="F8"),$C$2:$C$21)),$C$2:$C$21),1,0))

Enter with Ctrl+Shift+Enter.

Regards,

Thank you Somendra.

I have some more queries to ask..... please help me out with one as per the attachment
 

Attachments

Haseeb A

Active Member
Hello Sunil,

May be this...

="Week "&INT((A2-DATE(YEAR(A2),MONTH(A2)-(DAY(A2)<26),25)-1)/7)+1

Or with EOMONTH


="Week "&INT((A2-(EOMONTH(A2,-1-(DAY(A2)<26))+25)-1)/7)+1
 
Top