Reformatting Margin Data with calculations

snuffnchess

Board Regular
Joined
May 15, 2015
Messages
71
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: 18
  • current.png
    current.png
    12.7 KB · Views: 28
  • 3wk orginal.png
    3wk orginal.png
    12.6 KB · Views: 12
  • 3wk update.png
    3wk update.png
    11.7 KB · Views: 19
  • Result.png
    Result.png
    59.3 KB · Views: 12

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Do you have any discretion to change the names of your column headings? I ask because a series of formulas could be used to extract information more easily if some relatively minor changes were made to facilitate matching desired content. Also, which version of Excel are you using? You can update your account details to show that. Certain functions available in Excel 365 would facilitate construction of your desired output, but if you're not using that version...
 
Upvote 0
Hi there. I will update my profile to reflect office 365. What type of changes do you think should be made to the headers? I do have discretion to change if needed. If the changes are major, I would just reformat the data that comes in to us via a macro so that it would match whatever is needed.
 
Upvote 0
Hello Kirk,

So sorry to be a pest - just wanted to see what it is that you had in mind for updating the data to make this work?
 
Upvote 0
It's important provide sufficient clues so that each data element can be assigned to the correct category. In this case, a "category" means the intersection of a particular row and column in your Desired Result table. The Desired Result table has some columns that are to be taken directly from any of the other source tables (Owner, Description, Rate, Margin, Minimum, and Alternate Fees). Other columns in the Desired Result table are calculated from those just mentioned (Current Fee, Adjustment, and Invoice Total).

We also see in the Desired Result table that the rows involve groupings by Owner (Group 1, Group 2, etc.), then by Description. The Description labels appear to be sorted based on the sheet from which the data originate (with Fixed Data first followed by Current then by 3 Wk Original then by 2 Wk Updated). Within each subgrouping, further sorting appears to be based on the Fixed Data "rates" from largest to smallest (in this example, Item 3 at 0.24 followed by Items 1, 2, and 4.

To facilitate the categorization process, I looked for clues in the column headings, and most of them are okay. For example, margin "rates" are described in columns that include either the word "rate" or the "%" symbol, and those are used in the approach I'll describe below. The margin rates on the Fixed Data sheet do not include either of these clues, so I suggest adding "Rate" to them (I've done this in my sample file).

Rather than trying to pull data piecemeal from the various sheets, I think it makes sense to construct a large flat table that combines all of the information, assigns tags to it so that each element can be correctly mapped. Two ways to do this are with Power Query and VBA. I'm more comfortable with Power Query, but by most measures, I'm still a novice with it. In any case, I've taken a crack at transforming the tables and have gotten fairly close to your desired output, although there are a few things to consolidate and clean up...if I can figure it out, or perhaps someone will solve these trickier matters.

If you're not familiar with Power Query, it is a component of Excel that provides tools for operating on and transforming tables via a programming language (M-code). The first step is to transpose all of the source tables so that information pertaining to each owner appears in columns (rather than rows, as in the source tables).

A typical M-code script for this is:
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Added Custom" = Table.AddColumn(#"Transposed Table", "Sheet", each "FixedData")
in
    #"Added Custom"

Here "Table4" is the default name Excel offered for the table on the FixedData sheet, and I simply accepted that name. This query is called FixedData, and three other very similar ones are Current, 3WkOrig, and 3WkUpdate, each transposing the tables on those similarly named sheets.

Then the main query called "Results" performs the task of appending the four transposed tables (stacking them on top of each other). An important assumption here is that the Owners are listed in the same order in all of the source tables, as the current code makes that assumption. Let me know if this assumption is problematic.

Then the long appended table is "unpivoted" so that all owner columns are stacked one over the other while keeping the values and attributes attached to them. Then a series of rules are applied to assign tags to each row to describe which column the value should appear in and where in the final output table should the row appear (this is where the word/symbol clues are critical). As mentioned previously, this query is a work in progress. The current output is show below (I left the helper columns in place that are used to sort by owner, tier (whether the data come from either of the 3Wk sheets), and within Tier2 (whether data come from 3WkOriginal or 3WkUpdated). In the final version, those columns will be removed before Power Query loads the table back onto the Results worksheet. I think they may be important to facilitate making the calculations in your Desired Results table.

The Results table has no formulas in it, as all of the operations to generate it are performed in the Power Query script. I still need to figure out how to move the value contents on the "Alt Fee" and "Minimum" rows to their corresponding "3wk Comparison" rows...and then the term "3Wk Comparison" will be changed to "Summary" or "Top Level" and will become the upper row for each Owner group.

With this approach, your tables are maintained normally, and when you want to generate a revised Results table, you would execute Data > Refresh All and the queries will execute and rebuild the Results table. For that reason, if formulas are used somewhere on the Results sheet, be sure:
  1. The formulas can accommodate a potentially changing Results table structure (e.g., if some currently unused columns in the source tables are filled with data, new rows will appear in the Results table).
  2. The formulas won't be overwritten when the new table is loaded.
You can get to the Query Editor via Data > Queries & Connections...and then hover the cursor over a query name and a popup window appears with Edit as an option...that is one way. Alternatively, click anywhere in the output table and then in the menu bar, click Query > Edit.
Sort.OwnerSort.TierSort.Tier2OwnerDescriptionRateMarginMinimumAlternate FeesNameComparison
11Group 13wk Comparisonn
11Group 1Item 10.18144.03n
11Group 1Item 20.15175.4n
11Group 1Item 30.240n
11Group 1Item 40.050n
11Group 1Minimum250n
21Group 23wk Comparisony
21Group 2Item 10.18684.55y
21Group 2Item 20.1541.02y
21Group 2Item 30.240y
21Group 2Item 40.050y
21Group 2Minimum250y
221Group 2Item 1 - Original34.01y
221Group 2Item 2 - Original91.93y
221Group 2Item 3 - Original0y
221Group 2Item 4 - Original0y
222Group 2Item 1 - Updated32.96y
222Group 2Item 2 - Updated91.93y
222Group 2Item 3 - Updated0y
222Group 2Item 4 - Updated0y
31Group 33wk Comparisony
31Group 3Alt Fee125y
31Group 3Item 10.1712273.59y
31Group 3Item 20.174186.31y
31Group 3Item 30.23028.64y
31Group 3Item 3 - Exc. 10.11468.17Company 11396624y
31Group 3Item 3 - Exc. 20.1190.38Company 11255381y
31Group 3Item 40.050y
321Group 3Item 1 - Original11783y
321Group 3Item 2 - Original4019.9y
321Group 3Item 3 - Exc. 1 - Original1409.4Company 11396624y
321Group 3Item 3 - Exc. 2 - Original182.76Company 11255381y
321Group 3Item 3 - Original2907.5y
321Group 3Item 4 - Original0y
322Group 3Item 1 - Updated11783y
322Group 3Item 2 - Updated4118.9y
322Group 3Item 3 - Exc. 1 - Updated1409.4Company 11396624y
322Group 3Item 3 - Exc. 2 - Updated182.76Company 11255381y
322Group 3Item 3 - Updated2907.42y
322Group 3Item 4 - Updated0y

The current M-code that produced this table:
Power Query:
let
    Source = Table.Combine({FixedData, Current, #"3WkOrig", #"3WkUpdate"}),
    #"Trimmed Text" = Table.TransformColumns(Source,{{"Column1", Text.Trim, type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Trimmed Text", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Owner", type text}, {"Group 1", type any}, {"Group 2", type any}, {"Group 3", type any}, {"FixedData", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Owner] <> "Owner")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"FixedData", "Sheet"}}),
    #"Merged Columns" = Table.CombineColumns(#"Renamed Columns",{"Owner", "Sheet"},Combiner.CombineTextByDelimiter("/?/", QuoteStyle.None),"Merged"),
    #"Reordered Columns" = Table.ReorderColumns(#"Merged Columns",{"Merged", "Group 1", "Group 2", "Group 3"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Merged"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Merged", Splitter.SplitTextByDelimiter("/?/", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", type text}, {"Merged.2", type text}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Merged.1", "CHeading"}, {"Merged.2", "Sheet"}, {"Attribute", "Owner"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns1", "ValueTag", each if Text.Contains([CHeading], "Rate") then "Rate" else if Text.Contains([CHeading], "%") then "Rate" else if Text.Contains([CHeading], "Margin") then "Margin" else if Text.Contains([CHeading], "Minimum") then "Minimum" else if Text.Contains([CHeading], "Alt Fee") then "Alternate Fees" else if Text.Contains([CHeading], "Name") then "Name" else if Text.Contains([CHeading], "Comparison") then "Comparison" else null),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Conditional Column", "CHeading", "AttrTag"),
    #"Replaced Value1" = Table.ReplaceValue(#"Duplicated Column"," Rate","",Replacer.ReplaceText,{"AttrTag"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1"," %","",Replacer.ReplaceText,{"AttrTag"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2"," Margin","",Replacer.ReplaceText,{"AttrTag"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3"," Name","",Replacer.ReplaceText,{"AttrTag"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","Exception","- Exc.",Replacer.ReplaceText,{"AttrTag"}),
    #"Added Conditional Column1" = Table.AddColumn(#"Replaced Value5", "AttrTag2", each if [Sheet] = "3WkUpdate" then [AttrTag]&" - Updated" else if [Sheet] = "3WkOrig" then [AttrTag]&" - Original" else [AttrTag]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"CHeading", "AttrTag"}),
    #"Added Conditional Column2" = Table.AddColumn(#"Removed Columns", "Sort.Tier", each if [Sheet] = "FixedData" then 1 else if [Sheet] = "Current" then 1 else 2),
    #"Grouped Rows" = Table.Group(#"Added Conditional Column2", {"Owner"}, {{"Grouping", each _, type table [Sheet=nullable text, Owner=text, Value=any, ValueTag=text, AttrTag2=text, Tier=number]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Sort.Owner", 1, 1, Int64.Type),
    #"Expanded Grouping" = Table.ExpandTableColumn(#"Added Index", "Grouping", {"Sheet", "Value", "ValueTag", "AttrTag2", "Sort.Tier"}, {"Sheet", "Value", "ValueTag", "AttrTag", "Sort.Tier"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Grouping",{"Sheet"}),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns1", {{"Sort.Owner", type text}, {"Sort.Tier", type text}}, "en-US"),{"Sort.Owner", "Sort.Tier", "Owner", "AttrTag"},Combiner.CombineTextByDelimiter("/?/", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns1", List.Distinct(#"Merged Columns1"[ValueTag]), "ValueTag", "Value"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Pivoted Column", "Merged", Splitter.SplitTextByDelimiter("/?/", QuoteStyle.Csv), {"Sort.Owner", "Sort.Tier", "Owner", "Description"}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Split Column by Delimiter1",{"Sort.Owner", "Sort.Tier", "Owner", "Description", "Rate", "Margin", "Minimum", "Alternate Fees", "Name", "Comparison"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns2",{{"Owner", Order.Ascending}, {"Description", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Comparison"}),
    #"Added Conditional Column3" = Table.AddColumn(#"Filled Down", "DeleteRow", each if Text.Contains([Description], "Original") and [Comparison] = "n" then 1 else if Text.Contains([Description], "Updated") and [Comparison] = "n" then 1 else null),
    #"Filtered Rows1" = Table.SelectRows(#"Added Conditional Column3", each ([DeleteRow] = null)),
    #"Removed Columns3" = Table.RemoveColumns(#"Filtered Rows1",{"DeleteRow"}),
    #"Added Conditional Column4" = Table.AddColumn(#"Removed Columns3", "Sort.Tier2", each if Text.Contains([Description], "Original") then 1 else if Text.Contains([Description], "Updated") then 2 else null),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Conditional Column4",{"Sort.Owner", "Sort.Tier", "Sort.Tier2", "Owner", "Description", "Rate", "Margin", "Minimum", "Alternate Fees", "Name", "Comparison"})
in
    #"Reordered Columns1"
No doubt, many improvements could be made to this script and approach. I hope to make further progress on it soon. Please let me know if you see any issues.

Because it is somewhat involved to get the scripts loaded and running, I've uploaded my file to Dropbox...available for download at:
 
Upvote 0
Wow! Thank you for the thorough explanation. I am going to dig into your response tomorrow and get back to you.

Was hoping to do this in VBA, but open to learning power query as well, as there will be close to 200 owner accounts.

More to come tomorrow. Thank you again
 
Upvote 0
With 200 owner accounts, you'll definitely want either Power Query or VBA. The latter might be the preferred approach, but I wouldn't be much help with it. I think with VBA the approach would be similar. Transpose all source tables (need to confirm that all owner accounts appear in the same order across the source tables?). Then append and tag...the Power Query script describes the approach I took to assign the tags. You could try out the current offering by cutting and pasting real info from your worksheets into the sample tables...and see if anything breaks.
 
Upvote 0
Hi Kirk... Seriously thank you for all of this work that you have put into this. I understand what you mean about the reformatting of the data, and this could definitely work. And I think I have found a way to incorporate CBA and power query together. I will say though that I am 100% unfamiliar with power query so it will be a process for sure.

I understand the logic behind it... just putting that logic into practice is where the issue comes in, if that makes sense.
 
Upvote 0
I think the only changes I made to the source tables were the four headings shown here, where I added "Rate" so that the tag-assigning steps in the script could associate these columns with the Rate column in the Results table.
OwnerItem 1 RateItem 3 RateItem 2 RateItem 4 Rate

Are you able to confirm that the owners listed down column A on each sheet are in the same order on each sheet? If you follow the link I posted and download the Excel file, it includes the queries I described. You should be able to copy 10 or so rows of actual data from each of the four sheets in the official workbook and paste it directly into the corresponding tables in the sample file. Delete the three rows of sample data from each of the four tables, confirm that each of the tables have automatically resized to include your real data, and then execute Data > Refresh All, which should generate a new table on the Results sheet.
 
Upvote 0
The names would be the same on each sheet.... Is it easier / make more sense to do if the Margin amounts and the % are on the same worksheet? So for example if the data from the Fixed Data and Current worksheets were all in one row for each group?
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,624
Latest member
gregg777

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