Comparing changes in 2 sets of data

[halfgeek]

Hi there,

I have two sets of data that I need to show if there has been an increase or decrease in over a 2 year period so at present have something that looks like this:

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Year 1[/TD]
[TD][/TD]
[TD]Year 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Postcode[/TD]
[TD]Number[/TD]
[TD]Postcode[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]SY23 7[/TD]
[TD]2875[/TD]
[TD]SY66 2[/TD]
[TD]2890[/TD]
[/TR]
[TR]
[TD]ST65 8[/TD]
[TD]2700[/TD]
[TD]SY23 7[/TD]
[TD]2611[/TD]
[/TR]
[TR]
[TD]SY66 2[/TD]
[TD]2602[/TD]
[TD]ST9 6[/TD]
[TD]2400[/TD]
[/TR]
</TBODY>[/TABLE]


I need to show, for example, that SY66 2 has gone from 3rd in year one to 1st in year two.

Can anyone help at all please?


Its the top 10 postcodes which isn't alot, but I have to do this for about 60 bases so if I can find a formula or something that works my job will be much easier (especially as this will be a monthly occurence).

Thanks very much in advance.

 

[Bairn90]

Hi,

Not being 100% sure what you want your output to look like or the structure of your workbook I've quickly hashed together the following formula assuming Year1 is in A1, Postcode in A2 with the data actually beginning in A3.

=IFERROR(INDEX(ROW(A:A)-2,MATCH(C3,A:A,0))&" to "&ROW(C3)-2,"not previously found")

The output would then look like "1 to 2" for "SY23 7" & "3 to 1" for "SY66 2"

I've put this next to the data in year 2 looking up the postcode in year 1. It outputs the row in which is found the data in year 1 - 2 as for the Year1 and Postcode that are in the row above will give you the correct position the postcode is in. I've added in an if error not previously found just to tidy it up just in case but it may not be needed for what you want.

I think that should do want. Let me know if not.

[TABLE="width: 390"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[halfgeek]

Thank you Bairn. I am not, yet, terribly good with nested formulas but I have figured out what this is doing and it will do the job nicely. Ultimately (because I know my boss) I am going to need to somehow have conditional formatting with little up and down arrows to make it appealing to the eye, but this is great so thank you very much indeed.