How to identify first occurrence of a value above a target cell and report value in that cell

[ripvw]

This is how I get the data				This is what I need to convert it to, through some formula
		District 15
		Location 25
		a		District 15	Location 25	a
		b		District 15	Location 25	b
		c		District 15	Location 25	c
		d		District 15	Location 25	d
		e		District 15	Location 25	d
		f		District 15	Location 25	d
		g		District 15	Location 25	d
		location 26
		aa		District 15	Location 26	aa
		bb		District 15	Location 26	bb
		cc		District 15	Location 26	cc
		dd		District 15	Location 26	dd
		Location 33
		a1		District 15	Location 33	a1
		b1		District 15	Location 33	b1
		c1		District 15	Location 33	c1
		d1		District 15	Location 33	d1
		e1		District 15	Location 33	e1
		District 16
		Location 54
		aa1		District 16	Location 54	aa1
		bb1		District 16	Location 54	bb1
		cc1		District 16	Location 54	cc1
		dd1		District 16	Location 54	dd1
		ee1		District 16	Location 54	ee1
		ff1		District 16	Location 54	ff1
		gg1		District 16	Location 54	gg1

 

[Domenic]

Try the following...

ripvw.xlsm
	A	B	C	D	E	F	G
1
2
3
4			District 15
5			Location 25
6			a		District 15	Location 25	a
7			b		District 15	Location 25	b
8			c		District 15	Location 25	c
9			d		District 15	Location 25	d
10			e		District 15	Location 25	e
11			f		District 15	Location 25	f
12			g		District 15	Location 25	g
13			location 26
14			aa		District 15	location 26	aa
15			bb		District 15	location 26	bb
16			cc		District 15	location 26	cc
17			dd		District 15	location 26	dd
18			Location 33
19			a1		District 15	Location 33	a1
20			b1		District 15	Location 33	b1
21			c1		District 15	Location 33	c1
22			d1		District 15	Location 33	d1
23			e1		District 15	Location 33	e1
24			District 16
25			Location 54
26			aa1		District 16	Location 54	aa1
27			bb1		District 16	Location 54	bb1
28			cc1		District 16	Location 54	cc1
29			dd1		District 16	Location 54	dd1
30			ee1		District 16	Location 54	ee1
31			ff1		District 16	Location 54	ff1
32			gg1		District 16	Location 54	gg1
Sheet1

Cell Formulas
Range	Formula
E6:E32	E6	=IF(LEFT(C6,8)<>"District",IF(LEFT(C6,8)<>"Location",LOOKUP(2,1/(LEFT($C$4:C6,8)="District"),$C$4:C6),""),"")
F6:F32	F6	=IF(LEFT(C6,8)<>"District",IF(LEFT(C6,8)<>"Location",LOOKUP(2,1/(LEFT($C$4:C6,8)="Location"),$C$4:C6),""),"")
G6:G32	G6	=IF(LEFT(C6,8)<>"District",IF(LEFT(C6,8)<>"Location",C6,""),"")

Hope this helps!

 

[ripvw]

You are awesomeness personified, thank you so much.

 

[Domenic]

You're very welcome, I"m glad I could help.

Cheers!

 

[smozgur]

You are awesomeness personified, thank you so much.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.