showing one info from a historical dataset

C

confused in Frankfurt

Board Regular
Joined
Oct 11, 2010
Messages
53
Hello,
I have a range of historical data which shows all changes made to a data set. I want to select the last modified data set per opportunity. ie what I have manually marked in the selected column. This dataset should be the status of the data to a specific point in time. i.e. as though I looked at the data on the 01.04.2010.

I have not managed to create this the data set is sorted according to Opportunity ID and then Last modified date.
<table border="0" cellpadding="0" cellspacing="0" width="317"><tbody><tr style="height: 16.5pt;" height="22"><td class="xl22" style="height: 16.5pt; width: 98pt;" height="22" width="131">Opportunity ID</td> <td class="xl22" style="width: 74pt;" width="98">Last Modified</td> <td class="xl25" style="width: 66pt;" width="88">select</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl23" style="height: 16.5pt; width: 98pt;" height="22" width="131">00620000000qXlg</td> <td class="xl24" style="width: 74pt;" width="98">11.12.2009</td> <td>
</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl23" style="height: 16.5pt; width: 98pt;" height="22" width="131">00620000000qXlg</td> <td class="xl24" style="width: 74pt;" width="98">11.12.2009</td> <td>
</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl23" style="height: 16.5pt; width: 98pt;" height="22" width="131">00620000000qXlg</td> <td class="xl24" style="width: 74pt;" width="98">11.12.2009</td> <td>
</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl23" style="height: 16.5pt; width: 98pt;" height="22" width="131">00620000000qXlg</td> <td class="xl24" style="width: 74pt;" width="98">11.12.2009</td> <td>YES</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl23" style="height: 16.5pt; width: 98pt;" height="22" width="131">00620000000qXlg</td> <td class="xl24" style="width: 74pt;" width="98">06.09.2010</td> <td>
</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl23" style="height: 16.5pt; width: 98pt;" height="22" width="131">00620000000qXlg</td> <td class="xl24" style="width: 74pt;" width="98">28.01.2011</td> <td>
</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl23" style="height: 16.5pt; width: 98pt;" height="22" width="131">00620000000qXlg</td> <td class="xl24" style="width: 74pt;" width="98">28.01.2011</td> <td>
</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl23" style="height: 16.5pt; width: 98pt;" height="22" width="131">00620000000qXlg</td> <td class="xl24" style="width: 74pt;" width="98">22.02.2011</td> <td>
</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl23" style="height: 16.5pt; width: 98pt;" height="22" width="131">00620000001GUL5</td> <td class="xl24" style="width: 74pt;" width="98">01.12.2008</td> <td>
</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl23" style="height: 16.5pt; width: 98pt;" height="22" width="131">00620000001GUL5</td> <td class="xl24" style="width: 74pt;" width="98">09.12.2009</td> <td>YES</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl23" style="height: 16.5pt; width: 98pt;" height="22" width="131">00620000001GUL5</td> <td class="xl24" style="width: 74pt;" width="98">19.11.2010</td> <td>
</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl23" style="height: 16.5pt; width: 98pt;" height="22" width="131">00620000001o59g</td> <td class="xl24" style="width: 74pt;" width="98">07.12.2009</td> <td>YES</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl23" style="height: 16.5pt; width: 98pt;" height="22" width="131">00620000001refB</td> <td class="xl24" style="width: 74pt;" width="98">07.12.2009</td> <td>YES</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl23" style="height: 16.5pt; width: 98pt;" height="22" width="131">00620000002iq5U</td> <td class="xl24" style="width: 74pt;" width="98">07.12.2009</td> <td>YES</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl23" style="height: 16.5pt; width: 98pt;" height="22" width="131">00620000002iqK5</td> <td class="xl24" style="width: 74pt;" width="98">07.12.2009</td> <td>YES</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl23" style="height: 16.5pt; width: 98pt;" height="22" width="131">00620000002jX2s</td> <td class="xl24" style="width: 74pt;" width="98">07.12.2009</td> <td>
</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl23" style="height: 16.5pt; width: 98pt;" height="22" width="131">00620000002jX2s</td> <td class="xl24" style="width: 74pt;" width="98">29.09.2010</td> <td>YES</td> </tr> </tbody></table>
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
mat be this will do what you want
Excel Workbook
ABCDEFG
1Opportunity IDLast ModifiedselectOpportunity IDLast Modified
200620000000qXlg11.12.200900620000000qXlg22.02.2011
300620000000qXlg11.12.200900620000001GUL519.11.2010
400620000000qXlg11.12.200900620000001o59g07.12.2009
500620000000qXlg11.12.2009YES00620000001refB07.12.2009
600620000000qXlg06.09.201000620000002iq5U07.12.2009
700620000000qXlg28.01.201100620000002iqK507.12.2009
800620000000qXlg28.01.201100620000002jX2s29.09.2010
900620000000qXlg22.02.2011
1000620000001GUL501.12.2008
1100620000001GUL509.12.2009YES
1200620000001GUL519.11.2010
1300620000001o59g07.12.2009YES
1400620000001refB07.12.2009YES
1500620000002iq5U07.12.2009YES
1600620000002iqK507.12.2009YES
1700620000002jX2s07.12.2009
1800620000002jX2s29.09.2010YES
Sheet3
Excel 2007
Cell Formulas
RangeFormula
G2=LOOKUP(2,1/($A$2:$A$18=F2),$B$2:$B$18)
 
Upvote 0
Sorry
if you mean the last Modification before 1/4/2010 try this
Excel Workbook
ABCDEFG
1Opportunity IDLast ModifiedselectOpportunity IDLast Modified
200620000000qXlg11/12/200900620000000qXlg11/12/2009
300620000000qXlg11/12/200900620000001GUL509/12/2009
400620000000qXlg11/12/200900620000001o59g07/12/2009
500620000000qXlg11/12/2009YES00620000001refB07/12/2009
600620000000qXlg06/09/201000620000002iq5U07/12/2009
700620000000qXlg28/01/201100620000002iqK507/12/2009
800620000000qXlg28/01/201100620000002jX2s07/12/2009
900620000000qXlg22/02/2011
1000620000001GUL501/12/2008
1100620000001GUL509/12/2009YES
1200620000001GUL519/11/2010
1300620000001o59g07/12/2009YES
1400620000001refB07/12/2009YES
1500620000002iq5U07/12/2009YES
1600620000002iqK507/12/2009YES
1700620000002jX2s07/12/2009
1800620000002jX2s29/09/2010YES
Sheet3
Excel 2007
Cell Formulas
RangeFormula
G2=LOOKUP(2,1/(($A$2:$A$18=F2)*($B$2:$B$18<=DATE(2010,4,1))),$B$2:$B$18)
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
P
  • Question Question
Copy description from one cell to another
Replies
5
Views
712
Panoos64
P
I
  • Question Question
Pull rates from another sheet in work book
Replies
1
Views
681
jtakw
jtakw
P
  • Question Question
Drop Down List
Replies
0
Views
478
Panoos64
P
P
  • Question Question
Transfer data from one file to another (matching code and column's text)
Replies
1
Views
544
Panoos64
P

Forum statistics

Threads
1,224,521
Messages
6,179,291
Members
452,902
Latest member
Knuddeluff

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