Need to Concatenate

H

hamster

Hi All,

I go an excel with data in this format (3 columns):

LAST_NAME FIRST_NAME Selections
Adcock Julia Do
Adcock Julia Ra
Adcock Julia Me
Aiple Matthew Do
Aiple Matthew Ra

I want to concatenate the third column like this for each person (t
the existing sheet above)

LAST_NAME FIRST_NAME Selections
Adcock Julia Do, Ra, Me
Aiple Matthew Do, Ra


I know that this is done by conditional formating - but I have neve
used this before. Would anyone have any ideas? Is this a difficult tas
to do in excel?

Thanks,
James
 
B

Bob Phillips

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim sTemp
Dim rng As Range
Dim iStart As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
sTemp = Range("A2").Value & Range("B2").Value
iStart = 2
For i = 3 To iLastRow
If Cells(i, "A").Value & Cells(i, "B").Value = sTemp Then
Cells(iStart, "C").Value = Cells(iStart, "C").Value & _
", " & Cells(i, "C").Value
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
Else
sTemp = Cells(i, "A").Value & Cells(i, "B").Value
iStart = i
End If
Next i

If Not rng Is Nothing Then
rng.Delete
Set rng = Nothing
End If


End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
H

hamster

Wow that was fast!

How I can I this Format-> Conditional Formatting in Excel?

Thanks and again much appreciated.
James.
 
D

Dave Peterson

Format|conditional formatting won't help you in this case.

If you look at the output, you'll see the Bob actually changed your data by
combining it nicely.
 
B

Bob Phillips

Sorry, I should have mentioned that you can't do that with CF, and I gave
you some code that does it instead.

--

HTH

RP
(remove nothere from the email address if mailing direct)


Bob Phillips said:
Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim sTemp
Dim rng As Range
Dim iStart As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
sTemp = Range("A2").Value & Range("B2").Value
iStart = 2
For i = 3 To iLastRow
If Cells(i, "A").Value & Cells(i, "B").Value = sTemp Then
Cells(iStart, "C").Value = Cells(iStart, "C").Value & _
", " & Cells(i, "C").Value
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
Else
sTemp = Cells(i, "A").Value & Cells(i, "B").Value
iStart = i
End If
Next i

If Not rng Is Nothing Then
rng.Delete
Set rng = Nothing
End If


End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...
 
H

hamster

Thanks Phil! This really helps since I was going to have code this. Thi
will save a lot of time for similar queries....

Only problem is the first line doesn't work out... but we changed i
slightly to this... what do you think?

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim sTemp
Dim rng As Range
Dim iStart As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
sTemp = Range("A1").Value & Range("B1").Value
iStart = 1
For i = 2 To iLastRow
If Cells(i, "A").Value & Cells(i, "B").Value = sTemp Then
Cells(iStart, "C").Value = Cells(iStart, "C").Value & _
", " & Cells(i, "C").Value
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
Else
sTemp = Cells(i, "A").Value & Cells(i, "B").Value
iStart = i
End If
Next i

If Not rng Is Nothing Then
rng.Delete
Set rng = Nothing
End If


End Su
 
B

Bob Phillips

Changed, in what way?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
H

hamster

Sorry Philip,

To make myself more clear I changed the following lines:

sTemp = Range("A1").Value & Range("B1").Value -- was A2
iStart = 1
-- Was 2
For i = 2 To iLastRow
-- Was 3

The number you start with here determine the row it starts the
formula?
ie, the values here assume the data starts on the first line?

Either way, this has been a massive help and thank you! :)
 
B

Bob Phillips

I did that to avoid the headings row being included in the re-organisation.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
H

hamster

Gotcha - Thanks. I hope to get some free time to study some of these
tricks in more details. That's an eye opener! Thanks!
 
Top