Multiple Rows data to be concatenated into cell...

[Ashfaq]

Hi,


I need your help on how to get multiple rows data into one cell.

[pre]

Date                           Data               Result
01/01/2013                     A                  A, B, 500
B
500
02/01/2013                     C                  C, D, E,600
D
E
600
03/01/2013                     F                  F, 700, G, H, I, 800
700
G
H
I
800

[/pre]
Is there any formula or logic which can be applied to it, if so then please help...


*Date/Data/Result are the field headers


Thanks

 

[Debraj]

Hi Asfaq..

Welcome to the Forum..

Next time if you have any query.. Please select Correct Forum.. section..

Please read three green sticky notes..


and regarding your question..

Is this what you want to achieve!!.. Please approve.. so that we can proceed..

[pre]

1	A		B	        C
_____________________________________________________________
2	01-01-13	A	        A,B,500
3			B
4			500
_____________________________________________________________
5	02-01-13	C	        C,D,E,600
6			D
7			E
8			600
______________________________________________________________
9	03-01-13	F	        F,700,G,H,I,800
10			700
11			G
12			H
13			I
14			800

[/pre]
https://dl.dropbox.com/u/78831150/Excel/Multiple%20Rows%20data%20to%20be%20concatenated%20into%20cell.jpg


Regards,

Deb

 

[Ashfaq]

Oh yes, that is what I want to achieve.


II will try to use the correct forum from next time.


Thanks

 

[bobhc]

post moved

 

[Ashfaq]

No solution provided yet...


Iam sorry, I have just read those sticky notes and will try to take care from next time.


Thanks

Ashfaq

 

[shrivallabha]

Deb seems to be bit busy to reply. Assuming your data is in column A and B like Deb's layout and you want to get results in column C, test the following code:

[pre]

Public Sub ConcatData()
Application.ScreenUpdating = False
For i = Range("B" & Rows.Count).End(xlUp).Row To 1 Step -1
j = Range("A" & i).End(xlUp).Row
Range("C" & j).Value = Join(Filter(Application.Transpose(Evaluate("=IF(B" & j & _
":B" & i & "<>"""",B" & j & ":B" & i & ",""~"")")), "~", False), ",")
i = j
Next i
Application.ScreenUpdating = True
End Sub

[/pre]

 

[Ashfaq]

Thanks Shrivallabha!


The macro is working fine but after running it "Type Mismatch" alert is shown and then it is giving the correct result.


I am not familiar with this macro/ coding so it is hard for me to use for obtaining results for about 1000 rows containing different textual information.


Any logical formula or a function would help a lot.


Thanks

Ashfaq

 

[shrivallabha]

Unfortunately there are no easy formulas for concatenating. You can search for few options like ACONCAT by harlan grove or MCONCAT in morefunc but they also use VBA coding. So usage of VBA is a bit inevitable.


I did not get any errors while testing it for the sample data. Could you upload the file where you get this error? Then a solution can be worked out.


To upload a file, read the third green sticky post.

http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[Ashfaq]

I tried uploading the file thru Rapidshare but it is not uploading the file. I will try to send you the file once it is uploaded.


Thanks