Column contents moved depedning on number of blank cells

[Richiejjj]

HI

I have a worksheet in A1:C726.

The C column looks at the first four letters of the equivalent A column and returns "heading" if the A cell starts with "page" and if not the c column is blank.

So A2 has the words "page 6: commented" so c6 has "heading"
A3 has "are we missing" so c6 is blank.

The A3 cell is the content that needs to be next to the a2 heading. The content cells maybe 1-6 rows, before you get to a new heading.

I need to return to column D, next to the "heading" column a concatenate of the cells from the blank column C.

Any ideas?

 

[jmacleary]

Can you post a sample of what the data actually looks like please - I am having trouble visualising the requirement.

 

[Richiejjj]

Thanks for asking

A:B:C:
1 Page 6- :
2 The quick Brown Fox:
3 Jumped Over:
4 Page 7-:
5 Somewhere over
6 the rainbow:
7 Page 7-:
8 Hello

All the data above is in column A. I basically want :

Page 6-: The quick Brown Fox Jumped Over
Page 7-: Somewhere over the rainbow
Page 7-: Hello

So the data below a Page xxx is inserted/concatenated next the page number heading.

 

[Peter_SSs]

1. Are you looking for a formula solution or macro?

2. What version of Excel are you using?

 

[Richiejjj]

Formula please, I am using office 365 proplus. I tired to do it using get/transform but failed...

 

[Peter_SSs]

Formula please, I am using office 365 proplus.

I'm not sure of the exact layout required, but see if something like this heads you in the right direction.

Excel Workbook
	A	B
1	Page 6- :	The quick Brown Fox Jumped Over
2	The quick Brown Fox:
3	Jumped Over:
4	Page 7-:	Somewhere over the rainbow
5	Somewhere over
6	the rainbow:
7	Page 7-:	Hello
8	Hello
9
TEXTJOIN 2

 

[Richiejjj]

Peter that is brilliant thank you so much.

 

[Peter_SSs]

Peter that is brilliant thank you so much.

You're welcome. Glad it is what you wanted.