Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to combine multiple rows of text cells into one cell
Lets say I have
A1 has value "Y", Cell B1 having value : ABC A1 has value "Y", Cell B2 having value : DEF A1 has value "N",Cell B3 having value : GHI ... ... ... An has value "Y", Cell Bn having value : LKJ I wish to concatenate all these text into one single cell, based on a condition for ex: For all records wherein I have value of Ai = "Y", I wish to have a comma separated list of all values in Cell Bi Is there a formulae which i can use for concatenating this range of values Would appreciate all help from your side thanks Subda |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to combine multiple rows of text cells into one cell
You could do this with a User Defined Function (UDF). Put the UDF into a
code module and then reference it in a cell just like you would any other Excel worksheet function. Using your example, where test value is in column A and values to be concatenated are in column B, this should work as a UDF: Private Function BuildLongList(TestValue As Variant) Dim LC As Long Application.Volatile Do While LC <= Range("A" & Rows.Count).End(xlUp).Row If Range("A1").Offset(LC, 0) = TestValue Then BuildLongList = BuildLongList & Range("B1").Offset(LC, 0) & ", " End If LC = LC + 1 Loop If Right(BuildLongList, 2) = ", " Then BuildLongList = Left(BuildLongList, Len(BuildLongList) - 2) End If End Function Then in the cell(s) where you want the results to show up you would put formula similar to these, first example for any cell other than A1 or B1 in row 1, and second for any cell other than A9 or B9 in row 9: =BuildLongList(A1) =BuildLongList(A9) " wrote: Lets say I have A1 has value "Y", Cell B1 having value : ABC A1 has value "Y", Cell B2 having value : DEF A1 has value "N",Cell B3 having value : GHI ... ... ... An has value "Y", Cell Bn having value : LKJ I wish to concatenate all these text into one single cell, based on a condition for ex: For all records wherein I have value of Ai = "Y", I wish to have a comma separated list of all values in Cell Bi Is there a formulae which i can use for concatenating this range of values Would appreciate all help from your side thanks Subda |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to combine multiple rows of text cells into one cell
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merge several cells with text into 1 cell | Excel Discussion (Misc queries) | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
stopping pasted text deliminate across multiple cells | Excel Discussion (Misc queries) | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |