Concatenate

alidurfani

Board Regular
Joined
Apr 12, 2019
Messages
106
Office Version
  1. 2016
Platform
  1. Windows
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

Cell Formulas
RangeFormula
C1:C15C1=IF(A1,D1,"")
D1:D15D1=IF(A2,B1,B1&" "&D2)


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

Book8
AB
11A B C D E
22A B C D
33A B C
44A B
52A
Sheet1
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,057
Office Version
  1. 365
Platform
  1. Windows
How about getting rid of the formulae & just use
VBA Code:
Sub alidurfani()
   Dim Rng As Range
   
   For Each Rng In Range("A:A").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
 
Solution

alidurfani

Board Regular
Joined
Apr 12, 2019
Messages
106
Office Version
  1. 2016
Platform
  1. Windows
How about getting rid of the formulae & just use
VBA Code:
Sub alidurfani()
   Dim Rng As Range
  
   For Each Rng In Range("A:A").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
Hi Fluff,

Thank you very much!
Perfect solution as always.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,057
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,590
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Definitely use Fluff's solution, not this one! It is Sunday and I was bored, so I decided to see if this could be done without using a loop. Assuming there is no other data in the other columns that need to be kept in sync with the values in Column A, this will produce the same result as Fluff's macro...
VBA Code:
Sub alidurfani()
  Dim LastRow As Long, Arr As Variant
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  Range("A1:A" & LastRow) = Application.Transpose(Split(Mid(Join(Application.Transpose(Evaluate(Replace("IF(A1:A#="""","""",""|""&A1:A#&"":"")&B1:B#", "#", LastRow)))), 2), " |"))
  Columns("A").SpecialCells(xlConstants, xlErrors).Clear
  Columns("B").Clear
  Columns("A").TextToColumns , xlDelimited, , , False, False, False, False, True, ":"
End Sub
 

alidurfani

Board Regular
Joined
Apr 12, 2019
Messages
106
Office Version
  1. 2016
Platform
  1. Windows
Definitely use Fluff's solution, not this one! It is Sunday and I was bored, so I decided to see if this could be done without using a loop. Assuming there is no other data in the other columns that need to be kept in sync with the values in Column A, this will produce the same result as Fluff's macro...
VBA Code:
Sub alidurfani()
  Dim LastRow As Long, Arr As Variant
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  Range("A1:A" & LastRow) = Application.Transpose(Split(Mid(Join(Application.Transpose(Evaluate(Replace("IF(A1:A#="""","""",""|""&A1:A#&"":"")&B1:B#", "#", LastRow)))), 2), " |"))
  Columns("A").SpecialCells(xlConstants, xlErrors).Clear
  Columns("B").Clear
  Columns("A").TextToColumns , xlDelimited, , , False, False, False, False, True, ":"
End Sub
Thank you Rick Rothstein,
This also works but I have data in column C which should be kept with the values in column A.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,590
Office Version
  1. 2010
Platform
  1. Windows
...but I have data in column C which should be kept with the values in column A.
For future questions you ask, you should mention dependencies like that. As you can see from Fluff's and my solutions, it is possible to achieve the same result in multiple ways... if you don't tell us about other dependencies, we won't know to account for them.
 

alidurfani

Board Regular
Joined
Apr 12, 2019
Messages
106
Office Version
  1. 2016
Platform
  1. Windows
For future questions you ask, you should mention dependencies like that. As you can see from Fluff's and my solutions, it is possible to achieve the same result in multiple ways... if you don't tell us about other dependencies, we won't know to account for them.
Ok thank you for your guidance,
I will take care of it next time.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,087
Messages
5,576,035
Members
412,694
Latest member
Deaf1Too
Top