Formula for Comparisons and Subtraction

[Glasgowsmile]

Good Morning,

I've posted a photo so you can see what I'm doing but basically...

I want to compare two columns and then subtract the numbers next to them if they match.

I want to compare column K and column N, find the matches and then subtract column L from column O to paste into column Q.

https://imgur.com/kwG55fr

 

[AlanY]

may be

Book1
	K	L	M	N	O	P	Q
1							Comparison
2	A	12		B	6		10
3	B	10		D	4		4
4	C	5		C	8		-3
5	D	9		A	2		5
6	E	8		E	5		3
7	F	7		F	1		6
8	G	3		H	1		0
9	H	6		I	7		5
10	I	5		G	3		-2
Sheet2

Cell Formulas
Range	Formula
Q2	=L2-INDEX($O$2:$O$10,MATCH(K2,$N$2:$N$10,0))

 

[Glasgowsmile]

This is great, how could I expand this if I increase the data set?

For example, let's say K:L are Week 1, N:O are Week 2. What if I wanted to add a Week 3 / 4 and it compares across all 4 weeks what the changes are?

 

[AlanY]

it's basically just copy the formula across

Book1
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1													Comparison
2	Wk1			Wk2			Wk3			Wk4			Wk1 > Wk2	Wk2 > Wk3	Wk3 > Wk4
3	A	12		B	6		A	6		B	11		10	-4	5
4	B	10		D	4		B	1		D	11		4	5	-10
5	C	5		C	8		C	2		C	5		-3	6	-3
6	D	9		A	2		D	9		A	1		5	-5	-2
7	E	8		E	5		E	6		E	1		3	-1	5
8	F	7		F	1		F	9		F	11		6	-8	-2
9	G	3		H	1		G	10		H	1		0	-7	7
10	H	6		I	7		H	12		I	10		5	-11	11
11	I	5		G	3		I	4		G	3		-2	3	-6
Sheet1

Cell Formulas
Range	Formula
V3	=INDEX($K$3:$K$11,MATCH($J3,$J$3:$J$11,0))-INDEX($N$3:$N$11,MATCH($J3,$M$3:$M$11,0))
W3	=INDEX($N$3:$N$11,MATCH($J3,$M$3:$M$11,0))-INDEX($Q$3:$Q$11,MATCH($J3,$P$3:$P$11,0))
X3	=INDEX($Q$3:$Q$11,MATCH($J3,$P$3:$P$11,0))-INDEX($T$3:$T$11,MATCH($J3,$S$3:$S$11,0))

 

[Glasgowsmile]

Not quite what I had in mind, what if I wanted a column that was Week 1 vs Week 2 Comparison and then another Column that compared Week 1 to Week 2 & 3, and then a 3rd that compared Week 1 to 2, 3, & 4?

 

[AlanY]

Not quite what I had in mind, what if I wanted a column that was Week 1 vs Week 2 Comparison and then another Column that compared Week 1 to Week 2 & 3, and then a 3rd that compared Week 1 to 2, 3, & 4?

say for A

Wk1 10
Wk2 9
Wk3 8
Wk4 7

what results are you expected for compared Week 1 to Week 2 & 3, and
Week 1 to 2, 3, & 4 ?

 

[Glasgowsmile]

For example for A, I'm trying to see how A has grown or declined over the last 3 weeks compared to week 1 - so I want to compared week 1 against all other weeks to pull A-G, in this example, so I can see how it stacks up over time.

 

[AlanY]

ok, have a look of this

Book1
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1													Comparison
2	Wk1			Wk2			Wk3			Wk4			Wk2	Wk3	Wk4
3	A	12		B	6		A	6		B	11		10	6	11
4	B	10		D	4		B	1		D	11		4	9	-1
5	C	5		C	8		C	2		C	5		-3	3	0
6	D	9		A	2		D	9		A	1		5	0	-2
7	E	8		E	5		E	6		E	1		3	2	7
8	F	7		F	1		F	9		F	11		6	-2	-4
9	G	3		H	1		G	10		H	1		0	-7	0
10	H	6		I	7		H	12		I	10		5	-6	5
11	I	5		G	3		I	4		G	3		-2	1	-5
Sheet1

Cell Formulas
Range	Formula
V3	=INDEX($K$3:$K$11,MATCH($J3,$J$3:$J$11,0))-INDEX($N$3:$N$11,MATCH($J3,$M$3:$M$11,0))
W3	=INDEX($K$3:$K$11,MATCH($J3,$J$3:$J$11,0))-INDEX($Q$3:$Q$11,MATCH($J3,$P$3:$P$11,0))
X3	=INDEX($K$3:$K$11,MATCH($J3,$J$3:$J$11,0))-INDEX($T$3:$T$11,MATCH($J3,$S$3:$S$11,0))