CODEWITHSUNDEEP
excelvba
Andrew Morris
Andrew Morris

Reputation: 1652

Excel concatenate by matching ID

I have a table which is an export of a database of records of information relating to user accounts on my site. My end result of this is to move the information from an old database structure, into an import function, so I need to get the format correct in my new table before I begin the import.

This is what it looks like to start with, albeit a simplified version

Opening Database

And what I'm trying to do is check any data that matches in the UserID column, and put those values into the Value column (or a new column if thats easier to achieve)

So this is how I would want it to look

Concatenated values

Assuming this isn't something that could be done through Excels formatting, so I assume it would need to be a custom function, but I don't know enough VB to write it myself.

*Before anyone suggests, I'm stuck with this version of the export, I can't do a different database export as the client no longer has the db

Upvotes: 1

Views: 417

Answers (1)

ttaaoossuu
ttaaoossuu

Reputation: 7884

Try this:

Sub Parser()
Dim I As Integer
Dim J As Integer
Dim IMax As Integer
Dim CurrentValue As Integer
Dim CommaSet As Boolean

IMax = Range("A2").End(xlDown).Row

For I = 2 To IMax
    CurrentValue = Cells(I, 1).Value
    Call Cells(I, 3).Clear
    CommaSet = False
    For J = 2 To IMax
        If Cells(J, 2).Value = CurrentValue Then
            If CommaSet Then
                Cells(I, 3).Value = Cells(I, 3).Value + ", "
            Else
                CommaSet = True
            End If
            Cells(I, 3).Value = Cells(I, 3).Value + Chr(63 + J)
        End If
    Next
Next

End Sub

Upvotes: 2

Related Questions