Reverse Concatenation

[TBRoberts]

The question is of reverse concatenation in Excel VBA.


I currently have this:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Joe, Jon[/TD]
[/TR]
[TR]
[TD]Joe, Pam, Carl[/TD]
[/TR]
[TR]
[TD]Joe, Jon, Jeremy[/TD]
[/TR]
</tbody>[/TABLE]



And need to reverse concatenate so I can then have each individual name in its own row and cell as follows:

[TABLE="class: cms_table, width: 150"]
<tbody>[TR]
[TD]Joe[/TD]
[/TR]
[TR]
[TD]Jon[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[/TR]
[TR]
[TD]Pam[/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[/TR]
[TR]
[TD]Jon[/TD]
[/TR]
[TR]
[TD]Jeremy[/TD]
[/TR]
</tbody>[/TABLE]



I can then remove the duplicate names and re-concatenate to have the following:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Joe, Jon, Pam, Carl, Jeremy[/TD]
[/TR]
</tbody>[/TABLE]

Please note that the data is not static and so the number of individuals/rows will change.

I won't need any button or msgbox for this code but comments before each action would be great to help me understand what is going on within your coding.

Thank you!

 

[Rick Rothstein]

The Case Numbers and Account Numbers were easy enough to extract and concatenate as they have a designated cell for each value. However, the Client names can be grouped together at times, and changes for each report ran. So i would need your function to basically loop through from top to bottom as the number of rows/clients will change for each report.

Here is my function modified to pull the client names from Column C starting at Row 2 (it will automatically find the last client name). Note that this revised function takes no arguments.

Function Uniques() As String
  Dim Cell As Range, V As Variant, Data() As String
  Const Delimiter As String = ", "
  With CreateObject("Scripting.Dictionary")
    For Each Cell In Range("C2", Cells(Rows.Count, "C").End(xlUp))
      For Each V In Split(Cell.Value, Delimiter)
        .Item(V) = 1
      Next
    Next
    Uniques = Join(.Keys, Delimiter)
  End With
End Function

 

[TBRoberts]

Hey Rick, thanks for all of your help with this. I decided to go with this:

Sub Extract()

Dim rng As Range


Range("C4").Activate


Do Until ActiveCell.Value = ""


Z = Split(ActiveCell, ",")


Set rng = ActiveCell


rng.Select


Set rng = rng.Resize(1, 1 + UBound(Z))


rng.Select


rng = Z


ActiveCell.Offset(1, 0).Activate


Loop

End Sub

With names in Column C as:

Jon, Jane, Bob
Jon, Bob
Jane,Jon
Jane, Bob
(names and volume of names will change for each export)

I am wondering if you could chime in on my new dilemma

This code spits the names out into the next available columns and overwrites the data in it's place.

How would I go about adding new columns for each name that was extracted?

If possible, i would rather do this without a user function as calling out the functions in the code is a bit over my head and I am really just going through the basics so i can understand what each line of code is doing.

There could be any number of names concatenated in column C. As always, thanks for the help!

 

[TBRoberts]

Got it. Now I just need to delete duplicates and put back into a string!! (sloppy code posted below). I know there is a better and more efficient way to do this but I think I am happy for now. Thanks again! :D

Sub Extract()

Dim rng As Range
Dim separator As String
Dim Names As String
Dim i As Integer
Dim iCountSep As Integer


Range("C4").Select
Set rng = Range(Selection, Selection.End(XLDOWN))


rng.Select


separator = ","


Names = rng.Cells(1, 1)


For i = 1 To Len(Names)


If Mid(Names, i, 1) = separator Then iCountSep = iCountSep + 1


Next i


For i = 1 To iCountSep '2commas
    rng.Offset(0, i).EntireColumn.Insert
Next i

Range("C4").Activate


Do Until ActiveCell.Value = ""


Z = Split(ActiveCell, ",")


Set rng = ActiveCell


rng.Select


Set rng = rng.Resize(1, 1 + UBound(Z))


rng.Select


rng = Z


ActiveCell.Offset(1, 0).Activate


Loop


End Sub

 

[TBRoberts]

Now that I understand how to call your function.. It is pure gold. Much better than my previous code thank you sir.