How to concatenate the data....???

[ikhan123]

Hi All,

I have data in excel and i am trying to merge the data in a single column and i also try concatenate function to merge but unfortunately i didn't get any out put..

Below is the example of the same and please let me know if this is possible to do with the help of formula only.... i need formula not (VBA).....

Solution i already mentioned in 3rd column, but i didn't get it with formula its just manually done.....

I have 1,45,786 row and i can't done it with manually........

124536	Irshad Khan	irshad khan (124536, 154823, 1258)
154823
1258
458756	Irshad Khan	Irshad Khan (458756, 585857)
585857
9587569	Irshad Khan	Irshad Khan (9587569)
8547896	Irshad Khan
9632158
987456
9686867
3211456
65892	Irshad Khan
258574
2586

<tbody>
</tbody>

Thanks every one,

Waiting for your reply!!!!!
and it would be appreciated if i got it..

 

[hiker95]

ikhan123,

Welcome to the MrExcel forum.

i need formula not (VBA)
I have 1,45,786 row

I would think that a formula solution for 1,45,786 rows would be very slow.

You may want to consider a macro solution.

I have two versions of the macro.

Sample raw data:

Excel 2007
	A	B	C
1	124536	Irshad Khan
2	154823
3	1258
4	458756	Irshad Khan
5	585857
6	9587569	Irshad Khan
7	8547896	Irshad Khan
8	9632158
9	987456
10	9686867
11	3211456
12	65892	Irshad Khan
13	258574
14	2586
15
Sheet1

After macro ReorgData:

Excel 2007
	A	B	C
1	124536	Irshad Khan	Irshad Khan (124536, 154823, 1258)
2	154823
3	1258
4	458756	Irshad Khan	Irshad Khan (458756, 585857)
5	585857
6	9587569	Irshad Khan	Irshad Khan (9587569)
7	8547896	Irshad Khan	Irshad Khan (8547896, 9632158, 987456, 9686867, 3211456)
8	9632158
9	987456
10	9686867
11	3211456
12	65892	Irshad Khan	Irshad Khan (65892, 258574, 2586)
13	258574
14	2586
15
Sheet1

After macro ReorgDataV2:

Excel 2007
	A	B	C
1	124536	Irshad Khan	Irshad Khan (124536, 154823, 1258)
2	154823		Irshad Khan (458756, 585857)
3	1258		Irshad Khan (9587569)
4	458756	Irshad Khan	Irshad Khan (8547896, 9632158, 987456, 9686867, 3211456)
5	585857		Irshad Khan (65892, 258574, 2586)
6	9587569	Irshad Khan
7	8547896	Irshad Khan
8	9632158
9	987456
10	9686867
11	3211456
12	65892	Irshad Khan
13	258574
14	2586
15
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 03/27/2013
' http://www.mrexcel.com/forum/excel-questions/693838-how-concatenate-data.html
Dim a As Variant, c As Variant
Dim i As Long, ii As Long, h As String, sr As Long
With ActiveSheet.Cells(1).CurrentRegion
  a = .Value
  ReDim c(1 To UBound(a, 1), 1 To 1)
  sr = 1
  h = ""
  .Columns(3).ClearContents
  For i = 1 To UBound(a, 1)
    If a(i, 2) <> "" And h = "" Then
      h = a(i, 2) & " (" & a(i, 1) & ", "
    ElseIf a(i, 2) <> "" And h <> "" Then
      c(sr, 1) = Left(h, Len(h) - 2) & ")"
      sr = i
      h = ""
      h = a(i, 2) & " (" & a(i, 1) & ", "
    Else
      h = h & a(i, 1) & ", "
    End If
  Next i
  c(sr, 1) = Left(h, Len(h) - 2) & ")"
  .Cells(1, 3).Resize(UBound(c, 1)) = c
  .Columns(3).AutoFit
End With
End Sub


Sub ReorgDataV2()
' hiker95, 03/27/2013
' http://www.mrexcel.com/forum/excel-questions/693838-how-concatenate-data.html
Dim a As Variant, c As Variant
Dim i As Long, ii As Long, h As String, sr As Long
With ActiveSheet.Cells(1).CurrentRegion
  a = .Value
  ReDim c(1 To UBound(a, 1), 1 To 1)
  sr = 1
  h = "" 
  .Columns(3).ClearContents
For i = 1 To UBound(a, 1)
    If a(i, 2) <> "" And h = "" Then
      h = a(i, 2) & " (" & a(i, 1) & ", "
    ElseIf a(i, 2) <> "" And h <> "" Then
      ii = ii + 1
      c(ii, 1) = Left(h, Len(h) - 2) & ")"
      h = ""
      h = a(i, 2) & " (" & a(i, 1) & ", "
    Else
      h = h & a(i, 1) & ", "
    End If
  Next i
  ii = ii + 1
  c(ii, 1) = Left(h, Len(h) - 2) & ")"
  .Cells(1, 3).Resize(UBound(c, 1)) = c
  .Columns(3).AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro, or run the ReorgDataV2 macro.