| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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 |
|
#7
|
|||
|
|||
|
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 |
|
#8
|
|||
|
|||
|
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 |
|
#9
|
|||
|
|||
|
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 |
|
#10
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to concatenate adjacent cells in a range without using &? | Excel Worksheet Functions | |||
| Concatenate | Excel Discussion (Misc queries) | |||
| Using Concatenate inside a vlookup | Excel Worksheet Functions | |||
| space between text strings with concatenate | Excel Discussion (Misc queries) | |||
| Concatenate cells without specifying/writing cell address individually | Excel Discussion (Misc queries) | |||
Linear Mode
