How to organise this data (Year, Vendor, expenses)

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,169
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try this

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="background-color: #D9D9D9;;">Vendor</td><td style="text-align: right;background-color: #D9D9D9;;">2016</td><td style="text-align: right;background-color: #D9D9D9;;">2017</td><td style="text-align: right;background-color: #D9D9D9;;">2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="background-color: #D9D9D9;;">1999010034</td><td style="text-align: right;;">-264340.18</td><td style="text-align: right;;">-243313.18</td><td style="text-align: right;;">-341448.27</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="background-color: #D9D9D9;;">1999075412</td><td style="text-align: right;;">-68895.33</td><td style="text-align: right;;">-21482.1</td><td style="text-align: right;;">-26083.51</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="background-color: #D9D9D9;;">1999070532</td><td style="text-align: right;;">-50470.58</td><td style="text-align: right;;">-49838.33</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="background-color: #D9D9D9;;">1999063417</td><td style="text-align: right;;">-46591.22</td><td style="text-align: right;;">-68381.6</td><td style="text-align: right;;">-54325.09</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="background-color: #D9D9D9;;">1999056490</td><td style="text-align: right;;">-45247.37</td><td style="text-align: right;;">-116490.32</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="background-color: #D9D9D9;;">1999012433</td><td style="text-align: right;;">-29823.91</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="background-color: #D9D9D9;;">1999089508</td><td style="text-align: right;;">-20908.18</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="background-color: #D9D9D9;;">1999011400</td><td style="text-align: right;;">-19409.59</td><td style="text-align: right;;">-4500.36</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="background-color: #D9D9D9;;">1999081476</td><td style=";"></td><td style="text-align: right;;">-2941.7</td><td style="text-align: right;;">-7353.85</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="background-color: #D9D9D9;;">7999003105</td><td style=";"></td><td style=";"></td><td style="text-align: right;;">-7012.73</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Output</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">INDIRECT(<font color="Green">"'"&B$1&"'!C:C"</font>),MATCH(<font color="Green">$A2,INDIRECT(<font color="Purple">"'"&B$1&"'!B:B"</font>),0</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
126
Office Version
  1. 2016
Platform
  1. Windows

If I wanted to find top 10 expenses for each year, which option would be the recommended one: power query or pivot tables?

I tried pivot tables but I'm pretty sure I am not doing it very efficiently to achieve my output.

Thank you for your solution, Alan, I appreciate it :)
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,740
something like this?

part of the PivotTable:

Sum of USDYear
Vendor
2016
2017
2018
1999005689​
-38882.04​
-7770.16​
1999005960​
-11094.72​
1999006006​
-16183.56​
-15855.45​
-14130.15​
1999006846​
-6073.01​
1999007418​
-8995.86​
-6250.02​
1999008286​
-21478.49​
-8754.49​
1999009851​
-71866.39​
-56313.15​
-54623.42​
1999010034​
-264340.18​
-243313.18​
-341448.27​
1999011390​
-14248.07​
-34182.82​
-6344.01​
1999011400​
-19409.59​
-4500.36​
1999011602​
-17870.2​
1999012396​
-3014.44​
1999012433​
-29823.91​
1999014359​
-246531.43​
-151410.71​
-359535.34​
1999014542​
-7822.91​
1999014618​
-3818.5​
1999014834​
-227689.18​
-194781.46​
-56963.96​
1999015782​
-9106.3​
-28455.15​
-11298​
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,740

ADVERTISEMENT

or

Sum of USDYear
Vendor
2016
2017
2018
1999009851​
-71866.39​
-56313.15​
-54623.42​
1999010034​
-264340.18​
-243313.18​
-341448.27​
1999014359​
-246531.43​
-151410.71​
-359535.34​
1999014834​
-227689.18​
-194781.46​
-56963.96​
1999016925​
-223466.2​
-59121.83​
1999017909​
-214366.55​
-249918.03​
-220363.56​
1999057232​
-432924.34​
-426041.09​
-6492.33​
1999063417​
-46591.22​
-68381.6​
-54325.09​
1999067483​
-354493.37​
-692218.8​
-201080.1​
1999090540​
-85333.34​
-84593.87​
Grand Total
-2082268.86
-2226833.19
-1379425.94
 

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
126
Office Version
  1. 2016
Platform
  1. Windows
Hi Sandy,
How did you create your first result?

This is how I did it, but it felt 'messy'/unorganized:
I created a pivot table for each sheet
Filtered to Bottom 10 for each. So now, I have three pivot tables.
I then copied and pasted the values for each pivot table into another spreadsheet.
Then I pivoted that table.
Interested to know how you would have done it?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,740

ADVERTISEMENT

append Years via PowerQuery then create PivotTable, sort and filter Top10 or Bottom10
 

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
126
Office Version
  1. 2016
Platform
  1. Windows
No, I mean how did you get your output in your post 4 .
What you described is for post 5 , which shows top/bottom 10 vendors filtered by grand total.

Your output in post 4 shows top/bottom 10 vendors for each year (except 2017, which shows more than 10) but I need that output
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,740
as you probably read in post#4 this is a part of bigger table just without TopTen

Sum of USDYear
Vendor
2016
2017
2018
1999005689​
-38882.04​
-7770.16​
1999005960​
-11094.72​
1999006006​
-16183.56​
-15855.45​
-14130.15​
1999006846​
-6073.01​
1999007418​
-8995.86​
-6250.02​
1999008286​
-21478.49​
-8754.49​
1999009851​
-71866.39​
-56313.15​
-54623.42​
1999010034​
-264340.18​
-243313.18​
-341448.27​
1999011390​
-14248.07​
-34182.82​
-6344.01​
1999011400​
-19409.59​
-4500.36​
1999011602​
-17870.2​
1999012396​
-3014.44​
1999012433​
-29823.91​
1999014359​
-246531.43​
-151410.71​
-359535.34​
1999014542​
-7822.91​
1999014618​
-3818.5​
1999014834​
-227689.18​
-194781.46​
-56963.96​
1999015782​
-9106.3​
-28455.15​
-11298​
1999016925​
-223466.2​
-59121.83​
1999017909​
-214366.55​
-249918.03​
-220363.56​
1999018398​
-3553.16​
1999019141​
-4432.94​
1999043777​
-5745.46​
1999049206​
-3272.28​
-6567.99​
1999056490​
-45247.37​
-116490.32​
1999057232​
-432924.34​
-426041.09​
-6492.33​
1999057915​
-2898.97​
1999058172​
-10048.87​
1999063417​
-46591.22​
-68381.6​
-54325.09​
1999064801​
-18657.26​
-32955.62​
-33872.39​
1999067483​
-354493.37​
-692218.8​
-201080.1​
1999070532​
-50470.58​
-49838.33​
1999075412​
-68895.33​
-21482.1​
-26083.51​
1999080098​
-4068.34​
1999080175​
-3313.36​
1999081217​
-29841.4​
-28267.63​
1999081476​
-2941.7​
-7353.85​
1999082979​
-4329.85​
1999084715​
-9857.09​
1999085347​
-5986.68​
1999086132​
-5025.04​
1999087107​
-22207.55​
1999087108​
-11184.39​
1999087319​
-6805.6​
1999088163​
-4370.56​
1999088522​
-9453.25​
1999088957​
-2684.38​
1999089508​
-20908.18​
1999090257​
-11710.56​
1999090540​
-85333.34​
-84593.87​
7999001074​
-3426.01​
7999001146​
-3301.65​
7999001754​
-103293.66​
7999001991​
-18868.02​
-59168.37​
7999002245​
-38346.4​
-68524.87​
7999002587​
-5884.43​
7999002591​
-14700​
7999002741​
-22831.48​
7999002753​
-68016.85​
-15459.38​
7999002979​
-4994.6​
7999003105​
-7012.73​
7999003113​
-46842.84​
7999003427​
-9626.24​
7999005255​
-38999​
7999999399​
-37641.67​
7999999458​
-8267.91​
7999999471​
-25620.43​
7999999991​
-5511.85​
-7882.58​
Grand Total
-2415235.77
-3132490.25
-1791207.04
 

Watch MrExcel Video

Forum statistics

Threads
1,108,950
Messages
5,525,822
Members
409,665
Latest member
Jessyi

This Week's Hot Topics

Top