A question about converting text to excel and automatically searching data

[Eldraad]

New to this so please forgive me...

I have been transfered to a new department at work where they are usin
Excel 2000 for a series of reports. The data they are using to fill i
the fields for these reports are hard copies of 5 thick reports. Ever
day they are spending 2 hours digging through 5 stacks of reports t
fill in fields in excel when I am POSITIVE that it can be don
electronically. What they are doing now...frightens me....

The formats of each of the records are somewhat different but they al
fall into a type where you have columms seperated by spaces...

Dispatch Flight so-and-so Haller Name Packages
0069 231 to Texas Langos Exp. 32 and 1
0173 Dallas GND Richards LH 27 an
3

Something like the above anyway. The files used for the hard copie
are text files that we can e-mail to the computer that has the repor
form (big system to desktop). I suspect there is a way to convert eac
of the 5 reports into something that Excel can read and pull dat
from...such as the 0069 and the 32...1.

Ideally, We would convert the 5 files each day to something excel coul
scan for data with as little user intervention as possible. Perhap
something that would do a batch conversion of the text files and the
an automatic search for data which would then fill in the proper cell
in the excel worksheets. The Text conversion wizard is nice and all
but I seem to have to format the columms etc each time I convert th
same report and it is not automatic.

It is very late for me and I am sorry if I did not state this in
clear enough manner. I would be greatful for any help on this matte
be it step by step or "Hey, go to this link". I did do a few searche
for my subject on the web and here but the answers were not quite wha
I was looking for.

Thank you

 

[Mark Rosenkrantz]

Eldraad;

I don't know if I understood you exactly but do you have the pile of reports
on a disk or floppy or on a computer ?
If so, if that has the form of a textfile you can easily have excel import
it and seperate it into columns.

When you open the textfile in excel a dialogbox is shown.
It is somekind of wizard that leads you through a proces of seperating data
into a number of columns on the worksheet.

See if this might be a solution.

Mark.

 

[Trevor]

Let me see if I understand.

Each day, 5 text files are created which presumably, contain information
about the day's activities. At the end of the day, people in your
department fill out an Excel spreadsheet using information contained in
those 5 files. In order to find the information they need, they print the 5
reports, and manually search through the printouts in order to complete
their spreadsheets.

It sounds like you have 2 problems. First, the 5 text files are in a bit of
an odd format, containing things like the word "and" which is good for
reading the document, but not so good if you open the text file in Excel
using the Text Conversion Wizard, because there's more manual effort.
Second, you're wondering if, with the data in a nice format, there are ways
to make your daily summary spreadsheet so that it can automatically do the
"lookups" that are currently done manually.

There are solutions to both problems, I believe. First, some programming
could be done to create a routine which could reformat the data into a nice
series of columns with only the information that you need. This could be
anything from a batch program to an Excel macro to a Microsoft Access
program which loads the files into a little database. Then, your manual
lookup activities could probably be automated as well. Whether that would
be done through the use of a formula or Excel macros would depend on exactly
what you're trying to do.

Again, not knowing exactly what you're doing, it's hard to say, but I also
wonder if a reporting tool such as Crystal Reports might actually create
your spreadsheets for you without ANY manual intervention? That's possible
if you really are producing some sort of summary report.

So there are a number of options out there. I think the kind of automation
you're looking for involves some programming. This is a shameless sales
plug, but if you think you'd like the services of an experienced consultant,
please feel free to contact me. As I do in this forum, I'd be happy to give
you free advice wherever I can, too.

Trevor Sides
(e-mail address removed)
860-518-3998
www.intelligentsystemsconsulting.com

 

[Eldraad]

Mark, thanks for your reply. The reports we are using are printouts bu
we CAN get the electronic files that were used to make the print outs.
The text wizard has a few fewer features than what we really nee
though, I think.

Trevor, you have the correct idea. This misbegotten example I used wa
close to the mark. There are columns and rows in the reports. Th
text usually gives names or flight information or trucking information
Usually, the text is in a column of a specific width...

Riverside District.........................2904 1 of 17 53
Devore School District.................1802 7 of 20 97

If the format looks strange here just put each number in its own colum
and the text in columns as well.

(Edit) I mean line up the numbers into columns and the text int
columns.

These 5 reports CAN be e-mailed as text. The text wizard needs som
help to line things up and must be redone each time a file is read.
have imported one of the reports using the wizard and it does work (bu
not automated).

A macro could be used to format the text file and create an exce
worksheet. In fact, one of the other departments HAD a macro set up t
open one of the text reports, create a worksheet in excel and align th
data into columns and rows. I do not know if they had any way o
searching the data though.

I would like to tell it to create the worksheet from a text fil
(always the same worksheet for the SAME report) search for a certai
data (such as the 1802 used above) which will ALWAYS be in the sam
column but NOT the same row, and using that bit of information, once i
finds the right spot, move over 8 columns and get the data it find
there, move over 3 more columns and down 1 row to get the data it find
there AND pop it into the main report automatically. As you can see,
am not asking for much...LOL!

I gather it would be something along the lines of a macro to conver
the text files into individule worksheets with the data aligned column
and rows. A second function or macro would then check each report fo
identifying data "markers" and, once at these "markers", move over t
specific cells and grab the data. Then the data would be acted on by
formula if needed and placed in the proper cell of the main repor
worksheet. So a conversion takes place first. Then a search fo
specific data. And, finally, a population of a specific cell in th
main report.

Or did I just confuse everyone and myself again? The big question i
how to do this...good helpful links etc. My experience with excel s
far has been a place to put my TimeSheet information so I can get paid
Inother words paint a big NOOB on my forehead! If you tell me wha
parts I need to do I can try to find the information myself, or i
there is something out there to do this already...Please share th
wealth! LOL. Crystal Reports?? I will search for information on tha
as well

 

[Trevor]

Eldraad,

1) Creating a worksheet from a text file. Very feasible. There are 2 ways
to do this:
a) write a macro (this you could do yourself, with the right skills)
b) write a little program (you'd hire a developer to do this)
Writing the macro might be a little bit tricky, though, depending on how
much help you give the Text Wizard when you do it manually. But, any help
that you DO give it, should be replaceable by macro logic. If you create a
macro which reads in the file but run into specific problems with getting
the data to come out in the right columns, then you can always post to the
microsoft.public.excel.programming group and someone will surely give you
the code. Just be specific about what the data looks like and what you're
trying to do.

2) Creating your report. Very feasable AND easy once you have your
worksheet above. If you have one cell where the user enters 1802, as in
your example below, then, then you can use the VLOOKUP or MATCH functions to
find the 1802 row in your raw data, and then you can use the OFFSET or
INDIRECT functions to find the informatino 8 columns to the right or 11
columns to the right plus 1 row down. Again, posting the exact format of
your raw data sheet, resulting from 1) above, will get you the exact formula
you'll need. Here's an example:

Sheet1:
A1: 1802
B1: =INDIRECT("Sheet2!I"&MATCH(A1,Sheet2!A:A,0)) - which will resolve to
"some data"
C1: =INDIRECT("Sheet2!L"&MATCH(A1,Sheet2!A:A,0)+1) - which will resolve to
"some more data"

if Sheet 2:
A65: 1802
I65: some data
L66: some more data

Let us know how you're coming along with all this....

 

[Eldraad]

Actually what I have tried doing was recording a macro that takes th
text document and converts it to a worksheet. I have then used
formula I found in the HELP file to search for a certain string of tex
and then count over so many columns to retrieve the information that
needed.

the formula was something like:

=offset(a1,match("secondary total for dispatch 0056", c2:c1024, 0),5)

I needed to find the secondary dispatch number and then the amount tha
was several columns to the right. This worked quite well.

But one of the reports does not follow the same layout. What I woul
like to know is if there is a way to search for the "secondary dispatc
0056" string and then look one row BELOW that and one column to th
LEFT to get the data I need?

My system has been down for several days so I just got to experimentin
with formulas last night.

Thanks for your time in reading this message..

 

[Trevor]

oh sure. If the formula you're using is working well, then just add 1 to
the row parameter of your offset:

=offset(a1,match("secondary total for dispatch 0056", c2:c1024, 0)+1,5)

 

[Eldraad]

Thank you Trevor!

I had just started changing the section at the end of the formula whe
I got home. I was receiving an error when I changed the end to )1,5
the addition of the + or - gets it working. The odd thing was when
had ),5,5) it also looked one row down which had me stumped (and of
track).

Again, thank you. I should be able to get all the information for th
reports put together as soon as I can fix my computer. Fres
install...no virus or trojans. Constant fails on just about everthing
Including my experiments with excel. Lol. oh well..