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

### 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
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

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
something like this?

part of the PivotTable:

 Sum of USD Year 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

or

 Sum of USD Year 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
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

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

#### danhendo888

##### Board Regular
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
as you probably read in post#4 this is a part of bigger table just without TopTen

 Sum of USD Year 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​

Replies
9
Views
67
Replies
7
Views
321
Replies
1
Views
40
Replies
0
Views
37
Replies
1
Views
133