assigning unique id's to duplicate records

I have a sheet with address data:

company1 address1 address2 address3 product1
company1 address1 address2 address3 product2
company1 address1 address2 address3 product3
company2 address1 address2 address3 product2
company2 address1 address2 address3 product4
etc.

I need to assign a unique id to each unique company so that I end up with:
001 company1 address1 address2 address3 product1
001 company1 address1 address2 address3 product2
001 company1 address1 address2 address3 product3
002 company2 address1 address2 address3 product2
002 company2 address1 address2 address3 product4

I have tried using the advance filter to hide the duplicates, but then I can't find a way to set the id on each record (including the hidden ones). My file has about 40,000 records so need to automate this somehow.

Steve

This is fine except that it is possible for the Company name to be the same when the address is different and so in this case a new ID would be required, so it needs to check on the whole record and not just the company name.

Originally Posted by stevefsm

I have a sheet with address data:

company1 address1 address2 address3 product1
company1 address1 address2 address3 product2
company1 address1 address2 address3 product3
company2 address1 address2 address3 product2
company2 address1 address2 address3 product4
etc.

I need to assign a unique id to each unique company so that I end up with:
001 company1 address1 address2 address3 product1
001 company1 address1 address2 address3 product2
001 company1 address1 address2 address3 product3
002 company2 address1 address2 address3 product2
002 company2 address1 address2 address3 product4

I have tried using the advance filter to hide the duplicates, but then I can't find a way to set the id on each record (including the hidden ones). My file has about 40,000 records so need to automate this somehow.

Steve

Are the duplicates based only on Company Name, Add1 Add2 and Add3 and not on Prod? as shown in the above example