Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining Contents of cells into one cell
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining Contents of cells into one cell
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" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining Contents of cells into one cell
Thanks! That worked!
"Don Guillett" wrote: 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 "ExcelChallenge" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining Contents of cells into one cell
Thanks! That worked.
"Bernard Liengme" wrote: 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" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining Contents of cells into one cell
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 "Bernard Liengme" wrote: 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" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining Cell Contents (Part 2) | Excel Discussion (Misc queries) | |||
Combining Cell Contents | Excel Discussion (Misc queries) | |||
Combining Cell Contents | Excel Worksheet Functions | |||
Combining Cell Contents - entire columns | Excel Worksheet Functions | |||
Combining cell contents when there is content to combine | Excel Discussion (Misc queries) |