Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Oct 2005
    Location
    Charleston, South Carolina, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    To whom it my concern,
    Could someone please help me out in creating a formula and macro in a 2007 excel document to calculate Gross Product (GP) value and a total? The attached document is what Iím working with. This is an output from one of my accounting software programs. The first two sheets contain different sales transactions for a given date range. Each sheet has individual sale transactions starting on row 10. The formatting of the sheets is always the same when exported. Colum 0 shows the formula which I am using to calculate the GP. (Column G Discount)-(Column J Cost*Column H Quantity). (this formula will not be in the final document Iím working with)
    Iím trying to get a formula and/or Macro to be inserted into Sheet 3 which gives me the total GP for one of the first two sheets for a given Ship to State abbreviation (Colum K) from that sheet and for a given Rep (Colum C). For example: Sheet 1 (title Ė 02-2009) has different State abbreviations in Colum K. If the State is SC and the Rep is SAM then the total GP (Colum O) is $843.31 (=SUM('02-2009'!O12,'02-2009'!O39,'02-2009'!O52)) The Different values for Colum C will be (ACD, AE, DF, DP, I-DIST, INT, JMB, JO, LO, MJ, NC, NT, PS, RA, RA-IH, SAM, SMB, SUPER)
    I would like to be able to select or enter the Ship to State (Colum K) and also the Rep (Colum C) and have the total GP calculated and interned into Row C or D of Sheet 3. The formatting of sheet 1 and 2 will always be the same but the data in the cells will change. Also Colum O from sheet 1 showing the GP formula will not be in the sheet, so if possible I would like the macro to calculate this.
    I hope I havenít made this too complicated. Any help would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello Sean - The data looks ideal for Pivot Tables. Pivot Tables are a very powerful feature of Excel. No macros are used.

    In your sample there was only SAM for Rep, I added two more called JMB and SUPER, so you can see how this might work.

    The data has been consolidated on a new tab called All Data and a Pivot Table created on a separate tab.

    The data can be summarized by Year, Rep, GP, etc.

    Use cell B1 to select a rep or select All

    If you want to show certain states, use Cell A4.

    Hope this helps.
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Oct 2005
    Location
    Charleston, South Carolina, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Tim,
    The pivotTable looks great. However, the issue is reformation the data into a "All Data" sheet format. The actual data i'm using is very large, over 5,000 rows. The second tab of the document is the exact format that I have to work with. This data is exported from another system and I'm not able to change the format of how it is exported. About every 70 rows the data is repeated in the same format for a different date and time. Do you have any other ideas of how I could accomplish this?

    /r
    Sean

  4. #4
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Quote Originally Posted by SeanKM View Post
    Tim,
    The pivotTable looks great. However, the issue is reformation the data into a "All Data" sheet format. The actual data i'm using is very large, over 5,000 rows. The second tab of the document is the exact format that I have to work with. This data is exported from another system and I'm not able to change the format of how it is exported. About every 70 rows the data is repeated in the same format for a different date and time. Do you have any other ideas of how I could accomplish this?

    /r
    Sean
    Hi Sean - Are new tabs not allowed? Not sure what is meant by "the issue is reformation the data" ? The "All Data" tab is simply a copy of your first two tabs with empty rows eliminated, but the format of the data and the order of the columns is the same as the originals. I did add Column O for the GP calculation. It should be very simple to copy and paste over 5,000 or more rows all at once. Give the data a name of Database after copying and pasting and blank row elimination is done. Blank rows can be eliminated by Sorting all of the data by Invoice Date.

    There is lots of different reporting that can be accomplished with your data by using Pivot Tables.

    Tim

  5. #5
    New Lounger
    Join Date
    Oct 2005
    Location
    Charleston, South Carolina, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Tim,
    As soon as I left that post I realized that I could just copy only the invoice rows using a sort. Using the PivotTables will work for me. The data in the report changes every day so i have to keep formating it to use pivottables. That was what I was trying to not do. But this will work.

    Thanks again for your help.

    /r
    Sean

Posting Permissions

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