Heres one for you Excel boffins;

I have a data extract in the following format,

Column
S5
Input S5
Input Total:
Tax Type: S5
Z1
Input
Input Total:
Tax Type: Z1
Cost Centre: 002402

E1
Input
Input Total:
Tax Type: E1
S1
Input
Input Total:
Tax Type: S1
S5
Input
Input Total:
Tax Type: S5
Z1
Input
Input Total:
Tax Type: Z1
Cost Centre: 002521

What I need to do is, whenever "S5" comes up, I need to extract the relevant cost centre (which is always shown at the end of the 'block'). The above is just a small amount of data, where S5 appears in the rows, thousands of times in this format.

Now the above is 9 rows, but this is not always the case.

How do I extract the Cost Centres when S5 is shown? Or how do I group the blocks together into say an index or label, to use a formula on that to extract the cost centres?

 

Easiest to write a macro to work down the column. ISBLANK function should prove useful

flag = 0
Start in A1
DO
if cell contents = S5 flag = 1
next row
if flag = 1 and isblank(row+1)
if cell contents = "cost centre ..." (check maybe not essential)
write cost centre code to another sheet

else LOOP back

flag =0
row = row + 2

etc.

 

Yeh cheers mate, I was trying to avoid having to use a macro thou, as I was wondering if there was a function I was missing out on to group rows together somehow.

 

Are you saying that S5 appears in every block?

Be creative.

You might be able to put in an index column a and put a formula like
=if(B1="S5",1,if(left(b1,4)="cost",1,0))
This will put a 1 in column a where column b contains either "S5" or "cost......"
Then copy the whole shooting match to a new sheet, paste as values, sort on column a, and you will have all the S5's followed by their cost codes.
It will also include all the other cost codes where s5 doesn't occur.

you could write another formula into the new sheet saying
in cell a2
=if(B1="S5",1,0)
This will put a 1 in column a where the previous row of column b contains "S5", i.e. flagging the required cost code rows.
Then copy the whole shooting match to a new sheet, paste as values, sort on column a, and you will have all the S5 cost codes listed.

HTH

 

Thats it cheers I just had to filter on the resulting 1 in column A from the 1st part, before I copied / pasted it, then it was all in the correct order.