Concatenate

[Sufiyan97]

Hello everyone,

I am using below formula and I don't have any problem with it but I need to do some manual formatting like deleting blank rows and deleting column B and D

Book7
	A	B	C	D
1	1	A	A B C D E	A B C D E
2		B		B C D E
3		C		C D E
4		D		D E
5		E		E
6	2	A	A B C D	A B C D
7		B		B C D
8		C		C D
9		D		D
10	3	A	A B C	A B C
11		B		B C
12		C		C
13	4	A	A B	A B
14		B		B
15	2	A	A	A
Sheet1

Cell Formulas
Range	Formula
C1:C15	C1	=IF(A1,D1,"")
D1:D15	D1	=IF(A2,B1,B1&" "&D2)

I am looking for a Macro to do it automatically to get result like below

Book8
	A	B
1	1	A B C D E
2	2	A B C D
3	3	A B C
4	4	A B
5	2	A
Sheet1

 

[Sufiyan97]

How about

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

Thank you Fluff,

Works Perfect!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Sufiyan97]

Hello Fluff,

This code is very useful in our office and saving our time and I am getting credit because of this and when my boss seen this code he said me you are a champ!

but all actual credit belongs to you only.

Thank you very much again ?!

 

[Fluff]