I have a De-Dup'ing project that I am working on, and I feel like I am missing something really simple here.
I have a worksheet that has 4 columns in it: ID, CUSTODIAN, DUPID, ALLCUSTODIANS
ID is the unique index.
CUSTODIAN is the name of the custodian for that record
DUPID is an ID used for grouping
ALLCUSTODIANS is my target rield
What I am trying to do is run through the DUPID field and for all of the records that have the same DUPID, I want to populate the ALLCUSTODIANS field with the names of the CUSTODIANS separated by a ;
I attached the excel spreadsheet I am working off of. I went ahead and manually populated the ALLCUSTODIANs field just to show what I want the results to be
There are just a handful of records in the spreadsheet (normally I would have anywhere from 5000 - 50000 records with many CUSTODIANS and DUPIDs, some custodians and their DupIDs listed multiple times)
I have played around with pulling the data into Access, and am just not gaining any ground.
I am a VB Programmer, but frankly, I am not sure where to start with this project.
I would prefer to end up with a Macro or VBScript, but am not picky at this point.
Thanks in advance for any direction or guidance you can lend me.
ID Custodian DupID AllCustodians
13456 Smith, John 12456
13480 Black, William 12456
13524 Black, William 0
13567 Night, Amy 15789
13589 Night, Amy 15789
13594 Night, Amy 0
13609 Jones, Jim 15789
13610 Jones, Jim 12456
The ALLCUSTODIANS should end up like below:
ALLCUSTODIANS
Smith, John; Black, William; Jones, Jim
Black, William; Smith, John; Jones, Jim
Black, William
Night, Amy; Jones, Jim
Night, Amy; Jones, Jim
Night, Amy
Jones, Jim; Night, Amy
Jones, Jim; Smith, John; Black, William
Bookmarks