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]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td]Abstract[/td][td][/td][td] Abstract[/td][/tr]
[tr][td]

2​

[/td][td][/td][td]fun[/td][td] Abstract - Fun[/td][/tr]
[tr][td]

3​

[/td][td][/td][td]43yrt[/td][td] Abstract - 43yrt[/td][/tr]
[tr][td]

4​

[/td][td]Health[/td][td][/td][td] Health[/td][/tr]
[tr][td]

5​

[/td][td][/td][td]early[/td][td] Health - early[/td][/tr]
[tr][td]

6​

[/td][td][/td][td]

1​

[/td][td] Health - 1[/td][/tr]
[/table]

In C1 enter and copy down:

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