Merging Cells between two specific cells

[baha17]

Hi Everyone,

I cannot upload my worksheet because of restrictions of my company. I have a worksheet whiuch is exported by another database software. As the file exported the worksheet formatted as below format:

AxxxxxxxxxxxxxxBxxxxxxC
1 TablexxxxxxxxSKxxxxxdetails
2 -------xxxxxxx---xxxx-------
3 ACB554xxxxxxxxxxxxxxsome data
4 xxxxxxxxxxxxxxxxxxxxxsome data
5 xxxxxxxxxxxxxxxxxxxxxsome data
6 xxxxxxxxxxxxxxxxxxxxxsome data
7 -------xxxxxxx---xxxx---------

PLEASE CONSIDER "x" AS SPACE

As you can see above; there are some space between row number 3 and 7. However that is not fixed space, I mean somewhere in the same column there are four or five rows(in column A) blank, that is because of the details in column C. This exported data is very long, can say like 50,000 rows. Here is my problem:
I would like to merge the all cell between "-----" lines. But I am not sure how to do and secondly how to manage to do it on coulumn C same look but in one cell.
Thank you in advance if you can provide me any help.
Baha

 

[Robert Mika]

Hi Everyone,

I cannot upload my worksheet because of restrictions of my company. I have a worksheet whiuch is exported by another database software. As the file exported the worksheet formatted as below format:

AxxxxxxxxxxxxxxBxxxxxxC
1 TablexxxxxxxxSKxxxxxdetails
2 -------xxxxxxx---xxxx-------
3 ACB554xxxxxxxxxxxxxxsome data
4 xxxxxxxxxxxxxxxxxxxxxsome data
5 xxxxxxxxxxxxxxxxxxxxxsome data
6 xxxxxxxxxxxxxxxxxxxxxsome data
7 -------xxxxxxx---xxxx---------

PLEASE CONSIDER "x" AS SPACE

As you can see above; there are some space between row number 3 and 7. However that is not fixed space, I mean somewhere in the same column there are four or five rows(in column A) blank, that is because of the details in column C. This exported data is very long, can say like 50,000 rows. Here is my problem:
I would like to merge the all cell between "-----" lines. But I am not sure how to do and secondly how to manage to do it on coulumn C same look but in one cell.
Thank you in advance if you can provide me any help.
Baha

Give ago with this:
Add this UDF to your workbook:
XL-CENTRAL.COM : VBA : Concatenate the Data in a Range
The use:

Excel 2010
	A	B	C	D	E
1	Table	Sk	Details
2	-----	xxx	------
3	ABC		1		12345
4			2
5			3
6			4
7			5
8	-----	xxx	------
9	bbbb		2		245532124
10			4
11			5
12			5
13			3
14			2
15			1
16			2
17			4
18	-----	xxx	------
Sheet1

Cell Formulas
Range	Formula
E3	{=IF(AND(LEFT(A3,1)<>"-",A3<>""),SUBSTITUTE(AConcat(IF(OFFSET(C3,0,0,MATCH(TRUE,INDEX(B3:B500="xxx",0),0)-1,)<>"",""&OFFSET(C3,0,0,MATCH(TRUE,INDEX(B3:B500="xxx",0),0)-1,),"")),",","",1),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[baha17]

Hi Robert,
Thank you for your reply.I am sorry to mislead you but there is no "x",that one is only for space and 1,2,3,4... represents row numbers.So literally range("A2") :----- ; Range("A3"):"dats" and range("A4:A6"):"" then Range("A7"):------ I want to merge the cells between ------ lines.
Again thank you very much for the help
Bahs

 

[Robert Mika]

Hi Robert,
Thank you for your reply.I am sorry to mislead you but there is no "x",that one is only for space and 1,2,3,4... represents row numbers.So literally range("A2") :----- ; Range("A3"):"dats" and range("A4:A6"):"" then Range("A7"):------ I want to merge the cells between ------ lines.
Again thank you very much for the help
Bahs

From the Example I have posted what will be your final answer?
ABCbbb or as posted or all data 12345245532124.
My number values represent actuall values not row numbers.
Sorry if that confused you.

 

[baha17]

Thanks Robert, with a little bit of adjustment it worked for me.Thank you..
Baha

 

[Robert Mika]

Thanks Robert, with a little bit of adjustment it worked for me.Thank you..
Baha

You are welcome.