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

Unique Serial No

sambit

Member
Sir,
if Invoice No, Invoice Date, Customer Name & Amount is equal with other line item, then assign unique sl no.

The sample data given below for your reference.

Requirement
Invoice NoInvoice DateDoc NoAcct NoCustomer Name Amount Unique Sl No
RE-31895
18-03-2021​
33564
215100000005​
ABC 239.481
27159
24-03-2021​
33566
215100000006​
TRK 1,696.71
31422
26-03-2021​
33313
215100000001​
BAS 25.02
RE-31895
18-03-2021​
33564
215100000005​
ABC 239.481
13349665
30-03-2021​
34283
215100000023​
PTC 520.60
BST-3351
30-03-2021​
33787
215100000017​
STP 1,754.83
53360536
30-03-2021​
33385
215100000002​
SOP 287.792
73360613
30-03-2021​
34523
215100000024​
TAR 1,348.73
TO/33614
30-03-2021​
33750
215100000015​
TIA 73.833
883363691
30-03-2021​
33414
215100000003​
TMT 60.35
TO/33614
30-03-2021​
33750
215100000015​
TIA 73.833
13378834
01-04-2021​
33848
215100000019​
MTS 184.92
53360536
30-03-2021​
33385
215100000002​
SOP 287.792
RE-31895
18-03-2021​
33564
215100000005​
ABC 1,239.48
 

Attachments

Excel Wizard

Active Member
Please try at H3
=IF(COUNTIFS(B$3:B$99,B3,C$3:C$99,C3,F$3:F$99,F3,G$3:G$99,G3)>1,IF(COUNTIFS(B$3:B3,B3,C$3:C3,C3,F$3:F3,F3,G$3:G3,G3)=1,MAX(H$2:H2)+1,LOOKUP(2,1/(B$2:B2=B3)/(C$2:C2=C3)/(F$2:F2=F3)/(G$2:G2=G3),H$2:H2)),"")
 

Attachments

sambit

Member
Please try at H3
=IF(COUNTIFS(B$3:B$99,B3,C$3:C$99,C3,F$3:F$99,F3,G$3:G$99,G3)>1,IF(COUNTIFS(B$3:B3,B3,C$3:C3,C3,F$3:F3,F3,G$3:G3,G3)=1,MAX(H$2:H2)+1,LOOKUP(2,1/(B$2:B2=B3)/(C$2:C2=C3)/(F$2:F2=F3)/(G$2:G2=G3),H$2:H2)),"")
[/QUOT
Excellent Sir.
Thank you very much.
 
Top