• 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.

Same sl no for exact match row data.

sambit

Member
Sir,
Please resolve the issue. Example file attached for your reference.
 

Attachments

  • example1.xlsx
    11 KB · Views: 8
Unique Serial Number for same data for every row line item.
Criteria =
Vendor Name, Tax Invoice, Date & Amount is equal for every line item.

Pl Note : Amount may be positive or negative.

Revise example file is attached for your kind information
 

Attachments

  • example_revise.xlsx
    11 KB · Views: 4
Your examples are not clear
For instance KPG & CO appears two times with same date / amount/ tax invoice but the expected result is 3 ?
ABC & CO is to be counted 5 times for tax invoice 456 but only appears twice?
 
3 & 5 is the unique serial No.
I have assign own sl no for understanding purpose.
My question is if same data is continuing then unique sl No is assigned for every row line item.
 
Maybe try

=IF(COUNTIFS(D$3:D3,D3,F$3:F3,F3,G$3:G3,G3)>1,INDEX(L$3:L3,MATCH(1,(D$3:D3=D3)*(F$3:F3=F3)*(G$3:G3=G3),)),IF(MAX(L$2:L2)>4,"NA",COUNT(1/FREQUENCY(MATCH(D$3:D3&F$3:F3&G$3:G3,D$3:D3&F$3:F3&G$3:G3&{""},),ROW(D$3:D3)-ROW(D$2)))))
 

Attachments

  • example_revise.xlsx
    13.4 KB · Views: 4
Excel Wizard Sir,
The said formula is not working if the amount is differ from other line item.
Example file is attached for your kind information.
 

Attachments

  • example_revise1.xlsx
    13.1 KB · Views: 2
Yes, Your observation is correct.
it should be NA due to different vendor code.

correct file attached for your reference.
 

Attachments

  • example_revise2.xlsx
    13.3 KB · Views: 2
Try at K3

=IF(SUM(COUNTIFS(B$3:B3,B3,C$3:C3,C3,E$3:E3,E3,F$3:F3,F3,J$3:J3,J3*{1,-1}))>1,INDEX(K$3:K3,MATCH(1,(C$3:C3=C3)*(E$3:E3=E3)*(F$3:F3=F3),)),IF((MAX(K$2:K2)>4)+(COUNTIFS($B$3:$B$20,B3,$C$3:$C$20,C3)<2),"NA",COUNT(1/FREQUENCY(MATCH(C$3:C3&E$3:E3&F$3:F3,C$3:C3&E$3:E3&F$3:F3&{""},),ROW(C$3:C3)-ROW(C$2)))))
 

Attachments

  • example_revise2.xlsx
    14.6 KB · Views: 4
See column X
Right-click the table on the right and choose Refresh after updating the table on the left.
 

Attachments

  • Chandoo46115example_revise2.xlsx
    25.1 KB · Views: 5
Try at K3

=IF(SUM(COUNTIFS(B$3:B3,B3,C$3:C3,C3,E$3:E3,E3,F$3:F3,F3,J$3:J3,J3*{1,-1}))>1,INDEX(K$3:K3,MATCH(1,(C$3:C3=C3)*(E$3:E3=E3)*(F$3:F3=F3),)),IF((MAX(K$2:K2)>4)+(COUNTIFS($B$3:$B$20,B3,$C$3:$C$20,C3)<2),"NA",COUNT(1/FREQUENCY(MATCH(C$3:C3&E$3:E3&F$3:F3,C$3:C3&E$3:E3&F$3:F3&{""},),ROW(C$3:C3)-ROW(C$2)))))
Excel Wizard Sir,
The above formula working fine.
Lot of Thanks.
 
Back
Top