Hi all,

I've encountered a problem which I can't solve in Excel. Can someone
please help with this?

I have 2 columns: A and B. A contains Year information, while column B
contains Losses, e.g.:

Year Losses
1980 45
1980 500
1980 6,500
1981 1,002
1981 2,150,000
1981 6,500,000
1982 1,350,000
1982 490,650
1982 950,000
1983 56,000
1983 656,580
Now here's what I need - as you can see, the same year can appear more
than once. This means that more loss driving events happen in that
year. So, I need to look in every year in top to bottom order (without
going back up the cells) and satisfy the following conditions:

1. If a loss greater than $1,000,000 occurs, make the adjacent cell in
the next column C have a max loss of 1,000,000. Once that condition is
met, you got to step 2

2. Now that $1,000,000 loss has occured, the next loss in that SAME
year greater than 500,000 should have in an adjacent cell in the next
column C a max loss of 500,000. If that does happen, we go to step 3...

3. Once you have used up the $1,000,000 and $500,000 max numbers in the
SAME year, the next one to look out for is if there is a loss greater
than $75,000 and then put a max loss of 75,000 into the adjacent cell
in column c. Once this happens you stop doing that for that particular
year.

e.g.:

In 1981 above, the first loss greater than 1,000,000 (step 1) is
2,150,000, so right next to that cell, I want to place a max loss of
1,000,000. Go down the list and find the next loss in 1981 greater than
500,000 (step 2) - that corresponds to 6,500,000. Place max loss of
500,000 right next to that cell. Now, 1981 ends, so you have to start
the process again for 1982 by looking for 1,000,000 loss. It should
look like this for 1982:

Year Losses Column C
1982 1,350,000 1,000,000
1982 490,650
1982 950,000 500,000
1983 56,000
1983 656,580 75,000



Sorry for the long winded explanation, but I hope this illustrates what
I am looking for. Any or all help will be greatly appreciated!

Thanks!