|
Possible Macro Solution To My Problem
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 |
Possible Macro Solution To My Problem
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 |
Possible Macro Solution To My Problem
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 |
Possible Macro Solution To My Problem
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 |
| All times are GMT +1. The time now is 04:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com