Matching categorical data in two columns

[bpduggan]

I'm trying to compare how water use for a company changed year over year. My data set currently looks like this (with 893 rows):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name 2011[/TD]
[TD]Water 2011[/TD]
[TD]Name 2014[/TD]
[TD]Water 2014[/TD]
[/TR]
[TR]
[TD]Company A
[/TD]
[TD]110[/TD]
[TD]Company C[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Company C[/TD]
[TD]90[/TD]
[TD]Company B[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]50[/TD]
[TD]Company D[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]Company D[/TD]
[TD]40[/TD]
[TD]Company A[/TD]
[TD]70[/TD]
[/TR]
</tbody>[/TABLE]

I've already used the match function to see the relative position of Company A in 2011 to where it is in 2014. But I still can't figure out how to compare the Company A 2011 water value to the Company A 2014 water value. Help please and thank you.

 

[Michael M]

Why not sort the data so that Company A lines up with Company A
Then add a formula

Excel 2007
	A	B	C	D	E
1	Name 2011	Water 2011	Name 2014	Water 2014	difference
2	Company A	110	Company A	70	64%
3	Company B	50	Company B	90	180%
4	Company C	90	Company C	100	111%
5	Company D	40	Company D	80	200%
Sheet1

Cell Formulas
Range	Formula
E2	=D2/B2
E3	=D3/B3
E4	=D4/B4
E5	=D5/B5

 

[Drrellik]

using column E as a helper to sort A-Z all the companies listed in either A or C provided they are all accounted for you can put in F =(INDEX($A$1:$B$900,MATCH(E2,A:A),2))-INDEX($C$1:$D$900,MATCH(E2,C:C,),2)

HTH ~DR