Thread: Populate cell with values from multiple other cells based on criteria

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

Looking for that attachment... zip it up if you need to.

I added a new function to your workbook called CONCATIF(). It functions like a SUMIF() with two extra parameters. Have a look.

You didn't add the CONCATIF() User Defined Function into your main workbook.

I added it in and tried it, it's impossible with 21k records, the workbook dragged to a standstill with that many array formulas all with 21k calculation going in them at the same time. Best stick with Leith's "on demand" option.

Sorry about the OnDemand thing, I was mixing up your thread with an almost identical one elsewhere.

Like so:

Option Explicit

Sub AllCustodians()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False

Range("D2:D" & LR).ClearContents
    
    Columns("A:D").Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    
    With Range("D2:D" & LR)
        .FormulaR1C1 = "=IF(RC[-1]=0,RC[-2],IF(RC[-1]=R[1]C[-1],R[1]C&""; ""&RC[-2],RC[-2]))"
        .Value = .Value
    End With
    
    Columns("A:D").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
        
'    Columns.AutoFit

Application.ScreenUpdating = True
End Sub

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.