Need to Concatenate
 

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 (to
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 never
used this before. Would anyone have any ideas? Is this a difficult task
to do in excel?

Thanks,
James.


--
hamster
------------------------------------------------------------------------
hamster's Profile: http://www.excelforum.com/member.php...o&userid=28706
View this thread: http://www.excelforum.com/showthread...hreadid=483903

Need to Concatenate
 

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)


"hamster" wrote in
message ...

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 (to
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 never
used this before. Would anyone have any ideas? Is this a difficult task
to do in excel?

Thanks,
James.


--
hamster
------------------------------------------------------------------------
hamster's Profile:
http://www.excelforum.com/member.php...o&userid=28706
View this thread: http://www.excelforum.com/showthread...hreadid=483903

Need to Concatenate
 

Wow that was fast!

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

Thanks and again much appreciated.
James.


--
hamster
------------------------------------------------------------------------
hamster's Profile: http://www.excelforum.com/member.php...o&userid=28706
View this thread: http://www.excelforum.com/showthread...hreadid=483903

Need to Concatenate
 

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.

hamster wrote:

Wow that was fast!

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

Thanks and again much appreciated.
James.

--
hamster
------------------------------------------------------------------------
hamster's Profile: http://www.excelforum.com/member.php...o&userid=28706
View this thread: http://www.excelforum.com/showthread...hreadid=483903

--

Dave Peterson

Need to Concatenate
 

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" wrote in message
...
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)


"hamster" wrote in
message ...

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 (to
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 never
used this before. Would anyone have any ideas? Is this a difficult task
to do in excel?

Thanks,
James.


--
hamster
------------------------------------------------------------------------
hamster's Profile:
http://www.excelforum.com/member.php...o&userid=28706
View this thread:
http://www.excelforum.com/showthread...hreadid=483903

Need to Concatenate
 

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

Only problem is the first line doesn't work out... but we changed it
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 Sub


--
hamster
------------------------------------------------------------------------
hamster's Profile: http://www.excelforum.com/member.php...o&userid=28706
View this thread: http://www.excelforum.com/showthread...hreadid=483903

Need to Concatenate
 

Changed, in what way?

--

HTH

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


"hamster" wrote in
message ...

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

Only problem is the first line doesn't work out... but we changed it
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 Sub


--
hamster
------------------------------------------------------------------------
hamster's Profile:
http://www.excelforum.com/member.php...o&userid=28706
View this thread: http://www.excelforum.com/showthread...hreadid=483903

Need to Concatenate
 

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! :)


--
hamster
------------------------------------------------------------------------
hamster's Profile: http://www.excelforum.com/member.php...o&userid=28706
View this thread: http://www.excelforum.com/showthread...hreadid=483903

Need to Concatenate
 

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)


"hamster" wrote in
message ...

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! :)


--
hamster
------------------------------------------------------------------------
hamster's Profile:
http://www.excelforum.com/member.php...o&userid=28706
View this thread: http://www.excelforum.com/showthread...hreadid=483903

Need to Concatenate
 

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


--
hamster
------------------------------------------------------------------------
hamster's Profile: http://www.excelforum.com/member.php...o&userid=28706
View this thread: http://www.excelforum.com/showthread...hreadid=483903