Possible Macro Solution To My Problem

[Robert]

There are times when I have to dump financial information from the AS400 to
Excel. When I do, it comes down in the following format:

ACCOUNT/JOB # DESCRIPTION
7618-000-00000 COMMISSIONS
VL02527 MATHEWS GINA
VL02527 MATHEWS GINA
VL02537 MICHAELS MARG
VL02537 MICHAELS MARG
8111-000-00000 DEPRECIATION
APRIL 07 DEPR ENTRY
AUG 07 DEPR ENTRY
DEC 07 DEPR ENTRY
FEB 07 DEPR ENTRY

I need an easy way to move the account # and account name to the first two
columns like this:

7618-000-00000 COMMISSIONS VL02527 MATHEWS GINA
7618-000-00000 COMMISSIONS VL02527 MATHEWS GINA
7618-000-00000 COMMISSIONS VL02537 MICHAELS MARG
7618-000-00000 COMMISSIONS VL02537 MICHAELS MARG
8111-000-00000 DEPRECIATION APRIL 07 DEPR ENTRY
8111-000-00000 DEPRECIATION AUGUST 07 DEPR ENTRY
8111-000-00000 DEPRECIATION DECE 07 DEPR ENTRY
8111-000-00000 DEPRECIATION FEBR 07 DEPR ENTRY

I can do it manually, but there are 5,000+ lines and it takes a lot of time.
Is there any way to automate this?

Thanks.

Robert

 

[Joel]

There a lots of was to do this. I used a method that will run the quickest.
I relied on the fact that the account numbers contain a dash. The macro will
not work properly if the all the account numbers don't have dashes or if
there are dashes on other rows. I don't know any other method of
distiquishing between the acount rows and the other rows.


Sub MoveData()

'add two column
Columns("A:B").Insert
RowCount = 1
Do While Range("C" & RowCount) <> ""
If InStr(Range("C" & RowCount), "-") = 5 Then
Account = Range("C" & RowCount)
Name = Range("D" & RowCount)
Else
Range("A" & RowCount) = Account
Range("B" & RowCount) = Name
End If
RowCount = RowCount + 1
Loop

LastRow = Range("C" & RowCount).End(xlUp).Row
Rows("2:" & LastRow).Sort _
Header:=xlNo, _
key1:=Range("A2"), _
order1:=xlAscending

NewLastRow = Range("A" & RowCount).End(xlUp).Row
FirstDeleteRow = NewLastRow + 1

Rows(FirstDeleteRow & ":" & LastRow).Delete

Columns.AutoFit

End Sub

 

[Rick Rothstein]

The text you identified as having been dumped into Excel, where is it at... all in one cell, all in Column A, some other arrangement?

 

[ryguy7272]

Very nice Joel! I thin you just need to add this:
Dim lastrow As Long

Anyway, that worked for me...


HTH,
Ryan--

 

[Robert]

Joel,

Thanks for your response. Since I'm a complete novice when it comes to
macros, I've got a really stupid question. Exactly where do I go to apply
your suggestion and can I simply copy and paste it?