Macro or Visual Basic for Reformatting Data

[Larry R Harrison Jr]

I have Excel 2003 (yes, 2003). I have a stream of data converted from a RPT
file. It comes in cleanly if you import it as an ASCII file (fixed width,
etc), very little problems.

The thing is, the data needs to be "cleaned" quite a bit to make it clean
etc. It looks something like this:

****************************************************************************
**
1 VENDOR NAME DATE WARRANT NBR. AMOUNT ACCOUNT DESCRIPTION

XYZ 1/2/2004 22614105 195.36
TOOLS & EQUIP UNDER $1000

190.08 TOOLS & EQUIP UNDER $1000

190.08 TOOLS & EQUIP UNDER $1000

95.04 REPAIR & MNTNCE SUPPLIES

179.52 TOOLS & EQUIP UNDER $1000
****************************************************************************
**

(I hope the line-breaks haven't screwed this up.)

As you can see:

(1) There is an extra column on the left, the one with "1" on it; it needs
to be deleted

(2) If vendor name doesn't change (in this case, it continues to be
"COMPUTER BUSINESS SOLUTIONS") it doesn't redo it, which it needs to. Same
thing happened with DATE and WARRANTY NBR.

(3) The text data is in ALL CAPS, needs to be converted to "Title Case"
format

Obviously, this would seem to require macros and some Visual basic editing
capabilities. I mean, you could simply "fill down" the data which needs to
fill, but doing that manually would take awhile. Moreover, as I am setting
this up for "end users" to handle on their own, it needs to be "dummy proof"
as no one with such data-reformatting instincts is likely to be on staff
after I'm gone.

I do have Visual Basic programming knowledge extensively with Access 97,
2000 & XP but not much so in Excel. Frankly, it's near zilch.

What are some code suggestions to do as I have suggested? Any websites which
give good code samples I can refer to on my own?

Thanks to all,

LRH

 

[Howard Kaikow]

If you have extensive VBA experience already, then easiest may to grab the
following books:

1. MSFT Press Excel VBA Step by Step
2. John Walkenbach's book, in particular the Power Programming book.
3. The Excel VBA book from, the now defunct, Wrox Press.

Using those books will serve you well for the long term and will be a more
cost effective use of your time than searching for examples.

 

[Otto Moehrbach]

Larry
If you wish, send me a file with a sample of what you have and examples
of what you want to have. Include as much detail as you can. I'll try to
write some code for you that will be both useful and instructional. Remove
"cobia97" from my email address. Otto