Good morning everyone,
I am interning at a large utility company and currently working in their commercial and industrial customer master file. My summer long project is to cleanse and reformat a lot of the data that has been extracted from SAP and other sources. I feel that a lot of the work is out of my realm of knowledge (which isn't much), but I actually have been able to complete a lot of the project steps myself until now.
They have asked that I assign a 5-digit unique ID to any customer that has mulitple premise locations.
This means that under the name column, the company's name appears more than once.
Here is a simplified example of what I'm working with and the formula I've come up with so far:
This formula is entered into ColumnC
=IF(MAX(COUNTIF(A3:$A$27,A3:$A$27))>1, RANDBETWEEN(9999,100000), IF(MAX(COUNTIF($B3:$B27,$B3:$B27))>1,RANDBETWEEN(9999,100000),""))
ColumnA ..... ColumnB ............. ColumnC
(Name) ... (Mailing Address) .... (Unique ID)
AAA ............. 111 .................. 49323
AAA ............. 111 .................. 49323
AAA ............. 111 .................. 49323
AAA ............. 111 .................. 49323
AAA ............. 111 .................. 49323
BBB ............. 222 ................... 98753
BBB ............. 222 ................... 98753
BB ............... 222 ................... 98753
BBB ............. 222 ................... 98753
BBB ............. 222 ................... 98753
CCC ............ 333 ................... 11365
CC .............. 333 ................... 11365
CCC ............ 333 ................... 11365
CCC ............ 333 ................... 11365
CCC ............ 333 ....................11365
As you can see, the names aren't always the same but the mailing address usually is (but not always, so I have to check them both). I have the formula check both column A and B. If a dup isn't found in Collumn A, then the formula checks Column B and generates a 5-digit random number if a dup is found.
Here is my issue:
The formula I'm using is just generating a random number for every row, and then also leaving some rows entirely blank.
If anyone could come up with a macro script in VBA to do this, that would be awesome.
Thank you in advance for your help.
I am interning at a large utility company and currently working in their commercial and industrial customer master file. My summer long project is to cleanse and reformat a lot of the data that has been extracted from SAP and other sources. I feel that a lot of the work is out of my realm of knowledge (which isn't much), but I actually have been able to complete a lot of the project steps myself until now.
They have asked that I assign a 5-digit unique ID to any customer that has mulitple premise locations.
This means that under the name column, the company's name appears more than once.
Here is a simplified example of what I'm working with and the formula I've come up with so far:
This formula is entered into ColumnC
=IF(MAX(COUNTIF(A3:$A$27,A3:$A$27))>1, RANDBETWEEN(9999,100000), IF(MAX(COUNTIF($B3:$B27,$B3:$B27))>1,RANDBETWEEN(9999,100000),""))
ColumnA ..... ColumnB ............. ColumnC
(Name) ... (Mailing Address) .... (Unique ID)
AAA ............. 111 .................. 49323
AAA ............. 111 .................. 49323
AAA ............. 111 .................. 49323
AAA ............. 111 .................. 49323
AAA ............. 111 .................. 49323
BBB ............. 222 ................... 98753
BBB ............. 222 ................... 98753
BB ............... 222 ................... 98753
BBB ............. 222 ................... 98753
BBB ............. 222 ................... 98753
CCC ............ 333 ................... 11365
CC .............. 333 ................... 11365
CCC ............ 333 ................... 11365
CCC ............ 333 ................... 11365
CCC ............ 333 ....................11365
As you can see, the names aren't always the same but the mailing address usually is (but not always, so I have to check them both). I have the formula check both column A and B. If a dup isn't found in Collumn A, then the formula checks Column B and generates a 5-digit random number if a dup is found.
Here is my issue:
The formula I'm using is just generating a random number for every row, and then also leaving some rows entirely blank.
If anyone could come up with a macro script in VBA to do this, that would be awesome.
Thank you in advance for your help.
