Combining Contents of cells into one cell

[ExcelChallenge]

I have a spreadsheet with one column (let's call it column 1) that has
numbers as identifers. In column 1, there may be several occurences of the
same number. In the row for the number, there is another column that
contains text (let's call it column 2). Here's what I want to do. I want to
combine all the text from each column 2 occurence related to the number in
column 1. So, if column 1 has a row with the number 333 and column 2 in that
row has the text "Hello" and another row in column 1 also has number 333 but
the column 2 text is "What", I want to have output in one cell = Hello What
with the column 1 cell on the same row showing 333. Is this possible?

 

[Don Guillett]

Here is a macro using FINDNEXT. Look in vba help index for more info on
findnext.

Sub findnums()
lr = Cells(Rows.Count, "a").End(xlUp).Row
With Range("a1:a" & lr)
Set c = .Find(333, LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
ms = ms & " " & c.Offset(, 1)

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
MsgBox ms
End Sub

 

[Bernard Liengme]

In A1:A100 I have some numbers; some numbers are repeated
In B2:B100 I have some text
SO, for example, there are 4 cells in column A with value 333 and the
corresponding B cells have "dog", "cat", "mouse", "rat"
In D2 I enter =tryme(A2) and the UDF below gives me" dog cat mouse rat

Here is the UDF
Function tryme(mytest)
Application.Volatile
For Each mycell In Range("A2:A100")
If mycell.Value = mytest.Value Then
tryme = tryme & " " & mycell.Offset(0, 1)
End If
Next
End Function

You can change "A1:A100" to fit your requirement
If you text column is not adjacent to the number column, change the 1 in the
OFFSET phase to reflect your world.

If you are new to VBA try David McRitchie's site on "getting started" with
VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm

best wishes

 

[ExcelChallenge]

Thanks! That worked!

Here is a macro using FINDNEXT. Look in vba help index for more info on
findnext.

Sub findnums()
lr = Cells(Rows.Count, "a").End(xlUp).Row
With Range("a1:a" & lr)
Set c = .Find(333, LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
ms = ms & " " & c.Offset(, 1)

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
MsgBox ms
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[ExcelChallenge]

Thanks! That worked.

In A1:A100 I have some numbers; some numbers are repeated
In B2:B100 I have some text
SO, for example, there are 4 cells in column A with value 333 and the
corresponding B cells have "dog", "cat", "mouse", "rat"
In D2 I enter =tryme(A2) and the UDF below gives me" dog cat mouse rat

Here is the UDF
Function tryme(mytest)
Application.Volatile
For Each mycell In Range("A2:A100")
If mycell.Value = mytest.Value Then
tryme = tryme & " " & mycell.Offset(0, 1)
End If
Next
End Function

You can change "A1:A100" to fit your requirement
If you text column is not adjacent to the number column, change the 1 in the
OFFSET phase to reflect your world.

If you are new to VBA try David McRitchie's site on "getting started" with
VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

 

[ExcelChallenge]

Hi Bernard,

I'm now using Excel 2007. I used this in my older version of Excel; and, it
worked. However, now I get "Compile Error: Ambiguous name detected: tryme"

Is this a result of Excel 2007?

Thanks,

Rod