Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jul 2001
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Grouping of Data into Formated report (excel 2000)

    hi,

    first of all..i would like to say thanks to Geoff,LegareColeman
    and mark for replying me. As in my previous posts i not describe
    details in what i need. Thus, i post again !

    Actually what i need to do is creating a report in excel which reformatting
    the data being generated in a fixed lenght flat file .txt from the database
    table. i will import this flat file data and place it in sheet 1 A1 CELL and
    use this sheet as database for the table.

    TABLE DATA:

    <pre>CLUB ID REJ CODE REASON CARD NO DESC
    ------- -------- --------- -------- -------------
    A001 B02 not used 123456 PAYMENT
    MADE
    A001 B02 not used 236544 PAYMENT
    MADE
    A001 R01 use 222336 NO PAYMENT
    </pre>



    Then, at sheet 2, i will need to reformat this raw data into each groups and
    have their related information as below:
    Actually the "payment made" for the desc field the number of rows is not
    fixed. it depend on the amount of data store in
    the table on the database.

    <pre>CLUB ID: A001
    REJECT CODE: B02
    REJECT REASON: not used

    CARD NO DESC
    -------- ------------
    123456 PAYMENT MADE
    236544 PAYMENT MADE
    </pre>


    <pre>CLUB ID: A001
    REJECT CODE: R01
    REJECT REASON: use

    CARD NO DESC
    ------- -----------
    222336 NO PAYMENT
    </pre>

    well, i think i need a macro lst step to import the flat file so that it put
    all those data in sheet 1 then
    at sheet 2, the macro should be able to group the data on sheet 1 and
    display it as above format. coz this is a
    routine jobs as everyday there is a new flat file given to me and i need to
    arrange those information on the flat
    file so that it is group and display the information according to their
    groups.

    if anyone do have ideas in doing so. pls do give me
    some guidance over this..

    thanks for the helps!
    tianlong

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Grouping of Data into Formated report (excel 2000)

    Tianlong, it might be helpful if you attached a sample report that you are trying to read. If you just read the file as a delimited text file into Excel, then you would almost have the data as you need it. Can you get the DBA folks to change "not used" to "notused" and "NO PAYMENT" to "NOPAYMENT"? Are there any other fields in the report that have multiple words?

    After you import the report , you could delete row two (the dashes) and just use Data > AutoFilter to show the data in groups. If you want the information for each group on a separate sheet, then you can copy the AutoFiltered data with Edit > GoTo, Special. Visible Cells Only, then copy and paste this data.

    Of course, none of this is VBA, but if you record macros that do each of these pieces, you should be able to determine how to put it all together. If you use VBA, you might want to consider accessing the database directly, if possible.

    Hope this helps! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Grouping of Data into Formated report (excel 2000)

    Hello Tianlong

    I don't think you need a macro to open the file. You should be able to import it directly into Excel by use of the Text Import Wizard. When you want to open th efile select the file type as txt and it should start the wizard automatically.

    I think the second question may be solved by a Pivot table. take a look at teh attached file and see if that is near what you want. It also shows the text import wizard dialog box.

    It would be useful if you post a copy of the text file with sample data. and also a sample of the report format you would like to use.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •