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:
[TABLE="width: 600"]
<colgroup><col span="5"><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Leg 4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T2001A[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2500[/TD]
[TD]SF632V(1)[/TD]
[TD]MB[/TD]
[TD="align: right"]7604[/TD]
[TD="align: right"]1013904[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T2002A[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2500[/TD]
[TD]SF632V(1)[/TD]
[TD]MB[/TD]
[TD="align: right"]7604[/TD]
[TD="align: right"]1013901[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T2003A[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1200[/TD]
[TD]SF632V(1)[/TD]
[TD]MB[/TD]
[TD="align: right"]7604[/TD]
[TD="align: right"]1013905[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T2004A[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2500[/TD]
[TD]SF632V(1)[/TD]
[TD]MB[/TD]
[TD="align: right"]7604[/TD]
[TD="align: right"]1013630[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T2006A[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1200[/TD]
[TD]SF632V(2)[/TD]
[TD]MB[/TD]
[TD="align: right"]7604[/TD]
[TD="align: right"]1013902[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T2007A[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1200[/TD]
[TD]SF632V(2)[/TD]
[TD]MB[/TD]
[TD="align: right"]7604[/TD]
[TD="align: right"]1013629[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T2008A[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3600[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T2044[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]SF632V(2)[/TD]
[TD]MB[/TD]
[TD="align: right"]7604[/TD]
[TD="align: right"]1013625[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Leg 5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T2002B[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]DF770E(1)[/TD]
[TD]VS[/TD]
[TD="align: right"]6233[/TD]
[TD="align: right"]1013809[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T202[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]DF770E(2)[/TD]
[TD]VS[/TD]
[TD="align: right"]6233[/TD]
[TD="align: right"]1013810[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]A300[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]DF578E(1)[/TD]
[TD]JL[/TD]
[TD="align: right"]5976[/TD]
[TD="align: right"]1013805[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]DF578E(2)[/TD]
[TD]JL[/TD]
[TD="align: right"]5976[/TD]
[TD="align: right"]1013806[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]DF770E(1)[/TD]
[TD]VS[/TD]
[TD="align: right"]6233[/TD]
[TD="align: right"]1014077[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]DF770E(2)[/TD]
[TD]VS[/TD]
[TD="align: right"]6233[/TD]
[TD="align: right"]1014078[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Leg 7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T880[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]SF630VCM(1)[/TD]
[TD]VW[/TD]
[TD="align: right"]6380[/TD]
[TD="align: right"]1013819[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T881[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]SF630VCM(1)[/TD]
[TD]VW[/TD]
[TD="align: right"]6381[/TD]
[TD="align: right"]1013817[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T882[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T883[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]SF630VCM(2)[/TD]
[TD]VW[/TD]
[TD="align: right"]6381[/TD]
[TD="align: right"]1013818[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T884[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]SF630VCM(2)[/TD]
[TD]VW[/TD]
[TD="align: right"]6380[/TD]
[TD="align: right"]1013820[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T885[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]SF630VCM(2)[/TD]
[TD]VW[/TD]
[TD="align: right"]6381[/TD]
[TD="align: right"]1013816[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T886[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]DW2114LB(1)[/TD]
[TD]LB[/TD]
[TD="align: right"]6133[/TD]
[TD="align: right"]1014105[/TD]
[/TR]
</tbody>[/TABLE]

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))