Re: A question about converting text to excel and automatically searching data

From: Trevor (tsides_at_intelligentsystemsconsulting.com)
Date: 07/19/04 

Date: Mon, 19 Jul 2004 11:55:54 -0400

Eldraad,

1) Creating a work*** 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
work*** 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 ***, 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 *** 2:
A65: 1802
I65: some data
L66: some more data

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

"Eldraad >" <<Eldraad.19imgm@excelforum-nospam.com> wrote in message
news:Eldraad.19imgm@excelforum-nospam.com...
> Mark, thanks for your reply. The reports we are using are printouts but
> 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 need
> though, I think.
>
> Trevor, you have the correct idea. This misbegotten example I used was
> close to the mark. There are columns and rows in the reports. The
> 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 column
> and the text in columns as well.
>
> (Edit) I mean line up the numbers into columns and the text into
> columns.
>
> These 5 reports CAN be e-mailed as text. The text wizard needs some
> help to line things up and must be redone each time a file is read. I
> have imported one of the reports using the wizard and it does work (but
> not automated).
>
> A macro could be used to format the text file and create an excel
> work***. In fact, one of the other departments HAD a macro set up to
> open one of the text reports, create a work*** in excel and align the
> data into columns and rows. I do not know if they had any way of
> searching the data though.
>
> I would like to tell it to create the work*** from a text file
> (always the same work*** for the SAME report) search for a certain
> data (such as the 1802 used above) which will ALWAYS be in the same
> column but NOT the same row, and using that bit of information, once it
> finds the right spot, move over 8 columns and get the data it finds
> there, move over 3 more columns and down 1 row to get the data it finds
> there AND pop it into the main report automatically. As you can see, I
> am not asking for much...LOL!
>
> I gather it would be something along the lines of a macro to convert
> the text files into individule worksheets with the data aligned columns
> and rows. A second function or macro would then check each report for
> identifying data "markers" and, once at these "markers", move over to
> specific cells and grab the data. Then the data would be acted on by a
> formula if needed and placed in the proper cell of the main report
> work***. So a conversion takes place first. Then a search for
> specific data. And, finally, a population of a specific cell in the
> main report.
>
> Or did I just confuse everyone and myself again? The big question is
> how to do this...good helpful links etc. My experience with excel so
> far has been a place to put my Time*** information so I can get paid!
> Inother words paint a big NOOB on my forehead! If you tell me what
> parts I need to do I can try to find the information myself, or if
> there is something out there to do this already...Please share the
> wealth! LOL. Crystal Reports?? I will search for information on that
> as well.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>