Forum Discussion
VBA code to split worksheet by invoice type
pbolali I've attached your sample file. It contains two additional sheets. I also Tabled your data on Sheet1. The sheet 'PivotTables' is a PivotTable from the Table as the data source (it's easier to use Tables than standard ranges in my opinion). Then, I dropped the 'REMARKS' field into the Filter area and set it to the first value. Then, I copied the PivotTable two more times (there are only three PivotTables in this example).
PivotTable caveats:
They do not manually refresh. You have to refresh them yourself if data is added to the Table. I tend to add code to the worksheet housing a PivotTable to automatically refresh their data when the worksheet is activated. Also, and more importantly, this example has three PivotTables stacked vertically - this is generally not a good idea. Instead, having a PivotTable on each sheet would negate this issue. The reason is data will want to grow vertically, and two cannot overlap one another. This is also a general rule for Tables as well.
There is another sheet titled 'Subtotal'. This is a copy of the data, although it is not in a Table, because the subtotal feature doesn't work with them. With the data, on the Data ribbon tab, click the Subtotal button. Ensure each change is set for the field 'REMARKS' and you check the columns you want to sum. I've done this to mimic the example you set in Sheet2. It is a fast and easy way to see subtotals by a specific field. These subtotal values will automatically update when you re-apply this feature.
Regarding the VBA code you posted, if either of the above solutions work for you it would negate the need for it. I'm a very big fan of VBA, but if there is a native solution which will work for you, I'll generally recommend using it instead. With that being said, if you still want a VBA solution, I will code one for you.
- Where does S/N come from?
- How do you know "DSDP" is a sub group of Domestic Crude Sales, and further of Federation Crude Sales?
The hierarchy you show in the output isn't found anywhere in the sourced data, at least not that I can tell. You have to define it somewhere. I'm not sure if that's what the sheet 'Invoice Keys' is for or not, nor how to read/interpret it. Please be very specific when defining this, as well as the relationship with the source data.
The input sheet can be modified to include all the relationships, please let me know if more columns are required.
The serial numbers (s/n) were actually on the source input sheet but I wanted it to restart from 1 to lastrow for each group.
Each invoice number suggests the group it belongs to; the reason i provided the invoice key.
In practice, domestic crude is made up of two groups (direct sales and refinery supplies) thus invoiced as *dsdp* and *dom* as the case may be. The sheet 'Invoice Keys' was to explain the hierarchy of groupings and these groupings were (manually) done by grouping similar invoices.
I am willing to accept your advice on the preferred format of the source data sheet and/or the preferred solution; whether it is the pivot or code. I am actually learning, not an expert in excel yet. So your professional advice will be followed.
Would love to see how it looks. Going forward, I would love to learn how to explore the special features of "pivot tables". From your advice, i realised i could do more with PivotTables.
How do I parse the invoice numbers please? I tried using the long formula below=IF(ISNUMBER(SEARCH("NPDC",A2,1))=TRUE,"NPDC",IF(ISNUMBER(SEARCH("FIRS",A2,1))=TRUE,"FIRS",IF(ISNUMBER(SEARCH("DSDP",A2,1))=TRUE,"DSDP",IF(ISNUMBER(SEARCH("DPR",A2,1))=TRUE,"DPR",IF(ISNUMBER(SEARCH("MCA",A2,1))=TRUE,"MCA",IF(ISNUMBER(SEARCH("RA",A2,1))=TRUE,"RA",IF(ISNUMBER(SEARCH("2020 -",A2,1))=TRUE,"JVC",IF(ISNUMBER(SEARCH("NNPC",A2,1))=TRUE,"EF",IF(ISNUMBER(SEARCH("COS",A2,1))=TRUE,"JP MORGAN",IF(ISNUMBER(SEARCH("NGCL",A2,1))=TRUE,"Dom Gas",IF(ISNUMBER(SEARCH("GS",A2,1))=TRUE,"NGL","Feedstock")))))))))))
- Yes, that looks like a more normalized structure. You could add those columns via calculation if you know how to parse the Invoice No.
Regarding your PivotTable, I think it looks close to what you want. I would add subtotals to both 'SUBGROUP' and 'REMARKS'. Then, go to the Field Settings of 'SUBGROUP', then 'Layout & Print' tab, and check 'Insert blank line after each item label'. I'd also look at setting a Style to your PivotTable, which would help with readability. Probably one of the 'Medium' styles (i.e. "Light Blue, Pivot Style Medium 2").
If you have the inkling, you may want to find a Style which matches closest to what you want and then duplicate it to customize it. The big format item you're looking for in a setup like I describe above (e.g. subtotal levels) would be 'Subtotal Row 1', as it would give the greatest delineation between subgroups I think. 1.) I updated the source data sheet to include two hierarchical columns
2.) I attempted the pivot table method to group the source data (see sheet pivot)
I hope it is clearer now, and you can assist me further.
Thanks and best regards
@Zack Barresse
Please find attached with a few more columns.
- The groups are derived from the invoice numbers. That's exactly what I attempted to explain on the invoice key sheet.
Example: If an invoice number contains the characters *DSDP*, then it belongs to Domestic Crude group, if it contains *MCA*, It belongs to MCA Crude group, if it contains *DPR* it belongs to the DPR Group and so on.
So far, I manually introduced the column called "Remarks" to describe the respective invoice groups. My request was to do the grouping by similar invoices rather than manually introducing columns to replicate the group hierarchy (this would be prone to errors and defeat the purpose of automation).
From the output file, it shows exactly how similar invoices were grouped. I admit I am unable to do it myself thats why I asked for help. - No need for apologies pbolali. If the columns are all related, can you post how they are related to the source data? If that means including extra columns, please do so. If it's derived from the Invoice number field, please define how so.
- I am sorry as it sounds like I complicated my request. I meant to show the final output of the report i manually produce monthly.
By my last post; I asked if the source data sheet is itself not correct so I can update it to include all the columns required to either produce a pivot table or your already working vba application.
I appreciate your kind assistance and do not mean to complicate issues. I trust we are close to completion. Please what format can I produce to meet the report output as per my last submission. Thank you pbolali I'm not entirely sure what you mean.
The input sheet can be modified to include all the relationships, please let me know if more columns are required.
Can you give specific details for this? I'd like to see what you mean by this.
The serial numbers (s/n) were actually on the source input sheet but I wanted it to restart from 1 to lastrow for each group.
Are you altering the data to post it here in an attempt to make it easier for us? If so, please do not. It ends up making for more work. Otherwise, I'd like to see how this field is incorporated into your data as well.
Each invoice number suggests the group it belongs to; the reason i provided the invoice key.
In practice, domestic crude is made up of two groups (direct sales and refinery supplies) thus invoiced as *dsdp* and *dom* as the case may be. The sheet 'Invoice Keys' was to explain the hierarchy of groupings and these groupings were (manually) done by grouping similar invoices.
This sounds like it's on the avenue of what you are wanting and how we can help you get there. However, the structure on the sheet 'Invoice Keys' doesn't really help us as it's not defined at all. I can't understand what any of that means as it's just data on a sheet without organization or definition. If, however, that sheet is just an amalgamation of how the 'INVOICE NO.' field is defined/interpreted, we can ignore that altogether if you just define that field. Tell us what it means, how to parse it, and how it relates to your organization/hierarchy.
With all of this in mind, it honestly still looks like a PivotTable report to me. The key is going to be ensuring your source data is structured properly. Even if you have multiple defined Tables which are related, we can make it work. But we need all of the data in an organized structure. In your 'Expected Output' sheeet you have 3 overall levels of hierarchy, whereas previously we'd only had 1 defined (2 if you count the latest sub group request). This is what we must explore and define.