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

Hi Robert,

If this is a txt, csv, or prn file format you can (excel 2003) do:

Slectr File from the menu
Select Open
this should bring up the Text Import Wizard. Follow the steps and the data
should be brought into your spreadsheet parsed into separate columns.

Please press Yes if this helps.

John
"Robert" wrote:

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

John,

That's the problem. The account number and account name are in the same
column as the data. I need them moved over to the right of the data.

"JCS" wrote:

Hi Robert,

If this is a txt, csv, or prn file format you can (excel 2003) do:

Slectr File from the menu
Select Open
this should bring up the Text Import Wizard. Follow the steps and the data
should be brought into your spreadsheet parsed into separate columns.

Please press Yes if this helps.

John
"Robert" wrote:

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

Hi Robert.
I work with AS400 too, and what I do is first import the txt file to excel
(Data-Import External Data- Import Data). After that I Select all range of
data and apply Autofilter(Data-Autofilter). Clic on the new created buttons
and go to (Custom...) Using wild card(*) I filter the part I want to
replicate on a different column dragging it(once filtered), in your case the
Account number. One such a filter could be (????-???-?????), the (?)
representing one character, for each number on the Account. Once you filter
by the account number, you drag it to next column (insert one). All you have
to to now is select the new column with the account numbers and go to Edit
(Edit-Go to-Special-Blanks), after that press F2, then write =A1 (here the
letter A is the actual column where the filtered numbers are and the row
number the first row with an account number it could be 1 or 2, or whatever).
Then press CTRL+Enter, and the numbers will replicate as you wanted.
Ex.
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

Good luck!
--
Alex
*Remember to click "yes" if this post helped you. Thank you!



"Robert" wrote:

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

If, as you say, all is in the SAME column, then this will work but leaves
some short rows at the end to delete.

Sub lineupdata()
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If InStr(Cells(i, 1), " ") = 15 Then
mv = Cells(i, 1)
Rows(i).Delete
End If
If InStr(Cells(i, 1), " ") < 15 Then
Cells(i, 1).Value = mv & Cells(i, 1)
End If
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Robert" wrote in message
...
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