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

[danhendo888]

https://1drv.ms/x/s!AvjBsEPEq12ngTEmm7uG4pZsIFot?e=oA9EZo

I have three tables of information. Each table has these columns:

- Year
- Vendor Number
- Expenses

How would you arrange the info to produce something like the 'output' sheet (shown in link).

 

[AlanY]

try this

Book1
	A	B	C	D
1	Vendor	2016	2017	2018
2	1999010034	-264340.18	-243313.18	-341448.27
3	1999075412	-68895.33	-21482.1	-26083.51
4	1999070532	-50470.58	-49838.33
5	1999063417	-46591.22	-68381.6	-54325.09
6	1999056490	-45247.37	-116490.32
7	1999012433	-29823.91
8	1999089508	-20908.18
9	1999011400	-19409.59	-4500.36
10	1999081476		-2941.7	-7353.85
11	7999003105			-7012.73
Output

Cell Formulas
Range	Formula
B2	=IFERROR(INDEX(INDIRECT("'"&B$1&"'!C:C"),MATCH($A2,INDIRECT("'"&B$1&"'!B:B"),0)),"")

 

[danhendo888]

try this

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]

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]

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]

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]

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

 

[danhendo888]

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

 

[sandy666]

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​