Combine cells conditionally on the way down when cell content changes

[inosent]

The best way to describe this is by an example:

Row A

1- O
2- 1
3- 2
4- 3
5- R
6- 1
7- 2
8- B
9- 5
10 -6

What I am looking for is a formula I can fill down in Row B where the result will look like this:

O
O - 1
O - 2
O - 3
R
R - 1
R - 2
B
B - 5
B - 6

So when there is a letter in A1, as long as the next row does not contains that letter, then the next row will combine A1 with A2 in B2; A1 with A3 in B3; A1 with A4 in B4; but since A5 has a new letter, B5 will equal A5, then after, A5 with A6 in B6; A5 with A7 in B7 and so on

 

[JoeMo]

Copy the formula in B1 down to the end of col A data.

Excel Workbook
	A	B
1	O
2	1	1
3	2	2
4	3	3
5	R
6	1	1
7	2	2
8	B
9	5	5
10	6	6
Sheet4

 

[inosent]

Thanks, but that is not what I am looking for. Maybe my example is not good enough.

Let's try this

Col A -------------------B
R1 Abstract
R2 --------------------fun
R3 ---------------------43yrt
R4 Health
R5 ----------------------early
R6 ----------------------1

So as long as there are no new values in A:

C1 = "Abstract"
C2 = "Abstract - Fun"
C3 = "Abstract - 43yrt"
C4 = "Health"
C5 = "Health - early"
C6 = "Health - 1"

What I want is for col A to act as the main category, and the values in col b to be the sub-values, and when the value in Col A changes, that becomes the new main category, and the values in Col B become the associated sub-values for the new main category.

And, so, in my new example, every time the value in A changes (with blank cells in between the change of values in Col A, doesn't matter if they are text or numbers), the formula in Col C (in htis new example) will change with it, and associate that value with the subsequent rows in Col B, until the value in A changes again.

Copy the formula in B1 down to the end of col A data.

Excel Workbook
	A	B
1	O
2	1	1
3	2	2
4	3	3
5	R
6	1	1
7	2	2
8	B
9	5	5
10	6	6
Sheet4

 

[Aladin Akyurek]

Col A or col R?

 

[inosent]

Since I dont know how to post a spreadsheet, the "R" is supposed to mean "Row"

My example shows Col A and B going horizontally to the right, and "R"ows going down vertically 1-6.

Thanks for the reply.

Col A or col R?

 

[Aladin Akyurek]

Row\Col	A​	B​	C​
1​	Abstract		Abstract
2​		fun	Abstract - Fun
3​		43yrt	Abstract - 43yrt
4​	Health		Health
5​		early	Health - early
6​		1​	Health - 1

In C1 enter and copy down:

=LOOKUP(REPT("z",255),$A$1:A1)&IF($B1="",""," - "&$B1)