# Same sl no for exact match row data.

Sir,

#### Attachments

• 11 KB Views: 8

#### pecoflyer

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

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

• 11 KB Views: 4

#### pecoflyer

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

• 13.4 KB Views: 4

#### sambit

##### Member
Excel Wizard Sir,
Thank you very much.

#### sambit

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

• 13.1 KB Views: 2

#### pecoflyer

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

#### sambit

##### Member
it should be NA due to different vendor code.

correct file attached for your reference.

#### Attachments

• 13.3 KB Views: 2

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

• 14.6 KB Views: 4

#### p45cal

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

#### Attachments

• 25.1 KB Views: 5

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