Same sl no for exact match row data.

[sambit]

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

 

[pecoflyer]

Please explain in words what you are trying to achieve without having to open your file. Thx

 

[sambit]

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

 

[pecoflyer]

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]

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]

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

 

[sambit]

Excel Wizard Sir,
Thank you very much.

 

[sambit]

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.

 

[pecoflyer]

Please answer post #4 or at least have the courtesy to acknowledge it

 

[sambit]

Yes, Your observation is correct.
it should be NA due to different vendor code.

correct file attached for your reference.

 

[Excel Wizard]

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

 

[p45cal]

See column X
Right-click the table on the right and choose Refresh after updating the table on the left.

 

[sambit]

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.