Results 1 to 10 of 10

Thread: Sorting Report Data and Printing

  1. #1

    Sorting Report Data and Printing



    Register for a FREE account, and/
    or Log in to avoid these ads!

    There is that possibility is there that can help me with two tables
    consist of the following fields:


    Sheet 1:


    This table is imported from a generated view in SQL, which is
    aim is to generate a report with all related invoices,
    payments and applied to each invoice notes and obviously the values
    total outstanding of each invoice, which consist of the following
    fields:

    Sheet 2:
    DESP DATE, DOCUMENT, VTO DATE, DATE
    CURRENT DAYS, DETAIL, T. DOC, OFFICE, VAT, WB
    NET, RETURN, DISCOUNT, PAYMENTS, BALANCE

    The aim is the following, make a macro in which it can
    select the number of the ID or identification number of the person and the system I
    bring all documents (invoices, debit notes, credit notes, receipts).

    I send the file to look at that I'm talking and I
    indicate what would be the best option.

    I've already been doing a filtering MSQUERY but as need is
    generate a report as you can see on page 2, I have wanted to
    automate with a macro that selecting the cedula of
    person, I bring all documents and make it
    corresponding operations.

    what I really want is that you can select on page 2 customer NIT (cell H13) and the system I fill in the fields for operations for each invoice sheet 2 (debit notes to add, subtract and credit notescash receipts)
    On page 3 to find the relationship of the cells between the two sheets.

    On the third sheet is a table with the relationship between the tables of each sheet, the idea is that the sheet 2 in cell H13, you can select the customer NIT (extracted from the sheet 1) and the system will fill the fields I on page 2 with data from sheet 1 to perform the operations for each invoice (bring invoices, debit notes to add, subtract credit notes and cash receipts).

    Note: on page 2 is the format that you want to generate


    Thanks for your help
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    Hi there, and welcome to the forum.

    I've attached a copy of your workbook that includes a PivotTable to summarize your data. You'll need to know two things about this:
    1) if you change the filter in B14 to a different number (using the dropdown) the table will re-filter for you
    2) if you update the data range on Hoja1, you will need to right click in the PivotTable and choose Refresh to get the data back in sync

    The formulas in B9:B11 and others in the header are simply using an Index/Match combination off your data table to pull back the required info.

    Hope this helps,
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Thank you very much, it'sjust what I neededand nowI onlyneed to multiplythe values ​​bythe "mccsignoclien"and getrealvalueto be able tobalanceoperationsbills

  4. #4
    You can placethis query assolved ?

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    You can add a calculated field to the pivottable to multiply items from the table against each other. I see the first, but don't see getrealvalueto in your data table...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Perfect, thanks a lot

  7. #7
    I was reviewing your answer and I noticed that the filters are not bringing me all the information, for example:if you select the client 8045447 only brings me the document number 10, when the documents are actually 4 with the same number, one of which is the bill and the other 3 are the notes associated with the original invoice, I have been reviewing the table but did not see how the selection of the fields.I send youthe file againtolook at it.

    thanks

    Attached Files Attached Files

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    Hi there,

    What version of Excel are you using? If you're in 2010, then click in the pivottable and go to PivotTable Tools-->Design-->Report Layout-->Repeat All Item Labels. (I can't remember if this is in 2007 or not.)

    Now, that will get you part way.

    If you check the last line, you'll actually find that it is actually the sum of three invoices, not just one. It's summing rows 10:12 as all the line items are identical except the amounts.

    We can fix this in two ways:
    1) We could move all the amount fields into labels... but that's going to mess up your ability to work with the numbers
    2) You could adjust your original data pull to also include the record (index) numbers. If you do that, we can add them to the PivotTable ensuring that no records are compressed as they won't be seen as true duplicates.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  9. #9

    Sorting Report Data and Printing

    TheExcelversionI'musing is2007.I send youthe file againto see whatcan help me
    Attached Files Attached Files

  10. #10
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    Okay, so what I did here was:
    • Added a new field to your table (column A) that just lists the row of the worksheet. This gives as a unique value for each record, which means we don't have to worry about data compressing on us
    • Added the new field to the pivottable
    • Gave the new field a number format of 0 [White] to hide it in the table
    • Changed the field header to a blank space
    I think that should do it for you.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Tags for this Thread

Posting Permissions

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