+ Reply to Thread
Results 1 to 9 of 9

Transform a standardized output into structured format for Pivot table

  1. #1
    Forum Contributor
    Join Date
    09-29-2011
    Location
    Cape Town
    MS-Off Ver
    Excel 2010
    Posts
    107

    Transform a standardized output into structured format for Pivot table

    Hi,

    I receive a standard output when I export some data from an accounting software package into Excel.

    My aim is to transform this into a structured output so that I can pivot the data and create reports.

    I've attached an Excel workbook. "Sheet1" is an example of the output from exporting from the accounting package, and the tab "Consolidated" takes the first two invoices as an example and I've consolidates them in my required format. I would want to do this for all the entries in 'Sheet1"

    Things to note:
    Every "Item Code" is followed by the "Item Description"
    Every time the word "Customer ...." appears in column A we are referring to a new invoice. The invoice number is also in Column A (Total Invoice...") but right at the end of the list of Item Codes" and "Item Descriptions"
    The relevant date for the invoice in in column B on the same row as the invoice number (Total Invoice...")

    I've managed to find snippets of code to search for certain word's such as "Customer" etc. but I'm battling to make it dynamic in the sense that the number of items per invoice changes depending on the invoice.

    Would really appreciate some help.

    Thanks a million!
    Attached Files Attached Files
    Last edited by concatch; 03-22-2016 at 07:29 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Transform a standardized output into structured format for Pivot table

    Try this
    The macro always clears sheet "Consolidated" before doing anything else
    To run the macro just hit CTRL + t

    Is Excel interpreting some of the dates incorrectly when you run this? It is on my PC (but it could be my default settings)
    I have not tried to correct this - so let me know if also occurring for you.

    Please Login or Register  to view this content.
    NOTE - rows have been deleted by data filtering and deleting the visible rows (much quicker than deleting rows one by one)
    Attached Files Attached Files
    Last edited by Kevin#; 03-22-2016 at 10:51 AM.

  3. #3
    Forum Contributor
    Join Date
    09-29-2011
    Location
    Cape Town
    MS-Off Ver
    Excel 2010
    Posts
    107

    Re: Transform a standardized output into structured format for Pivot table

    Hi Kevin,

    Honestly, your work is incredible and super efficient, thank-you so much.

    2 things - I'm having the same issues with the dates as you, not exactly sure why?

    I know its asking a lot but there is a slight tweak that would really close the loop on this. I'm trying to link the invoice number to a credit note so that I can eventually see per invoice, how many items were returned.

    If you look at:
    row 1626: Column A has "Total Invoice : 14166" and column C has "Order : "
    row 7656: Column A has "Total Credit Note : IC100350" and column C has "Order : 14166"

    Is there a way to add an additional column which will be populated if there is a credit note?

    Invoice Number | Credit Note Number

    14166 | IC100350

    This would mean that once a credit note appears, there is a check to column C which populates the invoice number and then the credit note number is populated in a new column... Based on your code which seems to loop, the original invoice number will be populated without a corresponding credit note but if you do apply the above logic, then when credit notes are found both invoice and credit note will be populated. I've attached an example...

    Thanks again for taking the time to assist
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Transform a standardized output into structured format for Pivot table

    I will think about best approach here -probably build a look-up table of invoices/credit notes from sheet1 values - will consider tomorrow.
    Will also try to correct the dates - Excel is opting for mm/dd sometimes and dd/mm in other cells. So must format each value when placed in the date column. Do you want dd/mm/yy?

    Melba Toast appears on the credit note but was not an item on the original invoice!
    Why would that happen? (Multi-invoice against Order perhaps?)

    How are you going to update values on ongoing basis?
    Sheet1 contains dump of Sales Journal for period 01/12/15 to 29/02/16
    What will happen to March 2016 sales? - will they be added to the bottom of sheet "Consolidated"?

  5. #5
    Forum Contributor
    Join Date
    09-29-2011
    Location
    Cape Town
    MS-Off Ver
    Excel 2010
    Posts
    107

    Re: Transform a standardized output into structured format for Pivot table

    Very good point. What has happened is that the credit note being issued referred to a previous invoice and not the most current one. The assumption we made was that any returns would relate to the most recent invoice but as you have identified this is not always the case. The issue is that customers receive multiple orders in a month and don't necessarily have the ability to link an item back to the original invoice.

    For the time being I'm happy to leave it the way it is with this margin of error and I can run some rule sets which check to see if the item from a credit note did appear on the latest invoice. If not then I'll have to check the previous one using the date and customer until the item appears on an invoice, and I'll make the assumption it was from this invoice. I guess I could try build a rule-set that loops through previous invoices until the item is found but I can think more about this at a later stage once I have this primary data set structured. Also if I run an analysis over the month or last 30 days, and aggregate per customer, this should reduce the error as most returns will happen within this period. Only information I lose is the ability to track % return per invoice but I can use a weighted average by considering total returns / total sales per customer in a month...

    In terms of updating - I'll export new data over a different time period and would append to the Consolidated sheet. Note sure if there is a way to ensure that I don't double count without adding a lot of overhead to the macro...

    Let me know your thoughts on the lookup table, conceptually I think I know what you are possibly referring to in terms of creating a distinct list of the credit note number and corresponding order no. , and then checking to see if the order no. matches an invoice number and then updating...

  6. #6
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Transform a standardized output into structured format for Pivot table

    I have taken all your comments into account.
    - extra column is added for credit notes
    - dates are now correctly formatted
    - Macro A converts the data, macro B adds the values in the credit note column, Macro C tranfers the values to sheet Consolidated

    Run the macros in turn "A" then "B" then "C" (allows you to look at intermediate stages)

    Notes
    - date formatting issue resolved by utilizing "DateSerial" function
    Please Login or Register  to view this content.
    - avoided creating a secondary look-up table by building it into your table of values. Credit note column appears before invoice column because vlookup cannot look up the other way round
    - proof total check added at end of macro A (there will always be a minor difference due to roundings, but the check should tell you if the difference exceeds 1 in total)
    - "Evaluate" function used to sum the values in column "L"
    (build the formula like you would in the spreadsheet, and wrap it in the "Evaluate" function to achieve what the formula would have done - in this case sums column "L")

    Please Login or Register  to view this content.
    - invoice/credit note matching based on perfect match (one for one) - would be very slow otherwise
    - there is a check for overlapping dates when Macro C is run (have deliberately included 1 day overlap to prove it works - but ignore it and consolidate)



    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Kevin#; 03-24-2016 at 10:49 AM.

  7. #7
    Forum Contributor
    Join Date
    09-29-2011
    Location
    Cape Town
    MS-Off Ver
    Excel 2010
    Posts
    107

    Re: Transform a standardized output into structured format for Pivot table

    Thanks so much Kevin, tested and everything seems to be working really well and is incredibly efficient!

    Quick question -

    - there is a check for overlapping dates when Macro C is run (have deliberately included 1 day overlap to prove it works - but ignore it and consolidate) - I'm not exactly sure what you mean by overlapping dates in this instance? There will often be multiple invoices on the same date?

  8. #8
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Transform a standardized output into structured format for Pivot table

    This is checking if there is an overlap in dates between what is already on sheet "Consolidated" and what is being added by the latest macro.
    This is only helpful if you are able to include all invoices for a particular date at the same time. If you cannot do this, then may as well remove the check.
    It may be worth including some kind of duplicate invoice check? Perhaps run it after the consolidation macro, and delete any duplicate invoices. What do you think?

  9. #9
    Forum Contributor
    Join Date
    09-29-2011
    Location
    Cape Town
    MS-Off Ver
    Excel 2010
    Posts
    107

    Re: Transform a standardized output into structured format for Pivot table

    Hi Kevin, apologies for the delayed response

    Okay I see, what you say does make sense. Yes I would agree, a duplicate invoice check would really make a big difference. I started looking into this to run a check on the invoice and credit note columns and I think I have picked up a small bug.

    If you look at row 3603 of the consolidated data, the Credit note number is 192022 and the Invoice number is IC100435. These seem to have switched around i.e. invoice number should read 192022, and there are other cases like this. Most of them are correct but any idea why this would be happening?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Transform a standardized output into structured format for Pivot table
    By concatch in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-22-2016, 07:41 AM
  2. How to do this output from pivot table?
    By ccastell88 in forum Excel General
    Replies: 1
    Last Post: 01-07-2016, 03:58 AM
  3. [SOLVED] Multiple Name Formats in one Column Need to be Standardized to one Format
    By mo4391 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-02-2015, 04:35 PM
  4. Structured references for Pivot Table?
    By Cam in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-15-2015, 01:33 AM
  5. [SOLVED] Transform semi-structured bloomberg data into pivottable table
    By jasonleewkd in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-21-2013, 08:10 AM
  6. Pivot table "structured selection" issue
    By blackworx in forum Excel General
    Replies: 0
    Last Post: 11-22-2011, 08:33 AM
  7. pivot table output
    By stevekirk in forum Excel General
    Replies: 0
    Last Post: 09-15-2006, 12:47 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1