Jon von der Heyden
MrExcel MVP, Moderator
- Joined
- Apr 6, 2004
- Messages
- 10,907
- Office Version
- 365
- Platform
- Windows
Hi,
I have a list that needs to be sorting in ascending order. It's made up of three columns {Period;Code;Rate}
I need to return the latest rate for each code i.e. from the last period. I initially used a pivot table but then I discovered that the last period doesn't necessarily include all codes, so I would need to refer to the period prior to that if omitted.
Any suggestions?
I have a list that needs to be sorting in ascending order. It's made up of three columns {Period;Code;Rate}
I need to return the latest rate for each code i.e. from the last period. I initially used a pivot table but then I discovered that the last period doesn't necessarily include all codes, so I would need to refer to the period prior to that if omitted.
Any suggestions?
Book1 | ||||||||
---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | |||
2 | Period | Code | Rate | Sum of Rate | Period | |||
3 | Aug-05 | 1 | -16 | Code | Oct-05 | |||
4 | Aug-05 | 2 | -17 | 1 | -24 | |||
5 | Aug-05 | 4 | -18 | 2 | -25 | |||
6 | Sep-05 | 1 | -19 | 4 | -26 | |||
7 | Sep-05 | 2 | -20 | 5 | -27 | |||
8 | Sep-05 | 3 | -21 | |||||
9 | Sep-05 | 4 | -22 | |||||
10 | Sep-05 | 5 | -23 | |||||
11 | Oct-05 | 1 | -24 | |||||
12 | Oct-05 | 2 | -25 | |||||
13 | Oct-05 | 4 | -26 | |||||
14 | Oct-05 | 5 | -27 | |||||
Sheet1 |