Reformatting Margin Data with calculations

snuffnchess

Board Regular
Joined
May 15, 2015
Messages
68
Office Version
  1. 365
Platform
  1. Windows
I have put together a very small sampling of test data to show what I am trying to accomplish, but am not 100% of how to go about putting this in action.

We have a list of owners that pay a certain percent of margin of sales as a royalty payment. Which if it were just left at that it would be simple. There are then some exceptions where the percentages change based on the customer they are for etc. Then there are certain owners that we look back at a 3 week history for, and adjust numbers if the numbers have changed.

From the Fixed Data tab of the workbook, it will have the percentages, the exception accounts, minimum fees (if for some reason the minimum is not hit), and then column F to reflect if we are looking at the data from 3 weeks ago.

Current Tab has the margin in $ on it from current export and the margin for the exception accounts
3 wk original tab has the margin in $ as it appeared 3 weeks ago on reports
3 wk updated tab has the margin in $ as it appears today when looking at info from 3 weeks ago (there are sometimes adjustments that come in over time)

Desired result is ultimately how we want the data to look for the end result. (without the colors - i added those in just so that it was more obvious where the calculations came from).

Where do I even begin with this?


Fixed Data
Start.xlsx
ABCDEFGHIJKLMNOPQR
1OwnerItem 1Item 3Item 2Item 43wk Comparison Minimum Min Waive Alt Fee Item 3 Exception % 1Item 3 Exception % 2Item 1 Exception Name 1Item 1 Exception % 1Item 1 Exception Name 2Item 1 Exception % 2Item 1 Exception Name 3Item 1 Exception % 3Notes
2Group 118%24%15%5%n$ 250.00
3Group 218%24%15%5%y$ 250.00
4Group 317%20%17%5%y$ 125.0010%10%
Fixed Data


Current
Start.xlsx
ABCDEFGHIJKLMNOP
1Owner Item 1 Margin Item 2 Margin Item 3 Margin Item 4 Margin Item 3 Exception Name 1 Item 3 Exception Margin 1 Item 3 Exception Name 2 Item 3 Exception Margin 2 Item 1 Exception Name 1Item 1 Exception Margin 1Item 1 Exception Name 2Item 1 Exception Margin 2Item 1 Exception Name 3Item 1 Exception Margin 3Notes
2Group 1$ 144.03$ 175.40$ -$ -
3Group 2$ 684.55$ 41.02$ -$ -
4Group 3$ 12,273.59$ 4,186.31$ 3,028.64$ -Company 11396624$ 1,468.17Company 11255381$ 190.38
Current


3 wk Original
Start.xlsx
ABCDEFGHIJKLMNO
1Owner Item 1 Margin Item 2 Margin Item 3 Margin Item 4 Margin Item 3 Exception Name 1 Item 3 Exception Margin 1 Item 3 Exception Name 2 Item 3 Exception Margin 2 Item 1 Exception Name 1Item 1 Exception Margin 1Item 1 Exception Name 2Item 1 Exception Margin 2Item 1 Exception Name 3Item 1 Exception Margin 3
2Group 1$ 156.92$ 375.42$ 5.67$ -
3Group 2$ 34.01$ 91.93$ -$ -
4Group 3$ 11,783.00$ 4,019.90$ 2,907.50$ -Company 11396624$ 1,409.40Company 11255381$ 182.76
3 Wk Original


3 wk Updated
Start.xlsx
ABCDEFGHIJKLMNO
1Owner Item 1 Margin Item 2 Margin Item 3 Margin Item 4 Margin Item 3 Exception Name 1 Item 3 Exception Margin 1 Item 3 Exception Name 2 Item 3 Exception Margin 2 Item 1 Exception Name 1Item 1 Exception Margin 1Item 1 Exception Name 2Item 1 Exception Margin 2Item 1 Exception Name 3Item 1 Exception Margin 3
2Group 1$ 156.92$ 375.42$ 5.67$ -
3Group 2$ 32.96$ 91.93$ -$ -
4Group 3$ 11,783.00$ 4,118.90$ 2,907.42$ -Company 11396624$ 1,409.40Company 11255381$ 182.76
3 Wk Updated


Desired Result
Start.xlsx
ABCDEFGHIJK
1
2OwnerDescriptionRate Margin Current Fee Minimum Adjustment Alternate Fees Invoice Total
3Group 1$ 319.43$ 250.00$ 319.43
4Item 324%$ -
5Item 118%$ 144.03
6Item 215%$ 175.40
7Item 45%$ -
8
9Group 2
10Item 315%$ -$ 725.57$ 250.00$ (1.05)$ 724.52
11Item 118%$ 684.55
12Item 215%$ 41.02
13Item 45%$ -
14Item 3 - Original15%$ -
15Item 1 - Original18%$ 34.01
16Item 2 - Original15%$ 91.93
17Item 4 - Original5%$ -
18Item 3 - Updated15%$ -
19Item 1 - Updated18%$ 32.96
20Item 2 - Updated15%$ 91.93
21Item 4 - Updated5%$ -
22
23Group 3
24Item 320%$ 3,028.64$ 21,147.09$ 98.92$ 125.00$ 21,371.01
25Item 3 - Exc. 110%$ 1,468.17
26Item 3 - Exc. 210%$ 190.38
27Item 117%$ 12,273.59
28Item 217%$ 4,186.31
29Item 45%$ -
30Item 3 - Original20%$ 2,907.50
31Item 3 - Exc. 1 - Original10%$ 1,409.40
32Item 3 - Exc. 2 - Original10%$ 182.76
33Item 1 - Original17%$ 11,783.00
34Item 2 - Original17%$ 4,019.90
35Item 4 - Original5%$ -
36Item 3 - Updated20%$ 2,907.42
37Item 3 - Exc. 1 - Updated10%$ 1,409.40
38Item 3 - Exc. 2 - Updated10%$ 182.76
39Item 1 - Updated17%$ 11,783.00
40Item 2 - Updated17%$ 4,118.90
41Item 4 - Updated5%$ -
42
Desired Result
Cell Formulas
RangeFormula
F3F3=SUM(E4:E7)
F10F10=SUM(E10:E13)
H10H10=SUM(E18:E21)-SUM(E14:E17)
J10J10=H10+F10
F24F24=SUM(E24:E29)
H24H24=SUM(E36:E41)-SUM(E30:E35)
J24J24=F24+H24+I24
 

Attachments

  • fixed.png
    fixed.png
    12.4 KB · Views: 7
  • current.png
    current.png
    12.7 KB · Views: 6
  • 3wk orginal.png
    3wk orginal.png
    12.6 KB · Views: 4
  • 3wk update.png
    3wk update.png
    11.7 KB · Views: 7
  • Result.png
    Result.png
    59.3 KB · Views: 6
Making that change (Margins and Rates on same sheet) wouldn't really affect this approach. The very first step in the Results query combines all four of the transposed tables into one large table. So if a specific column were moved from one sheet to another, it would simply be located on a different row in this large table produced by the Append operation...and any subsequent steps for tagging, grouping, etc. would remain the same. If you're comfortable with the current layouts, I wouldn't recommend changing for any reasons related to producing the Results table.
 
Upvote 0

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Making that change (Margins and Rates on same sheet) wouldn't really affect this approach. The very first step in the Results query combines all four of the transposed tables into one large table. So if a specific column were moved from one sheet to another, it would simply be located on a different row in this large table produced by the Append operation...and any subsequent steps for tagging, grouping, etc. would remain the same. If you're comfortable with the current layouts, I wouldn't recommend changing for any reasons related to producing the Results table.
Understood will leave the current layout then.

Thanks for explaining.
 
Upvote 0
The script currently produces the following output.

Your desired output table includes values for the cells I've highlighted yellow. Do those values simply come from matching the corresponding "Item..." in the block of entries immediately above the yellow blocks?

It appears that your desired output table organizes the items within each output block (current, original, updated) based on the "Rate" sorted in descending order. Is this correct?....and if so, is that important?

Does your actual workbook use the same column heading names? For example, do you have headings with the literal terms "Item 1", "Item 2", etc.? Or is that text used only in your small example?
Start.xlsx
DEFGHIJKL
1
2OwnerDescriptionRateMarginCurrent FeeMinimumAdjustmentAlternate FeesInvoice Total
3Group 1Summary$ 319.43$ 250.00$ 319.43
4Group 1Item 118%$ 144.03
5Group 1Item 215%$ 175.40
6Group 1Item 324%$ -
7Group 1Item 45%$ -
8Group 2Summary$ 725.57$ 250.00$ (1.05)$ 724.52
9Group 2Item 118%$ 684.55
10Group 2Item 215%$ 41.02
11Group 2Item 315%$ -
12Group 2Item 45%$ -
13Group 2Item 1 - Original$ 34.01
14Group 2Item 2 - Original$ 91.93
15Group 2Item 3 - Original$ -
16Group 2Item 4 - Original$ -
17Group 2Item 1 - Updated$ 32.96
18Group 2Item 2 - Updated$ 91.93
19Group 2Item 3 - Updated$ -
20Group 2Item 4 - Updated$ -
21Group 3Summary$ 21,147.09$ 98.92$ 125.00$ 21,371.01
22Group 3Item 117%$ 12,273.59
23Group 3Item 217%$ 4,186.31
24Group 3Item 320%$ 3,028.64
25Group 3Item 3 - Exc. 110%$ 1,468.17
26Group 3Item 3 - Exc. 210%$ 190.38
27Group 3Item 45%$ -
28Group 3Item 1 - Original$ 11,783.00
29Group 3Item 2 - Original$ 4,019.90
30Group 3Item 3 - Exc. 1 - Original$ 1,409.40
31Group 3Item 3 - Exc. 2 - Original$ 182.76
32Group 3Item 3 - Original$ 2,907.50
33Group 3Item 4 - Original$ -
34Group 3Item 1 - Updated$ 11,783.00
35Group 3Item 2 - Updated$ 4,118.90
36Group 3Item 3 - Exc. 1 - Updated$ 1,409.40
37Group 3Item 3 - Exc. 2 - Updated$ 182.76
38Group 3Item 3 - Updated$ 2,907.42
39Group 3Item 4 - Updated$ -
40
ResultsAll
 
Upvote 0
The actual workbook does have different column names.... This was just for sample that it was used.

The lines in yellow are using the same margin % as what is listed for the item 1, item 2, item 3, item 4

It is simply pure coincidence that the rate is sorted in descending order. It is essentially different product lines where services are charged at a different percent of margin
 
Upvote 0
Sorry for the delay - it has been a day.

This is seriously amazing! And has opened my eyes to a powerful tool that really I should have played with more in the past. It seems like the file is almost there? It is just labeling those percentages in the part marked in yellow?
 
Upvote 0
Yes, I haven't been using it very long but it's quite handy. I suspected that the last couple of tweaks might be messy...and they were, as I needed to figure out how to do some things that I've not done before. No doubt this could be improved upon and I fear that it might be slow. In the end, I have several queries. The first four transpose the four input tables. Then another four do these things:
  1. One appends all of the transposed tables, creates a grouping/sorting scheme for organizing the information, assigns tags to the Attributes (eventually to become the rows in the output table) and to the Values (eventually to become the columns in the output table), changes the Description (adding Updated or Original) based on certain criteria, and pivots the intermediate table to create the general desired output form.
  2. One creates the list of Rates associated with what I term the "Tier 1" block of entries within each group.
  3. Another performs summations and moves the results to the "Summary" top row within each owner group
  4. The final one merges the first three in different ways to populate missing Rates in the Tier 2 block of entries within each owner group, creates blank rows between owner blocks, sorts the output, and performs final formatting for loading onto a worksheet.
All of the queries except for the last one are now "Connection Only", meaning they will not load onto a worksheet. Instead, each of them produces a virtual table of sorts. I've tweaked the previous code in an attempt to eliminate hard-coding column names that might cause an error. Generally, if you have a scheme for either building a list of column names from the data Source (and then refer to that list) or assign column names within the script (and refer to those names), these types of errors can be eliminated. I've taken the latter approach here.

I uploaded the latest version, available at:

I added a couple more rows to the source tables and inserted some extra columns to see how the script handled the changes. It appears to be working. I have a couple of unpopulated rates in the output table, but I think that may be because I neglected to populate certain cells on the source tables where that information would need to be specified (user error). Let me know if you run into any issues or have questions or suggestions. I'll try to add some documentation to the code to help explain the steps.
 
Upvote 0
I revisited the script and split it into several queries because of branching, where an intermediate result is needed for two or more purposes, and each of those proceeds down a different path. I've also provided documentation in the queries to explain the purpose of each query and what the code is doing. The approach taken in this version is generally the same as what was posted before, although after having worked through the problem, I saw several areas for efficiency improvements...and those have been incorporated here. The most significant change involves the initial steps where the data are loaded into Power Query. I opted for an approach that points to an Excel file and all of the worksheets in that file are loaded into a table...and then a filter is immediately applied to select only the four worksheets whose names are "Fixed Data", "Current", "3Wk Original", and "3Wk Updated" (and the names of the tables on those worksheets is immaterial). This allows for application of a function to perform the initial pivot operations on the tables in one step and the code sequences are such that the order of owners does not necessarily have to be the same on the four source worksheets. If desired, this workbook containing the queries and the final output table can exist separately from the workbook that contains the four source worksheets (i.e., the four source worksheets do not need to be in this workbook). The user still needs to modify one line of code in the BaselineTable query: one of the first few lines describes the Source...the full path/name of the Excel workbook that holds the source worksheets. Once that change is made in Power Query, saved, and then PQ is exited by File > Load to worksheet, future updates can be made from the workbook where the final results table is located by executing Data > Refresh All. The current "Source" line in the code has the file/path name that I last used (c:\users\kirkr\documents\@home\hobbies_misc\excelstuff\pqbook.xlsx)...and this file contains the queries/final output table and the source worksheets. In any case, this path/filename will need to be modified.
 
Upvote 0
Wow! This is amazing. I will dig into this a bit deeper and play with it ASAP. If I have any questions I'll let you know.

Thank you again.
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top