When compare doesn't mean compare! How do I create a profit and loss sheet from 2 separate sheets?

[jimsallyiona]

Hi,
I have trawled all the forums and googled till I’m blue
but I’m losing my decorum and don’t know what to do!

Please can you help me solve what I think should be a really simple profit and loss spreadsheet problem?
Every time I look up ‘compare spreadsheets’ I find solutions which only highlight cells that are different from one another using conditional formatting as a sort of validation exercise to spot mistakes. Clearly I am using the wrong terminology!

I want to actually compare values in cells and report the difference in a new sheet.
I have:

Two Spreadsheets in 1 workbook.
Names “2014-2015” and “2015-2016”.
These contain cost data by commodity and are last year and this year’s data.
To start with assume each sheet is identical. List of 40 commodities in Column starting B3 and row of 12 Months starting in row 2.

How do I get excel to create a new sheet showing increase or decrease in cost (and by how much) when comparing the two years? In other words copies the template (I guess) of commodities and months and then populates the cells with profit or loss.

That's my opener! I will then need to make things a bit more complex, if it is possible, when one sheet has more commodities than another. I'll post a screen shot if someone has the time (patience) to explain how I do that.
Thanks so much for putting up with a numpty
Jim

 

[AlanY]

a slim down version of your data

Excel 2012
	A	B	C	D	E	F
1		C1	C2	C3	C4	C5
2	Jan	120	250	160	220	250
3	Feb	250	230	190	190	230
4	Mar	240	190	140	150	120
5	Apr	160	150	110	180	200
6	May	240	130	230	220	160
7	Jun	150	190	220	190	250
8	Jul	180	230	180	240	220
9	Aug	170	220	200	180	120
10	Sep	200	240	110	100	250
11	Oct	110	130	110	100	160
12	Nov	170	110	190	140	190
13	Dec	230	200	140	180	200
2014-2015

Excel 2012
	A	B	C	D	E	F
1		C1	C2	C3	C4	C5
2	Jan	160	130	210	190	120
3	Feb	110	150	220	160	210
4	Mar	220	240	140	110	190
5	Apr	110	170	160	230	250
6	May	100	110	130	100	150
7	Jun	190	190	190	190	220
8	Jul	190	160	120	140	180
9	Aug	240	240	190	180	140
10	Sep	170	180	100	210	230
11	Oct	130	150	250	180	240
12	Nov	160	200	230	220	250
13	Dec	130	190	170	150	170
2015-2016

and the new sheet, just copy formula in C1 to the whole range (+ve indicates increase in cost)

Excel 2012
	A	B	C	D	E	F
1		C1	C2	C3	C4	C5
2	Jan	40	-120	50	-30	-130
3	Feb	-140	-80	30	-30	-20
4	Mar	-20	50	0	-40	70
5	Apr	-50	20	50	50	50
6	May	-140	-20	-100	-120	-10
7	Jun	40	0	-30	0	-30
8	Jul	10	-70	-60	-100	-40
9	Aug	70	20	-10	0	20
10	Sep	-30	-60	-10	110	-20
11	Oct	20	20	140	80	80
12	Nov	-10	90	40	80	60
13	Dec	-100	-10	30	-30	-30
Diff

Cell Formulas
Range	Formula
B2	='2015-2016'!B2-'2014-2015'!B2

 

[jimsallyiona]

Hi
Thank you for this solution. Are you able to answer the other part of my problem.
How do I get Excel (OOps my version is through MS office 2010) to transpose/merge all the months and commodities as well onto the new sheet before doing the calculation?
How do I get the formula to work if there are more commodities in one year and not in another? If it helps it would be easy to give each commodity a reference number.

2014-2015
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]April[/TD]
[/TR]
[TR]
[TD]Pots[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pans[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sticks[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]stones[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

2015-2016
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]April[/TD]
[/TR]
[TR]
[TD]Pots[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sticks[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Elephants[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lettuce[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks again,
Jim

 

[AlanY]

try this

Excel 2012
	A	B	C	D	E
1		Jan	Feb	Mar	April
2	Pots	140	130	170	160
3	Sticks	160	200	240	250
4	Elephants	230	150	110	140
5	Lettuce	130	140	230	240
2015-2016

Excel 2012
	A	B	C	D	E
1		Jan	Feb	Mar	April
2	Pots	210	160	190	110
3	Pans	230	250	220	240
4	Sticks	250	110	180	150
5	stones	160	250	230	140
2014-2015

Excel 2012
	A	B	C	D	E
1		Jan	Feb	Mar	April
2	Pots	-30	-90	10	-90
3	Sticks	0	-110	50	10
4	Elephants
5	Lettuce
Diff

Cell Formulas
Range	Formula
B2	=IFERROR(VLOOKUP($A2,'2015-2016'!$A:$E,MATCH(B$1,'2015-2016'!$A$1:$E$1,0),0) -VLOOKUP($A2,'2014-2015'!$A:$E,MATCH(B$1,'2014-2015'!$A$1:$E$1,0),0),"")

 

[jimsallyiona]

try this

Excel 2012

	A	B	C	D	E
Jan	Feb	Mar	April
Pots
Sticks
Elephants
Lettuce

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]140[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]160[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]160[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]250[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]230[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]140[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]130[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]230[/TD]
[TD="align: right"]240[/TD]

</tbody>

2015-2016​

Excel 2012

	A	B	C	D	E
Jan	Feb	Mar	April
Pots
Pans
Sticks
stones

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]210[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]110[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]230[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]240[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]250[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]150[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]160[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]230[/TD]
[TD="align: right"]140[/TD]

</tbody>

2014-2015​

Excel 2012

	A	B	C	D	E
Jan	Feb	Mar	April
Pots
Sticks
Elephants
Lettuce

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]-30[/TD]
[TD="align: right"]-90[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]-90[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]-110[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

</tbody>

Diff​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A2,'2015-2016'!$A:$E,MATCH(B$1,'2015-2016'!$A$1:$E$1,0),0)
-VLOOKUP($A2,'2014-2015'!$A:$E,MATCH(B$1,'2014-2015'!$A$1:$E$1,0),0),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi,
Thank you for this I will give it a try. If you have a sec could you explain a little to help me learn?
Jim