selecting last values in a list

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,874
Office Version
  1. 365
Platform
  1. 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?
Book1
BCDEFG
2PeriodCodeRateSum of RatePeriod
3Aug-051-16CodeOct-05
4Aug-052-171-24
5Aug-054-182-25
6Sep-051-194-26
7Sep-052-205-27
8Sep-053-21
9Sep-054-22
10Sep-055-23
11Oct-051-24
12Oct-052-25
13Oct-054-26
14Oct-055-27
Sheet1
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
noj said:
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?
...
Book1
ABCDEFGHI
1
2PeriodCodeRateCodeLast RatePeriod
3Aug-051-161-24Oct-05
4Aug-052-172-25Oct-05
5Aug-054-183-21Sep-05
6Sep-051-194-26Oct-05
7Sep-052-205-27Oct-05
8Sep-053-21
9Sep-054-22CodeLast RatePeriodPos
10Sep-055-231-24Oct-059
11Oct-051-242-25Oct-0510
12Oct-052-253-21Sep-056
13Oct-054-264-26Oct-0511
14Oct-055-275-27Oct-0512
15
Sheet1


G3, copied down:

=LOOKUP(2,1/($B$3:$B$14=F3),$C$3:$C$14)

H3, copied down:

=LOOKUP(2,1/($B$3:$B$14=F3),$A$3:$A$14)

More efficient:

G10, copied down:

=INDEX($C$3:$C$14,I10)

H10, copied down:

=INDEX($A$3:$A$14,I10)

I10:

=MATCH(2,1/($B$3:$B$14=F10))

which is confirmed with control+shift+enter then copied down.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,802
Messages
5,833,755
Members
430,230
Latest member
Doug G

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top