Consoldating Data

  • Thread starter Thread starter Pierre
  • Start date Start date
P

Pierre

I have a spreadsheet with 57 columns of data. I'd like to
figure out a quick way to have all the data (text) into
just one column. I don't want add or multiply anything...
just want all the data in 1 column.
 
Hi Pierre

You can use a function like this one
Copy the function in a normal module

=Rangecat(A1:BE1," ")
Use this in the worksheet


From J.E. McGimpsey
It is working for rows an columns

Public Function RangeCat(rng As Excel.Range, _
Optional delimiter As String = "", _
Optional direction As Integer = 1) As Variant
Dim myColumn As Range
Dim cell As Range
If direction = 1 Then 'by rows
For Each cell In rng
RangeCat = RangeCat & delimiter & cell.Text
Next cell
ElseIf direction = 2 Then 'by cols
For Each myColumn In rng.Columns
For Each cell In myColumn.Cells
RangeCat = RangeCat & delimiter & cell.Text
Next cell
Next myColumn
Else
RangeCat = CVErr(xlErrNA)
Exit Function
End If
RangeCat = Mid(RangeCat, 1 + Len(delimiter))
End Function
 
You can make values from your formulas like this

Select the formula cells
Ctrl-C
Edit>Paste Special
Check Values
OK
 
Back
Top