Text to Row automation

[DianeM]

Hello all,

I have no experience in Excel VBA (except for modifying a few recorded
macros), but am faced with a very complicated data clean-up project
which will take forever unless I can automate it.

My original data looks like this:
DocumentNumber Companies Date
Type Description
12 CoA, CoB
1/1/07 Type1 Doc12description
15 CoA
12/15/06 Type2 Doc15description
26 CoB, CoC,CoD,CoE 12/12/06
Type1 Doc26description

I need my final spreadsheet to look like this:
DocumentNumber Companies Date
Type Description
12 CoA
1/1/07 Type1 Doc12description
12 CoB
1/1/07 Type1 Doc12description
15 CoA
12/15/06 Type2 Doc15description
26 CoB
12/12/06 Type1 Doc26description
26 CoC
12/12/06 Type1 Doc26description
26 CoD
12/12/06 Type1 Doc26description
26 CoE
12/12/06 Type1 Doc26description

I was thinking that if I added a column indicating the number of items
in the column B for each row, I could somehow set a variable to that
number, add the appropriate number of rows, copy the data that's the
same on each row, and then somehow parse the data from column B into
each of the separate rows. But I have no idea how to go about this in
Excel ... my only experience with VBA (and that's not much) is in
Access.

I have written some macros to automate bits of this, but I still have
to step through each row and count the item numbers myself. It's
better than doing it by hand, but I'm thinking there must be a way I
can run the whole worksheet at one time.

Any pointers?

 

[Madhan]

Hi, you should remember one thing while programming for any office component,
which is, all are objects and expose methods and properties. If you are good
in programming for access, all you have to do is, use the relevant objects
for excel.

In your case, the solution would be a simple do-while loop to read a text
file and store the contents into either an excel worksheet or a table in
access database.

 

[Don Guillett]

Still hard to see what you want. Send a workbook with a clear explanation to
me at the address below.

 

[Joel]

You can easily add a row with
Range(Cells(RowCount, 1), Cells(RowCount, SourceLastCol)).Insert (xlShiftDown)

You can use string commands to find the position of wher the first commar is
located.

position = instr(old_string, ",")

if position > 0
short_string = left(old_string, position - 1)

'then extract evverything left of the commmar

old_string = Mid(old_sttring, position + 1)
end if