Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,365
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
merge several cells with text into 1 cell chum Excel Discussion (Misc queries) 4 July 25th 06 04:30 PM
Custom functions calculating time arguments Help Desperate Bill_De Excel Worksheet Functions 12 April 25th 06 02:22 AM
stopping pasted text deliminate across multiple cells Paul from NZ Excel Discussion (Misc queries) 3 September 8th 05 06:06 AM
Convert data of cells to any type: Number, Date&Time, Text Kevin Excel Discussion (Misc queries) 0 December 30th 04 07:55 AM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 11:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"