VBA/macro/code for combined the name from multiple row

[Vishaal]

HI All,

Thanks in advance, we have the following sheet in excel and there is names in two or three rows, we want to combine them.

For example Code No. 763 (row 10) name in three rows, we want to make in one "UGC-Net/JRF/SET Teaching, Research, practice, numerical, 100 mock Aptitude (General Paper-1)"

We have many names this type in file. help pls.

Excel 2010 32 bit

	B​	C​
2​	Code​	Name
3​	420​	U.G.C. NET/JRF/SET Teaching & Research
4​	​	Aptitude (General Paper-I)
5​	1553​	UGC-Net/JRF/SET Teaching & Research Aptitude (General Paper-1)
6​	1588​	CSIR-UGC NET/JRF/SET Chemical Sciences
7​	500​	CSIR-UGC NET/JRF/SET Physical Sciences
8​	1551​	CSIR-UGC NET/JRF/SET Physical Sciences
9​	1587​	CSIR-UGC NET/JRF/SLET Mathematical Sciences
10​	763​	UGC-Net/JRF/SET Teaching, Research,
11​	​	practice, numerical, 100 mock
12​	​	Aptitude (General Paper-1)
13​	894​	UGC NET/JRF/SET Computer Science and Applications (Paper II)
14​	931​	UGC-NET/JRF/SLET Physical Education (Paper II)
15​	317​	CSIR-UGC NET/JRF/SET Life Sciences

Sheet: Sheet1

 

[Peter_SSs]

Another vba option that might be useful if the data set is very large.
This code writes the results in columns D:E but if you want to replace the original data, just remove the .Offset(, 2) near the end.

Sub CombineRows()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  
  With Range("B3", Range("C" & Rows.Count).End(xlUp))
    a = .Value
    ReDim b(1 To UBound(a), 1 To 2)
    For i = 1 To UBound(a)
      If Len(a(i, 1)) > 0 Then
        k = k + 1
        b(k, 1) = a(i, 1)
        b(k, 2) = a(i, 2)
      Else
        b(k, 2) = b(k, 2) & " " & a(i, 2)
      End If
    Next i
    .Offset(, 2).Value = b
  End With
End Sub

 

[Vishaal]

Thanks lrobbo314, but we are using the excel 2010 and have no idea about power query

You can do this with Power Query. Copy paste didn't work with your OP, so I used fake data. Should work just the same.

Book1
	A	B	C	D	E
1	Code	Name		Code	Name
2	A	ABC		A	ABC 123
3		123		B	X
4	B	X		C	CDE 345
5	C	CDE
6		345
Sheet5

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    ToText = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Name", type text}}),
    FilledDown = Table.FillDown(ToText,{"Code"}),
    Group = Table.Group(FilledDown, {"Code"}, {{"Name", each _, type table [Code=text, Name=anynonnull]}}),
    Combine = Table.TransformColumns(Group,{"Name", each Text.Combine(Table.Column(_,"Name")," ")})
in
    Combine

 

[Vishaal]

Thanks sandy666, we are using excel 2010 and have no idea about power query

maybe an alternative M-code ?

Code	Name		Code	Name
420	U.G.C. NET/JRF/SET Teaching & Research		420	U.G.C. NET/JRF/SET Teaching & Research Aptitude (General Paper-I)
	Aptitude (General Paper-I)		1553	UGC-Net/JRF/SET Teaching & Research Aptitude (General Paper-1)
1553	UGC-Net/JRF/SET Teaching & Research Aptitude (General Paper-1)		1588	CSIR-UGC NET/JRF/SET Chemical Sciences
1588	CSIR-UGC NET/JRF/SET Chemical Sciences		500	CSIR-UGC NET/JRF/SET Physical Sciences
500	CSIR-UGC NET/JRF/SET Physical Sciences		1551	CSIR-UGC NET/JRF/SET Physical Sciences
1551	CSIR-UGC NET/JRF/SET Physical Sciences		1587	CSIR-UGC NET/JRF/SLET Mathematical Sciences
1587	CSIR-UGC NET/JRF/SLET Mathematical Sciences		763	UGC-Net/JRF/SET Teaching, Research, practice, numerical, 100 mock Aptitude (General Paper-1)
763	UGC-Net/JRF/SET Teaching, Research,		894	UGC NET/JRF/SET Computer Science and Applications (Paper II)
	practice, numerical, 100 mock		931	UGC-NET/JRF/SLET Physical Education (Paper II)
	Aptitude (General Paper-1)		317	CSIR-UGC NET/JRF/SET Life Sciences
894	UGC NET/JRF/SET Computer Science and Applications (Paper II)
931	UGC-NET/JRF/SLET Physical Education (Paper II)
317	CSIR-UGC NET/JRF/SET Life Sciences

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Table.FillDown(Source,{"Code"}), {"Code"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Name", each Table.Column([Count],"Name")),
    Extract = Table.TransformColumns(List, {"Name", each Text.Combine(List.Transform(_, Text.From), " "), type text})
in
    Extract

 

[Vishaal]

Thanks Peter_SSs,

We have tried but code runs and copy the same data to right side in excel

Another vba option that might be useful if the data set is very large.
This code writes the results in columns D:E but if you want to replace the original data, just remove the .Offset(, 2) near the end.

Sub CombineRows()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
 
  With Range("B3", Range("C" & Rows.Count).End(xlUp))
    a = .Value
    ReDim b(1 To UBound(a), 1 To 2)
    For i = 1 To UBound(a)
      If Len(a(i, 1)) > 0 Then
        k = k + 1
        b(k, 1) = a(i, 1)
        b(k, 2) = a(i, 2)
      Else
        b(k, 2) = b(k, 2) & " " & a(i, 2)
      End If
    Next i
    .Offset(, 2).Value = b
  End With
End Sub

 

[Peter_SSs]

We have tried but code runs and copy the same data to right side in excel

So the cells in column B that appear empty in your sample data are not in fact empty. Try this instead.

If Len(a(i, 1)) > 0 Then
If Len(a(i, 1)) > 1 Then

 

[sandy666]

we are using excel 2010 and have no idea about power query

more about PowerQuery aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

 

[Vishaal]

Thanks Peter_SSs, Its working

So the cells in column B that appear empty in your sample data are not in fact empty. Try this instead.

If Len(a(i, 1)) > 0 Then
If Len(a(i, 1)) > 1 Then

 

[Vishaal]

Thanks sandy666, we will check all the url provided by you

more about PowerQuery aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

 

[Peter_SSs]

Thanks Peter_SSs, Its working

Good news.