Tidying up data in Excel...

[raston_89]

Hi,

I have a complex problem. I have a heap of data in excel that has a lot of crap in between, below is an example. It lists a Job Number (i.e A1), then a heap of crap I don't care about until it gets to the data blocks that each have the heading "REPORT" with a few rows of data for that particular job number below them. After a few REPORT blocks (which all have a one row space between them), there is some more crap, then the next Job number (A29), and the series repeats.

I want to run a formula down column B that looks to the cell on its left (in column A) and returns the values in that cell only if it is the Job number, the heading REPORT or the important data underneath Report. Below is an example of what the data looks like, and then what I want it to look like.

I have tried to think up of a way but am just getting stuck... Any help would be great!!



WHAT DATA LOOKS LIKE:

	a
1	Job 1000
2	Irrelevant Data
3	Irrelevant Data
4
5	REPORT
6	Important Data
7	Important Data
8	Important Data
9
10	REPORT
11	Important Data
12	Important Data
13	Important Data
14	Important Data
15	Important Data
16
17	REPORT
18	Important Data
19	Important Data
20	Important Data
21	Important Data
22
23	Irrelevant Data
24	Irrelevant Data
25	Irrelevant Data
26
27	Irrelevant Data
28
29	Job 1001
30	Irrelevant Data
31	Irrelevant Data
32
33	REPORT
34	Important Data
35	Important Data
36	Important Data
37	Important Data
38
39	Job 1002
40	Irrelevant Data
41	Irrelevant Data
42
43	REPORT
44	Important Data
45	Important Data
46	Important Data

<colgroup><col><col></colgroup><tbody>
</tbody>

WHAT I WANT DATA TO LOOK LIKE:

	a	b
1	Job 1000	Job 1000
2	Irrelevant Data
3	Irrelevant Data
4
5	REPORT	REPORT
6	Important Data	Important Data
7	Important Data	Important Data
8	Important Data	Important Data
9
10	REPORT	REPORT
11	Important Data	Important Data
12	Important Data	Important Data
13	Important Data	Important Data
14	Important Data	Important Data
15	Important Data	Important Data
16
17	REPORT	REPORT
18	Important Data	Important Data
19	Important Data	Important Data
20	Important Data	Important Data
21	Important Data	Important Data
22
23	Irrelevant Data
24	Irrelevant Data
25	Irrelevant Data
26
27	Irrelevant Data
28
29	Job 1001	Job 1001
30	Irrelevant Data
31	Irrelevant Data
32
33	REPORT	REPORT
34	Important Data	Important Data
35	Important Data	Important Data
36	Important Data	Important Data
37	Important Data	Important Data
38
39	Job 1002	Job 1002
40	Irrelevant Data
41	Irrelevant Data
42
43	REPORT	REPORT
44	Important Data	Important Data
45	Important Data	Important Data
46	Important Data	Important Data

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[RonB1111]

Start your data in row 2 instead of row 1, and enter this formula in cell B2 and copy down:
=IF(OR(LEFT(A2,3)="Job",A2="REPORT"),A2,IF(OR(LEFT(B1,3)="Job",B1="",A2=""),"",A2))