Is there away around an imported reports funky layout?

[Eldraad]

What I have happening is this: I am converting a text report to a
excel worksheet (done automatically,). The report USUALLY follows th
same pattern....

78..06..texttexttext.....0.....0.....0
............Sample.............0.....0....0

(if this looks bad it should have 78 06 in the first column
texttexttext in the next column and numbers in several columns afte
that. The row below it should have the first column blank, the
sample lined up under the texttexttext and several columns of number
after that).

BUT, due to the way this report is created at the source (it i
e-mailed to us) we sometimes get the two rows split with other dat
from a page break or header or something, and it looks like this:

78..06..texttexttext.....0.....0.....0
...Statistical Report for 07/28/2004....text.....text......text.....
text......text.....text......text......text.....text....text......text.....
............Sample.............0.....0.....0

What I have been doing prior to finding this error, was running
formula to find the "78 06" then counting 1 row down and 2 columns t
the right to get the data for that dispatch number after the wor
sample. When the extra text shows up, the formula returns the portio
of the text (from these extra rows) that shows up in the expected dat
cell. The formula is as follows"

=offset(a1, Match("78 06", A2:A1900, 0)+1,2)

These extra rows of text can show up at any dispatch number (the "78
06" is a dispatch number and we have hundreds of them)and I would lik
to know how to set up the formula to "skip" the extra text and find th
"Sample" row so I can get the correct data.

In other words, if there are no "extra rows" it will find Sample as i
the formula shown. But if there ARE extra rows it needs to look pas
them to find "Sample" and then the column with the correct data.

Is there a way to do this?

Thank you for your time in reading this message.

Eldraa

 

[Dave Peterson]

"Sample" is really a value that shows up in column B of the row to be kept????
It's not just an example in your post????

If that's true, maybe something like this will work for you.

I'd insert a new column A (to use an indicator to keep or delete that row).

=if(or(b1="78 06",c1="sample),"Keep","delete")

Now apply data|filter|autofilter and show the Delete rows.

And delete them.

And delete that helper column A.

=====
Or do you need to keep that extra inserted data???

 

[Eldraad]

Dave Peterson, Thank you for your reply!

The 78 06 always shows up in column A

The text "Sample" always shows up in column B

By the way, the 78 06 is one of several hundred Dispatch numbers tha
are in the A column. I have to locate about 90 different dispatc
numbers and on some of then, locate their related "Sample" figure
(usually two columns to the right of the word Sample.

The report varys in length (or the total amount of rows) as some day
we have more dispatches than others. So I use the dispatch number t
locate the correct route and then the dispatchs Sample amount jus
below.

I am new at this and my math stinks after all these years (come t
think of it I NEVER did well in that area) so i am not quite sure wha
the formula is saying...

I gather it is something like: if cell b1 = "78 06" and c1 = "Sample
then keep the row, if not delete the row

If I am close to what it says it WOULD work except B1 and C1 would be
variable...I would never know exactly what row number in the b colum
"78 06" would be in.

Would it be possible to Filter out the Header that is in the way of m
data? Is there a way to tell the worksheet to delete or hide specifi
rows based on what is in the first cell of that row?

the first cell in the first Header row always contains:

LABR81000 V.026
DATE CREATED (always shows in the row right below it)

Of course the "78 06" example is above the 1st header row
and "sample" is below the second header row.

Could that stuff be filtered out or hidden so the two rows containin
my data are one on top of the other again?

Whew...gotta sleep now...getting cross-eyed

 

[Dave Peterson]

One version of this text is always in the row to delete
either: LABR81000 V.026
or: DATE CREATED

Change that keep/delete formula to:

=IF(OR(LEFT(B1,15)="labr81000 v.026",
LEFT(B1,12)="date created"),"delete","keep")
(all one cell)

And apply the filter and do the rest of the work.

if those strings could occur anywhere in the cell:

=IF((COUNTIF(B1,"*labr81000 v.026*")
+COUNTIF(B1,"*date created*"))>0,"delete","keep")
(still all one cell)