Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging multiple cells into a single cell
Dear all,
please help me, i have a two column data. I need to merge data from column b with the same data in column a into a single cell separated by ";". the sheet looks as follows: ---A--- ---B--- Job ID Name 335119 Malonzo, Elaine 335119 Monsalve, Edmund 335119 Pilapil, Flofer 335119 Pilapil, Flofer 335119 Santiago, Minerva 335212 Calub, Charina 335212 Calub, Charina 335212 Calub, Charina 335212 Requita, Darius 335212 Santiago, Minerva 335253 Bonifacio, DaisyLou 335253 Bonifacio, DaisyLou 335253 Delos Santos, Veronica 335253 Delos Santos, Veronica 335253 Llamas-Ong, Christine 335253 Llamas-Ong, Christine 335253 Reyes, Wilson I need it to look like this.... ---A--- ---B------------------------------------ Job ID Name 335119 Malonzo, Elaine; Monsalve, Edmund; Pilapil, Flofer; Santiago, Minerva 335212 Calub, Charina; Requita, Darius; Santiago, Minerva 335253 Bonifacio, DaisyLou; Delos Santos, Veronica; Llamas-Ong, Christine Hoping to have an answer. best regards, akaDong |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging multiple cells into a single cell
=A1 & ";" & B1
"akaDong" skrev i melding ... Dear all, please help me, i have a two column data. I need to merge data from column b with the same data in column a into a single cell separated by ";". the sheet looks as follows: ---A--- ---B--- Job ID Name 335119 Malonzo, Elaine 335119 Monsalve, Edmund 335119 Pilapil, Flofer 335119 Pilapil, Flofer 335119 Santiago, Minerva 335212 Calub, Charina 335212 Calub, Charina 335212 Calub, Charina 335212 Requita, Darius 335212 Santiago, Minerva 335253 Bonifacio, DaisyLou 335253 Bonifacio, DaisyLou 335253 Delos Santos, Veronica 335253 Delos Santos, Veronica 335253 Llamas-Ong, Christine 335253 Llamas-Ong, Christine 335253 Reyes, Wilson I need it to look like this.... ---A--- ---B------------------------------------ Job ID Name 335119 Malonzo, Elaine; Monsalve, Edmund; Pilapil, Flofer; Santiago, Minerva 335212 Calub, Charina; Requita, Darius; Santiago, Minerva 335253 Bonifacio, DaisyLou; Delos Santos, Veronica; Llamas-Ong, Christine Hoping to have an answer. best regards, akaDong |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging multiple cells into a single cell
I have a user-written worksheet function that does that
very thing. In a cell I put, for example, =CONCAT(B2:E2), and it joins the text values of those cells together. By default it puts a space between each one, but I can call =CONCAT(B2:E2,"; ") instead and it'll put the second string (semicolon-and-space) between each value. This CAN be done with the MS-provided worksheet functions, but writing your own is easier in my opinion. The only problem is that I wrote that function at work and I don't seem to have a copy of it here; I'll have to go get it tomorrow. But here's a minor puzzle: You asked the question in the Programming forum, so one might assume you know how to write in VBA and are just looking for a few hints on how to write this function for yourself. Yet it seems to me if you know VBA at all, this function would be pretty simple to write. I don't object to helping you, but did you really mean to ask this question in the Excel Worksheet Functions forum? --- "akaDong" wrote: i have a two column data. I need to merge data from column b with the same data in column a into a single cell separated by ";". the sheet looks as follows: ---A--- ---B--- Job ID Name 335119 Malonzo, Elaine 335119 Monsalve, Edmund 335119 Pilapil, Flofer 335119 Pilapil, Flofer 335119 Santiago, Minerva 335212 Calub, Charina 335212 Calub, Charina 335212 Calub, Charina 335212 Requita, Darius 335212 Santiago, Minerva 335253 Bonifacio, DaisyLou 335253 Bonifacio, DaisyLou 335253 Delos Santos, Veronica 335253 Delos Santos, Veronica 335253 Llamas-Ong, Christine 335253 Llamas-Ong, Christine 335253 Reyes, Wilson I need it to look like this.... ---A--- ---B------------------------------------ Job ID Name 335119 Malonzo, Elaine; Monsalve, Edmund; Pilapil, Flofer; Santiago, Minerva 335212 Calub, Charina; Requita, Darius; Santiago, Minerva 335253 Bonifacio, DaisyLou; Delos Santos, Veronica; Llamas-Ong, Christine |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging multiple cells into a single cell
On Sun, 22 Feb 2009 14:49:01 -0800, akaDong
wrote: Dear all, please help me, i have a two column data. I need to merge data from column b with the same data in column a into a single cell separated by ";". the sheet looks as follows: ---A--- ---B--- Job ID Name 335119 Malonzo, Elaine 335119 Monsalve, Edmund 335119 Pilapil, Flofer 335119 Pilapil, Flofer 335119 Santiago, Minerva 335212 Calub, Charina 335212 Calub, Charina 335212 Calub, Charina 335212 Requita, Darius 335212 Santiago, Minerva 335253 Bonifacio, DaisyLou 335253 Bonifacio, DaisyLou 335253 Delos Santos, Veronica 335253 Delos Santos, Veronica 335253 Llamas-Ong, Christine 335253 Llamas-Ong, Christine 335253 Reyes, Wilson I need it to look like this.... ---A--- ---B------------------------------------ Job ID Name 335119 Malonzo, Elaine; Monsalve, Edmund; Pilapil, Flofer; Santiago, Minerva 335212 Calub, Charina; Requita, Darius; Santiago, Minerva 335253 Bonifacio, DaisyLou; Delos Santos, Veronica; Llamas-Ong, Christine Hoping to have an answer. best regards, akaDong Looking at your output, it appears you want to have only unique names output. The following macro does not depend on your data being sorted. If your original data is not sorted, and you want the output sorted, some sort routines can be added to the macro. Read the comments in the macro for additional, important information. To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens. To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN. =========================================== Option Explicit Sub JobIDNameCombine() 'look for Job ID and combine all the 'Names under that ID. 'Names and Job ID's do NOT need to be sorted but 'unless you add a sort routine, they will be output in 'the order of first entry Dim rJobID As Range Dim rDest As Range Dim c As Range Dim sJobID As String Dim cJobID As Collection Dim cName As Collection Dim sNames() As String Dim i As Long, j As Long Dim k As Long Set rJobID = Range("Job_ID") 'This is a Named Range on the worksheet 'you could also use "selection" or any number of other ways of 'setting the range of Job ID's Set rDest = Range("D1") ' or wherever 'Clear destination cells and print headers 'Max number to clear would be count of Job ID's Range(rDest, rDest.Offset(rJobID.Rows.Count - 1, 1)).ClearContents rDest.Value = "Job ID" rDest.Offset(0, 1).Value = "Names" 'get unique list of Job ID's Set cJobID = New Collection On Error Resume Next For Each c In rJobID cJobID.Add c.Value, CStr(c.Value) Next c On Error GoTo 0 k = 0 For i = 1 To cJobID.Count Set cName = New Collection On Error Resume Next For Each c In rJobID If c.Value = cJobID(i) Then cName.Add c.Offset(0, 1).Value, CStr(c.Offset(0, 1).Value) End If Next c On Error GoTo 0 ReDim sNames(cName.Count - 1) For j = 0 To UBound(sNames) sNames(j) = cName(j + 1) Next j k = k + 1 rDest.Offset(k, 0).Value = cJobID(i) rDest.Offset(k, 1).Value = Join(sNames, "; ") Next i Set cJobID = Nothing Set cName = Nothing End Sub --ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging multiple cells into a single cell
The data should have headers to columns A & B for the filter to function and assumes the code below with no spaces. Option Explicit Sub Joins() Dim rng As Range, cel As Range Dim i As Long, txt As String 'Filter unique records Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("E1"), Unique:=True 'Get range of numbers Set rng = Range("E1").CurrentRegion.Columns(1) 'Loop through range For i = rng.Cells.Count To 1 Step -1 'Find first instance of number Set cel = rng.Find(Cells(i, 5), After:=Cells(1, 5), LookIn:=xlValues, Lookat:=xlWhole) 'Get name txt = Cells(i, 6) If Not i = cel.Row Then 'Append name to first occurrence of number cel.Offset(, 1) = cel.Offset(, 1) & "; " & txt 'Delete copied data Cells(i, 5).Resize(, 2).Delete shift:=xlUp End If Next End Sub -- mdmackillop ------------------------------------------------------------------------ mdmackillop's Profile: http://www.thecodecage.com/forumz/member.php?userid=113 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=67468 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging multiple cells into a single cell
Wow!! thank you .... you're a genius !! (^^,)
"Ron Rosenfeld" wrote: On Sun, 22 Feb 2009 14:49:01 -0800, akaDong wrote: Dear all, please help me, i have a two column data. I need to merge data from column b with the same data in column a into a single cell separated by ";". the sheet looks as follows: ---A--- ---B--- Job ID Name 335119 Malonzo, Elaine 335119 Monsalve, Edmund 335119 Pilapil, Flofer 335119 Pilapil, Flofer 335119 Santiago, Minerva 335212 Calub, Charina 335212 Calub, Charina 335212 Calub, Charina 335212 Requita, Darius 335212 Santiago, Minerva 335253 Bonifacio, DaisyLou 335253 Bonifacio, DaisyLou 335253 Delos Santos, Veronica 335253 Delos Santos, Veronica 335253 Llamas-Ong, Christine 335253 Llamas-Ong, Christine 335253 Reyes, Wilson I need it to look like this.... ---A--- ---B------------------------------------ Job ID Name 335119 Malonzo, Elaine; Monsalve, Edmund; Pilapil, Flofer; Santiago, Minerva 335212 Calub, Charina; Requita, Darius; Santiago, Minerva 335253 Bonifacio, DaisyLou; Delos Santos, Veronica; Llamas-Ong, Christine Hoping to have an answer. best regards, akaDong Looking at your output, it appears you want to have only unique names output. The following macro does not depend on your data being sorted. If your original data is not sorted, and you want the output sorted, some sort routines can be added to the macro. Read the comments in the macro for additional, important information. To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens. To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN. =========================================== Option Explicit Sub JobIDNameCombine() 'look for Job ID and combine all the 'Names under that ID. 'Names and Job ID's do NOT need to be sorted but 'unless you add a sort routine, they will be output in 'the order of first entry Dim rJobID As Range Dim rDest As Range Dim c As Range Dim sJobID As String Dim cJobID As Collection Dim cName As Collection Dim sNames() As String Dim i As Long, j As Long Dim k As Long Set rJobID = Range("Job_ID") 'This is a Named Range on the worksheet 'you could also use "selection" or any number of other ways of 'setting the range of Job ID's Set rDest = Range("D1") ' or wherever 'Clear destination cells and print headers 'Max number to clear would be count of Job ID's Range(rDest, rDest.Offset(rJobID.Rows.Count - 1, 1)).ClearContents rDest.Value = "Job ID" rDest.Offset(0, 1).Value = "Names" 'get unique list of Job ID's Set cJobID = New Collection On Error Resume Next For Each c In rJobID cJobID.Add c.Value, CStr(c.Value) Next c On Error GoTo 0 k = 0 For i = 1 To cJobID.Count Set cName = New Collection On Error Resume Next For Each c In rJobID If c.Value = cJobID(i) Then cName.Add c.Offset(0, 1).Value, CStr(c.Offset(0, 1).Value) End If Next c On Error GoTo 0 ReDim sNames(cName.Count - 1) For j = 0 To UBound(sNames) sNames(j) = cName(j + 1) Next j k = k + 1 rDest.Offset(k, 0).Value = cJobID(i) rDest.Offset(k, 1).Value = Join(sNames, "; ") Next i Set cJobID = Nothing Set cName = Nothing End Sub --ron |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging multiple cells into a single cell
On Wed, 25 Feb 2009 09:20:01 -0800, akaDong
wrote: Wow!! thank you .... you're a genius !! (^^,) You're welcome. Glad to help. Genius? I think not, but I have learned a lot from others in this NG. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging multiple (n) cells into one cell | Excel Discussion (Misc queries) | |||
Concatenate Multiple Cells into A Single Cell | Excel Discussion (Misc queries) | |||
Merging multiple workbooks with crosslinked formulas into single f | Excel Discussion (Misc queries) | |||
How to Copy single Cell into Multiple cells | Excel Worksheet Functions | |||
Using a single cell value to repopulate multiple cells | Excel Discussion (Misc queries) |