Combine multiple cells into one cell.

[jfcby]

Hi,

In Excel 2003, I have a worksheet that has 50 rows and 7 columns of
data.

In G4 - G6 my data is formated like this:

MECH RM 125
MECH RM 548
KITCHEN 258

In the above example I would sometimes have 20 to 30 rows of data to
combine.

What I need F1 to look like: MECH RM 125, MECH 548, KITCHEN 258.

The formula I'm using now in F1 is: =G4 & ", " & G5 & ", " & G6

Is there a way to combine several rows into one without having to
manually type each row reference?

Thank for your help,
jfcby

 

[Ken]

jfcby

I think it would be a pretty difficult formula to include the commas
and much flexibility in your range. You could probably accomplish what
you want with a simple user defined function to which you would pass
the relevant cells and which would return the desired string.

Something like this should get you started

Function comb(R)

Dim m As String
Dim i As Integer

For i = 1 To R.Rows.Count
m = m & R.Cells(i, 1) & ", "
Next i

comb = m

End Function

Your worksheet would have =comb(range from which to string together
values). The passed range could be defined with counts or offsets or
whatever is most efficient way to describe the cells you want to
string together.

Good luck

Ken
Norfolk, Va

 

[jfcby]

jfcby

I think it would be a pretty difficult formula to include the commas
and much flexibility in your range. You could probably accomplish what
you want with a simple user defined function to which you would pass
the relevant cells and which would return the desired string.

Something like this should get you started

Function comb(R)

Dim m As String
Dim i As Integer

For i = 1 To R.Rows.Count
    m = m & R.Cells(i, 1) & ", "
Next i

comb = m

End Function

Your worksheet would have =comb(range from which to string together
values).  The passed range could be defined with counts or offsets or
whatever is most efficient way to describe the cells you want to
string together.

Good luck

Ken
Norfolk, Va











- Show quoted text -

Thank you for your help and function Ken.

I do not know call or run a function. I researched function in this
forum and in the Excel help file and still do not understand how
functions work.

Is it possible to convert the function into a macro?

Thank you for your help,
jfcby

 

[jfcby]

Thank you for your help and function Ken.

I do not know call or run a function. I researched function in this
forum and in the Excel help file and still do not understand how
functions work.

Is it possible to convert the function into a macro?

Thank you for your help,
jfcby- Hide quoted text -

- Show quoted text -

This is my macro working solution:

Sub CombineCells()
'Combines data from multiple selected cells into one cell.

Dim firstCell As Range
Dim lastCell As Range
Dim offsetCell As Range
Dim i As Integer

Set firstCell = Selection(1)
Set lastCell = Selection(Selection.Count)
Set offsetCell = Selection(Selection.Count).Offset(0, 1)

If lastCell > 1 Then
For i = 1 To Selection.Count
If offsetCell = "" Then
offsetCell = offsetCell & Selection(i)
Else 'If offsetCell > "" Then
offsetCell = offsetCell & ", " & Selection(i)
End If
Next
Else 'If lastCell <= 1 Then
MsgBox "Select 2 or more cells to continue!"
End If
End Sub

Thanks for your help,
jfcby