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

 

[Fluff]

How about

Sub Vishaal()
   Dim Rng As Range
   
   With Range("B3:B" & Range("C" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks)
      For Each Rng In .Areas
         Rng.Offset(-1, 1).Resize(1).Value = Join(Application.Transpose(Rng.Offset(-1, 1).Resize(Rng.Count + 1).Value), " ")
      Next Rng
      .EntireRow.Delete
   End With
End Sub

 

[Vishaal]

Its showing the following error

Run-time error '1004':

No cells were found

pls check and update.......

How about

Sub Vishaal()
   Dim Rng As Range
  
   With Range("B3:B" & Range("C" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks)
      For Each Rng In .Areas
         Rng.Offset(-1, 1).Resize(1).Value = Join(Application.Transpose(Rng.Offset(-1, 1).Resize(Rng.Count + 1).Value), " ")
      Next Rng
      .EntireRow.Delete
   End With
End Sub

 

[Fluff]

Are the codes in col B starting on row 3 with the header on row 2?

 

[Vishaal]

Yes Fluff Ji,

Are the codes in col B starting on row 3 with the header on row 2?

Are the codes in col B starting on row 3 with the header on row 2?

 

[lrobbo314]

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

 

[sandy666]

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

 

[sandy666]

@lrobbo314

Copy paste didn't work with your OP

try this way

1. copy table from the post
2. paste into the sheet
3. click the first cell of data (not header)
4. click on the end of this value in formula bar
5. shift +left arrow
6. ctrl+c (non-printable character)
7. select whole range
8. Find/Replace copied something with nothing

 

[lrobbo314]

@lrobbo314

try this way

I went to follow your steps, and it pasted correctly right off the bat. I think I didn't have everything selected or something. All the rows were staggered and it looked really strange.

Thanks for the info though.

 

[sandy666]

Thanks for the info though.

most of ranges copied directly from the sheet and pasted into the post contain CODE 63 on the end of the all data or most of them