filter data, latest date rate

F

faizee

Board Regular
Joined
Jan 28, 2009
Messages
214
Office Version
  1. 2016
Platform
  1. Windows
[TABLE="width: 568"]
<TBODY>[TR]
[TD]S#[/TD]
[TD]Product[/TD]
[TD]Shipment Date[/TD]
[TD]Rate[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]xyx[/TD]
[TD="align: right"]5-Jan-12[/TD]
[TD="align: right"]221[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]zzz[/TD]
[TD="align: right"]5-Jan-12[/TD]
[TD="align: right"]455[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]abc[/TD]
[TD="align: right"]5-Jan-12[/TD]
[TD="align: right"]5666[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]zzz[/TD]
[TD="align: right"]5-Feb-12[/TD]
[TD="align: right"]323[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]abc[/TD]
[TD="align: right"]5-Apr-12[/TD]
[TD="align: right"]1200[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]xyx[/TD]
[TD="align: right"]5-Apr-12[/TD]
[TD="align: right"]999[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]abc[/TD]
[TD="align: right"]5-May-12[/TD]
[TD="align: right"]333[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]abc[/TD]
[TD="align: right"]5-Jul-12[/TD]
[TD="align: right"]944[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]xyx[/TD]
[TD="align: right"]5-Aug-12[/TD]
[TD="align: right"]222[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]abc[/TD]
[TD="align: right"]5-Nov-12[/TD]
[TD="align: right"]123[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Enter Product[/TD]
[TD]Latest Shipment rate[/TD]
[TD]Latest Shipment Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]abc[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]when we enter product, then I should give the last shipment rate. And the shipment date[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]for example.:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]product[/TD]
[TD]rate[/TD]
[TD]shipment date[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]abc[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]5-Nov-12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]xyx[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]5-Aug-12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]zzz[/TD]
[TD="align: right"]323[/TD]
[TD="align: right"]5-Feb-12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
faizee said:
[TABLE="width: 568"]
<tbody>[TR]
[TD]S#
[/TD]
[TD]Product
[/TD]
[TD]Shipment Date
[/TD]
[TD]Rate
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]xyx
[/TD]
[TD="align: right"]5-Jan-12
[/TD]
[TD="align: right"]221
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]zzz
[/TD]
[TD="align: right"]5-Jan-12
[/TD]
[TD="align: right"]455
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]abc
[/TD]
[TD="align: right"]5-Jan-12
[/TD]
[TD="align: right"]5666
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]zzz
[/TD]
[TD="align: right"]5-Feb-12
[/TD]
[TD="align: right"]323
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]abc
[/TD]
[TD="align: right"]5-Apr-12
[/TD]
[TD="align: right"]1200
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]xyx
[/TD]
[TD="align: right"]5-Apr-12
[/TD]
[TD="align: right"]999
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]abc
[/TD]
[TD="align: right"]5-May-12
[/TD]
[TD="align: right"]333
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]abc
[/TD]
[TD="align: right"]5-Jul-12
[/TD]
[TD="align: right"]944
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]xyx
[/TD]
[TD="align: right"]5-Aug-12
[/TD]
[TD="align: right"]222
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]abc
[/TD]
[TD="align: right"]5-Nov-12
[/TD]
[TD="align: right"]123
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Enter Product
[/TD]
[TD]Latest Shipment rate
[/TD]
[TD]Latest Shipment Date
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]abc
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]when we enter product, then I should give the last shipment rate. And the shipment date
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]for example.:
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]product
[/TD]
[TD]rate
[/TD]
[TD]shipment date
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]abc
[/TD]
[TD="align: right"]123
[/TD]
[TD="align: right"]5-Nov-12
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]xyx
[/TD]
[TD="align: right"]222
[/TD]
[TD="align: right"]5-Aug-12
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]zzz
[/TD]
[TD="align: right"]323
[/TD]
[TD="align: right"]5-Feb-12
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

G2, control+shift+enter, not just enter:

=MAX(IF($B$2:$B$11=$F2,$D$2:$D$11)

H2, control+shift+enter, not just enter:

=MAX(IF($B$2:$B$11=$F2,$C$2:$C$11)

where F2 = abc.
 
Upvote 0
[TABLE="width: 1027"]
<TBODY>[TR]
[TD]Enter Product</SPAN>[/TD]
[TD]Latest Shipment rate</SPAN>[/TD]
[TD]Latest Shipment Date</SPAN>[/TD]
[/TR]
[TR]
[TD]abc</SPAN>[/TD]
[TD]=INDIRECT("C" & SUM(MAX(IF($B:$B=F2,ROW($B:$B),0))))</SPAN>[/TD]
[TD]=INDIRECT("D" & SUM(MAX(IF($B:$B=$F2,ROW($B:$B),0))))</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

USe this formula and its array formula so use ctrl+shift+enter where column B is your Product column and F2 is your Enter Product
 
Upvote 0
Dear Sir,
Problems is not solved yet,
I have two criteria,
1. Product
2. Latest date of shipment

for example.
if products is "abc", then its latest shipment date is 5-nov-12, so the answer should be 123
if product is "xyx", then its latest shipment date is 5-aug-12. so the answer should be 222
 
Upvote 0
faizee said:
Dear Sir,
Problems is not solved yet,
I have two criteria,
1. Product
2. Latest date of shipment

for example.
if products is "abc", then its latest shipment date is 5-nov-12, so the answer should be 123
if product is "xyx", then its latest shipment date is 5-aug-12. so the answer should be 222
Click to expand...

F2: abc

G2, control+shift+enter, not just enter:

=MAX(IF($B$2:$B$11=$F2,$C$2:$C$11)

which yields the latest shipment date for the product in F2.

H2, control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,IF($B$2:$B$13=$F2,IF($C$2:$C$13=$G2,$D$2:$D$13)))
 
Upvote 0
faizee said:
[TABLE="width: 568"]
<TBODY>[TR]
[TD]S#
[/TD]
[TD]Product
[/TD]
[TD]Shipment Date
[/TD]
[TD]Rate
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]xyx
[/TD]
[TD="align: right"]5-Jan-12
[/TD]
[TD="align: right"]221
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]zzz
[/TD]
[TD="align: right"]5-Jan-12
[/TD]
[TD="align: right"]455
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]abc
[/TD]
[TD="align: right"]5-Jan-12
[/TD]
[TD="align: right"]5666
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]zzz
[/TD]
[TD="align: right"]5-Feb-12
[/TD]
[TD="align: right"]323
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]abc
[/TD]
[TD="align: right"]5-Apr-12
[/TD]
[TD="align: right"]1200
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]xyx
[/TD]
[TD="align: right"]5-Apr-12
[/TD]
[TD="align: right"]999
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]abc
[/TD]
[TD="align: right"]5-May-12
[/TD]
[TD="align: right"]333
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]abc
[/TD]
[TD="align: right"]5-Jul-12
[/TD]
[TD="align: right"]944
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]xyx
[/TD]
[TD="align: right"]5-Aug-12
[/TD]
[TD="align: right"]222
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]abc
[/TD]
[TD="align: right"]5-Nov-12
[/TD]
[TD="align: right"]123
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Enter Product
[/TD]
[TD]Latest Shipment rate
[/TD]
[TD]Latest Shipment Date
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]abc
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]when we enter product, then I should give the last shipment rate. And the shipment date
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]for example.:
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]product
[/TD]
[TD]rate
[/TD]
[TD]shipment date
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]abc
[/TD]
[TD="align: right"]123
[/TD]
[TD="align: right"]5-Nov-12
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]xyx
[/TD]
[TD="align: right"]222
[/TD]
[TD="align: right"]5-Aug-12
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]zzz
[/TD]
[TD="align: right"]323
[/TD]
[TD="align: right"]5-Feb-12
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
Click to expand...
Try this...

Data:

Book1
ABCD
1S#ProductShipment DateRate
23xyx5-Jan-12221
39zzz5-Jan-12455
410abc5-Jan-125666
57zzz5-Feb-12323
61abc5-Apr-121200
75xyx5-Apr-12999
82abc5-May-12333
94abc5-Jul-12944
106xyx5-Aug-12222
118abc5-Nov-12123
Sheet1

Summary:

Book1
FGH
1ProductRateShipment Date
2xyx2225-Aug-12
3abc1235-Nov-12
4zzz3235-Feb-12
Sheet1

Enter this array formula** in G2:

=INDEX(D$2:D$11,MATCH(H2,IF(B$2:B$11=F2,C$2:C$11),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Enter this formula in H2:

=LOOKUP(2,1/(B$2:B$11=F2),C$2:C$11)

Select G2:H2 and copy down as needed.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Latest Data Pull-up from the corresponding date-Single party
Replies
3
Views
124
Sergius
S
S
  • Solved
lookup return from range of dates
Replies
4
Views
496
Peter_SSs
Peter_SSs
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
2K
phiero21
P
B
  • Solved
IF dates
Replies
9
Views
301
BrutalLogiC
B
B
  • Question Question
Copying data from a year tab and pasting into month tabs
Replies
2
Views
531
B-radK
B

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

Share this page

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
Back
Top