• 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

M

matt-gilbert

Member
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
 
I assume your data is in A2:Ax
In B2: =COUNTIF(A$2:A2,A2)
Copy down
 
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
 
Somendra Misra said:
Hi Sunil,

See below for the formulas.

View attachment 7373

Regards,
Click to expand...


Hi Somendra,

Thanks for the post, i think i couldn't put my query right. I have to count total no of invoices which are of bill type F2 & F8. there are few duplicate entries in coloumn "Invoice" which need not be counted.

The workbook is attached, kindly help.
 

Attachments

  • Query.xlsx
    8.6 KB · Views: 10
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.. :)
 
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,
 
Debraj said:
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.. :)
Click to expand...


Thanks a lot Debraj,

The formula has worked for me. :)

Cheers.
 
Hi Sunil,

See the attached file.

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

Regards,
 

Attachments

  • Query (3).xlsx
    40.1 KB · Views: 6
Somendra Misra said:
Hi Sunil,

See the attached file.

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

Regards,
Click to expand...

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

  • Query (3) (1).xlsx
    39.5 KB · Views: 3
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,
 
Somendra Misra said:
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,
Click to expand...


Thank you Somendra.

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

Attachments

  • Query.xlsx
    15.1 KB · Views: 1
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
 
Hi Everbody,

Please help me with the attached query, i'll be very thankful for the kind support.
 

Attachments

  • Query.xlsx
    32.7 KB · Views: 3
You must log in or register to reply here.
Back
Top