what is best way to do this?

[petethecat]

I want to interrogate some data that is only available in a certain format & can change every day. The objective is to extract the data under the "header" so that ultimately i can use a VLOOKUP to get what i want from the data.

This is how it looks at present:

		0	0	0	Leg 4	0	0	0
		T2001A	0	2500	SF632V(1)	MB	7604	1013904
		T2002A	0	2500	SF632V(1)	MB	7604	1013901
		T2003A	0	1200	SF632V(1)	MB	7604	1013905
		T2004A	0	2500	SF632V(1)	MB	7604	1013630
		T2006A	0	1200	SF632V(2)	MB	7604	1013902
		T2007A	0	1200	SF632V(2)	MB	7604	1013629
		T2008A	0	3600	0	0	0	0
		0	0	0	0	0	0	0
		T2044	0	0	SF632V(2)	MB	7604	1013625
		0	0	0	Leg 5	0	0	0
		0	0	0	0	0	0	0
		T2002B	0	0	DF770E(1)	VS	6233	1013809
		T202	0	0	DF770E(2)	VS	6233	1013810
		A300	0	0	DF578E(1)	JL	5976	1013805
		0	0	0	DF578E(2)	JL	5976	1013806
		0	0	0	DF770E(1)	VS	6233	1014077
		0	0	0	DF770E(2)	VS	6233	1014078
		0	0	0	Leg 7	0	0	0
		T880	0	0	SF630VCM(1)	VW	6380	1013819
		T881	0	0	SF630VCM(1)	VW	6381	1013817
		T882	0	0	0	0	0	0
		T883	0	0	SF630VCM(2)	VW	6381	1013818
		T884	0	0	SF630VCM(2)	VW	6380	1013820
		T885	0	0	SF630VCM(2)	VW	6381	1013816
		T886	0	0	DW2114LB(1)	LB	6133	1014105

<colgroup><col span="5"><col><col span="3"></colgroup><tbody>
</tbody>

What i want to do is have a new column that identifies the area (LEG4, LEG5 LEG7 In bold/italics) and what ever is under that is called the heading. IE in a column next to T2001 I would want to have "LEG 4" shown. Same in T2002 Etc until we get to LEG5 where i would want to show LEG5 against T2002B & so on all the way down.

I have attempted If/AND but i come unstuck when we get further down the list as the statement does not cover all the scenarios.

This must be a simple thing to rectify but i cant for the life in me think what it is!

Any help is greatly appreciated.

 

[RasGhul]

Does this work for what you need?

Book1
	A	B	C	D	E	F	G
1		Leg 4		Leg 4
2	T2001A	Leg 4	2500	SF632V(1)	MB	7604	1013904
3	T2002A	Leg 4	2500	SF632V(1)	MB	7604	1013901
4	T2003A	Leg 4	1200	SF632V(1)	MB	7604	1013905
5	T2004A	Leg 4	2500	SF632V(1)	MB	7604	1013630
6	T2006A	Leg 4	1200	SF632V(2)	MB	7604	1013902
7	T2007A	Leg 4	1200	SF632V(2)	MB	7604	1013629
8	T2008A	Leg 4	3600
9		Leg 4
10	T2044	Leg 4		SF632V(2)	MB	7604	1013625
11		Leg 5		Leg 5
12		Leg 5
13	T2002B	Leg 5		DF770E(1)	VS	6233	1013809
14	T202	Leg 5		DF770E(2)	VS	6233	1013810
15	A300	Leg 5		DF578E(1)	JL	5976	1013805
16		Leg 5		DF578E(2)	JL	5976	1013806
17		Leg 5		DF770E(1)	VS	6233	1014077
18		Leg 5		DF770E(2)	VS	6233	1014078
19		Leg 7		Leg 7
20	T880	Leg 7		SF630VCM(1)	VW	6380	1013819
21	T881	Leg 7		SF630VCM(1)	VW	6381	1013817
22	T882	Leg 7
23	T883	Leg 7		SF630VCM(2)	VW	6381	1013818
24	T884	Leg 7		SF630VCM(2)	VW	6380	1013820
25	T885	Leg 7		SF630VCM(2)	VW	6381	1013816
26	T886	Leg 7		DW2114LB(1)	LB	6133	1014105
Sheet1

Cell Formulas
Range	Formula
B1	=IF(ISNUMBER(SEARCH("Leg",D1)),D1,OFFSET(D1,-1,-2))

 

[petethecat]

yep - Brilliant! Much appreciate your time..........

 

[petethecat]

one question though. What would i need to change if i wanted to shift the column across to the left (2 columns - so a new column A) Where the data is shown in the example i cant use that column - Col B. I would need to insert a column, move everything over by one & use a new column A

 

[RasGhul]

Assuming that you insert a new column as A and your heading column now becomes Column E - Enter into Cell A1;


=IF(ISNUMBER(SEARCH("Leg",E1)),E1,OFFSET(E1,-1,-4))