Concatenate

Sufiyan97

Well-known Member
Joined
Apr 12, 2019
Messages
1,538
Office Version
  1. 365
  2. 2013
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
...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.
 
Upvote 0
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.
 
Upvote 0
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

Hello Your code works perfect but in last row it is giving below result
In last row it is getting trailing spaces
if possible please modify otherwise it is perfect

Automatic CONCATENATE.xlsm
ABC
4911/24/2020BNKCD SETTLE DES:MERCH DEP ID:286000000494070 INDN:AMERISPEC INSPECTION S CO ID:1043763809 CCD399.20
501996
5194
Sheet1
Cell Formulas
RangeFormula
B50B50=LEN(B49)
B51B51=LEN(TRIM(B49))
 
Upvote 0
How about
VBA Code:
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
 
Upvote 0
Solution

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top