formula help

[lirkus84]

Hello I need some help with creating a formula for the below table. What I am trying to achieve is in the "required results" column A. The "Name" entries will always have "Name" preceding

required results
	Name Jimmy
Name Jimmy -shoes	-shoes
	Name Tom
Name Tom-Balls	-Balls
Name Tom-nets	-nets
Name Tom-snacks	-snacks

<tbody>
</tbody>

 

[Joe4]

Let's say that your data is in the range B1:B6.
Then enter this formula in cell A2 and copy down for all rows:

=IF(LEFT(B2,4)="Name","",IF(LEFT(B1,4)="Name",B1 & B2,LEFT(A1,LEN(A1)-LEN(B1)) & B2))

 

[Eric W]

Same general idea as Joe4:

	B	C	D	E
1	List	Helper	Required Results
2	Name Jimmy		Name Jimmy-shoes
3	-shoes	Name Jimmy-shoes	Name Tom-Balls
4	Name Tom		Name Tom-nets
5	-Balls	Name Tom-Balls	Name Tom-snacks
6	-nets	Name Tom-nets	Name Mary-socks
7	-snacks	Name Tom-snacks	Name Mary-shin guards
8	Name Mary		Name Alice-chalk
9	-socks	Name Mary-socks
10	-shin guards	Name Mary-shin guards
11	Name Alice
12	-chalk	Name Alice-chalk
13

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula C2 =IF(LEFT(B2,4)="Name","",LOOKUP(2,1/ISERROR(FIND("-",$B$1:$B1)),$B$1:$B1)&B2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D2 {=IFERROR(INDEX($C$2:$C$20,SMALL(IF($C$2:$C$20<>"",ROW($C$2:$C$20)-ROW($C$2)+1),ROWS($D$2:$D2))),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

I added an array formula in D to remove the spaces if you want.