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

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

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

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

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

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