Ordering Lists

[PGalla06]

I'm trying to write code to compare revenue data by state year over year. For purposes of my analysis, I need the states sorted in descending order by revenue for the most recent year, then each prior year needs to be sorted in the same order. So, if I receive data as such:

2005

MA $1000
TN $950
IL $800
MN $300


2004

IL $5000
MN $300
TN $200
MA $50

I needed to sort the 2004 revenue data, so that the states are listed in the same ordered as 2005, as seen below:

2004

MA $50
TN $200
IL $5000
MN $300

Does anyone know an efficient way to do this? I feel like their must be a way to store all this data in one array and then build a table using the array.

Any help would be greatly appreciated.

Thanks,
Peter

[Gary''s Student]

Once you have sorted the states by revenue descending, insert a helper column
next to it in ascending order:

MA $1000 1
TN $950 2
IL $800 3
MN $300 4

The helper columns of all the other years would be composed of VLOOKUP()s to
the master year which , for 2004, should result in:

IL $5000 3
MN $300 4
TN $200 2
MA $50 1

Then just sort by the helper column for each year.
--
Gary''s Student


"PGalla06" wrote:

>
> I'm trying to write code to compare revenue data by state year over
> year. For purposes of my analysis, I need the states sorted in
> descending order by revenue for the most recent year, then each prior
> year needs to be sorted in the same order. So, if I receive data as
> such:
>
> 2005
>
> MA $1000
> TN $950
> IL $800
> MN $300
>
>
> 2004
>
> IL $5000
> MN $300
> TN $200
> MA $50
>
> I needed to sort the 2004 revenue data, so that the states are listed
> in the same ordered as 2005, as seen below:
>
> 2004
>
> MA $50
> TN $200
> IL $5000
> MN $300
>
> Does anyone know an efficient way to do this? I feel like their must
> be a way to store all this data in one array and then build a table
> using the array.
>
> Any help would be greatly appreciated.
>
> Thanks,
> Peter
>
>
> --
> PGalla06
> ------------------------------------------------------------------------
> PGalla06's Profile: http://www.excelforum.com/member.php...o&userid=24260
> View this thread: http://www.excelforum.com/showthread...hreadid=469012
>
>