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

pecoflyer

Active Member
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?
 

sambit

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

Excel Wizard

Active Member
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

Excel Wizard

Active Member
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

sambit

Member
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.
 
Top