Results 1 to 8 of 8
  1. #1
    mrtjan
    Guest

    Help with Macro/Function

    I am hoping that I can get help with this situation. I am exporting information from an AS400 database into Excel. The report I am trying to generate is a subtotal of all orders received from a date range. The way the information is extracted, it gives line items on all orders. The problem this creates is that I have 40,000 lines from 4000 orders. Is there a way to take the line items on the same orders and combine them to see just the total for that particular order? If I am not making myself clear, I will try to show below what the output actually looks like. I would really appreciate the help! I hate to print out 200 pages when I can do it in 50.
    Here's the way it looks:
    ORDER NO. QUANTITY PRICE EXTENDED
    1234 5 1.00 5.00
    1234 10 2.00 20.00
    1234 20 0.10 2.00
    1234 1 10.00 10.00
    2345 20 1.00 20.00
    2345 5 1.00 5.00
    ----------------------------------------
    The report goes on and on similar to this. Can I compare the first column and then run a total of the extended price column or is this something that I should do in Access or another database application?
    THANK YOU FOR ANY HELP!!!!!!

  2. #2
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Macro/Function

    This is the sort of thing I would do in Access, but it does sound like something you could do with Datasubtotal function which will provide a subtotal for each change in a defined column, so every time the code changes the orders total would be calculated, you would have to sort the data by order number first

  3. #3
    mrtjan
    Guest

    Re: Help with Macro/Function

    Thanks, I will try to create something with that function. Now, something else that I want to do is if I get the total to erase the lines that are listed on the same order.
    Thanks again!

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Macro/Function

    Hi,

    I tend to like VBA solutions for this sort of thing- perhaps because I don't know as much about Excel as I should- but I also find that at times, I've got so much more control over things that I should have started with VBA in the first place.

    Anyway,

    Here's what I've come up with. It assumes your data is in Sheet1, and the summary data will be put into Sheet2. It assumes that processing will stop at the first blank cell.

    <pre>Option Explicit

    Sub Macro1()
    Dim i As Long
    Dim j As Long
    Dim curTotal1 As Currency
    Dim curTotal2 As Currency
    Dim curTotal3 As Currency
    Dim prevAcc As String

    i = 1
    j = 1
    Sheets("Sheet1").Activate
    prevAcc = Cells(1, 1)

    Do Until Cells(i, 1) = ""
    If Cells(i, 1) <> prevAcc Then
    With Sheets("Sheet2")
    .Cells(j, 1) = prevAcc
    .Cells(j, 2) = curTotal1
    .Cells(j, 3) = curTotal2
    .Cells(j, 4) = curTotal3
    j = j + 1
    End With
    curTotal1 = 0
    curTotal2 = 0
    curTotal3 = 0

    End If
    curTotal1 = curTotal1 + Cells(i, 2)
    curTotal2 = curTotal2 + Cells(i, 3)
    curTotal3 = curTotal3 + Cells(i, 4)
    prevAcc = Cells(i, 1)
    i = i + 1
    Loop
    With Sheets("Sheet2")
    .Cells(j, 1) = prevAcc
    .Cells(j, 2) = curTotal1
    .Cells(j, 3) = curTotal2
    .Cells(j, 4) = curTotal3
    End With

    End Sub
    </pre>

    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Macro/Function

    Since I could not figure out any reason why you would want to subtotal the number of items or the unit price of each, I have posted a modification of Geoff's otherwise excellent code below. I have also added one additional feature. My code will clear Sheet2 before it starts inserting the subtotals, and it starts the subtotals in row 2. This way, you can put column headers in row 1, and apply whatever formatting you want to the columns and keep reusing this sheet every time you need to do this.

    <pre>Public Sub Macro1()
    Dim i As Long
    Dim j As Long
    Dim curTotal As Currency
    Dim prevAcc As String
    i = 1
    j = 2
    Sheets("Sheet1").Activate
    prevAcc = Cells(1, 1)
    With Sheets("Sheet2")
    Range("A2:B16000").ClearContents
    End With
    Do Until Cells(i, 1) = ""
    If Cells(i, 1) <> prevAcc Then
    With Sheets("Sheet2")
    .Cells(j, 1) = prevAcc
    .Cells(j, 2) = curTotal
    j = j + 1
    End With
    curTotal = 0
    End If
    curTotal = curTotal1 + Cells(i, 4)
    prevAcc = Cells(i, 1)
    i = i + 1
    Loop
    With Sheets("Sheet2")
    .Cells(j, 1) = prevAcc
    .Cells(j, 2) = curTotal
    End With
    End Sub
    </pre>

    Legare Coleman

  6. #6
    mrtjan
    Guest

    Re: Help with Macro/Function

    Thank you both for the solutions!
    I have a quick question.... How can I learn more about doing these types of functions?

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Macro/Function

    One good way is to frequent places like this. Study and understand the code you see posted. Ask questions. There are also a lot of books availble that might help.
    Legare Coleman

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help with Macro/Function

    Another way is by doing it. Try to develop your own code solutions then ask for help with the bits which do not work.

    And, above all, have fun.
    David Grugeon
    Brisbane Australia

Posting Permissions

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