Concatenate Strings if Case Number is Same

[ChuckRobert]

In column D I have a case statement that generates numbers if the data in column E is related. I would like to use code to reference the numbers in column D and if the same, concatenate strings from column E into column H. For example:

Col D Col E
23 abc John
24 cca Sam
24 cca Sally
25 dba Mark
26 erb Jim
26 erb Jill

Ideally, the data would be posted in column H as:

abc John
cca Sam // cca Sally
dba Mark
erb Jim // erb Jill

I could hard-code this portion, but have been able to get some outstanding suggestions, from this forum.

 

[John Davis]

In column D I have a case statement that generates numbers if the data in column E is related. I would like to use code to reference the numbers in column D and if the same, concatenate strings from column E into column H. For example:

Col D Col E
23 abc John
24 cca Sam
24 cca Sally
25 dba Mark
26 erb Jim
26 erb Jill

Ideally, the data would be posted in column H as:

abc John
cca Sam // cca Sally
dba Mark
erb Jim // erb Jill

I could hard-code this portion, but have been able to get some outstanding suggestions, from this forum.

Based on your sample could there be more than two consecutive numbers in Column D?

 

[ChuckRobert]

John,
The numbers range from 1 to about 45. There may be a maximum of two numbers that would be the same, and those are the ones I want to connect.

In thinking about this, I remebered some code offerred by Mirabeau on this forum http://www.mrexcel.com/forum/showthread.php?t=533928
that I think I can adapt to complete this...

Sub reorgteams()
Dim e As Range, d As Object, f, x, k As Integer
Set d = CreateObject("scripting.dictionary")
With Range("A:A")
For Each e In .Resize(.Cells(Rows.Count, 1).End(3).Row)
If Not d.exists(e.Value) Then
d(e.Value) = e.Value & "," & e.Offset(, 1)
Else
d(e.Value) = d(e.Value) & "," & e.Offset(, 1)
End If
Next e
End With
For Each f In d
k = k + 1
x = Split(d(f), ",")
Cells(k, 3).Resize(, UBound(x) + 1) = x
Next f
End Sub

This code displayed only 1 instance of each name (in this case it will be numbers), and then pasted each corresponding name in the columns to the right, 1 item per cell. I could then hard-code the concatenation.

However, if you have another suggestion, I'll be glad to hear it. Thanks again!

Chuck

 

[John Davis]

John,
The numbers range from 1 to about 45. There may be a maximum of two numbers that would be the same, and those are the ones I want to connect.

In thinking about this, I remebered some code offerred by Mirabeau on this forum http://www.mrexcel.com/forum/showthread.php?t=533928
that I think I can adapt to complete this...

Sub reorgteams()
Dim e As Range, d As Object, f, x, k As Integer
Set d = CreateObject("scripting.dictionary")
With Range("A:A")
For Each e In .Resize(.Cells(Rows.Count, 1).End(3).Row)
If Not d.exists(e.Value) Then
d(e.Value) = e.Value & "," & e.Offset(, 1)
Else
d(e.Value) = d(e.Value) & "," & e.Offset(, 1)
End If
Next e
End With
For Each f In d
k = k + 1
x = Split(d(f), ",")
Cells(k, 3).Resize(, UBound(x) + 1) = x
Next f
End Sub

This code displayed only 1 instance of each name (in this case it will be numbers), and then pasted each corresponding name in the columns to the right, 1 item per cell. I could then hard-code the concatenation.

However, if you have another suggestion, I'll be glad to hear it. Thanks again!

Chuck

Chuck:

I know you wanted code, and I'm not sure if this would be of any help but this formula beginning in H2 and copied down seems to work and may be of use.

=IF(AND(D2=D3,D2<>D1),E2&" // "&E3,IF(AND(D2=D1,D2<>D3),"",E2))

 

[ChuckRobert]

John,
Thanks! I hope to work this project later tonight.

Chuck

 

[ChuckRobert]

John,
That will work great. Thanks again!

Chuck

 

[John Davis]

John,
That will work great. Thanks again!

Chuck

You're welcome. Glad it helped.