Hello,
I have a large spreadsheet which contains multiple rows of rate data by employee. I have sorted by effective date with most recent date on the top to get the most recent rate of pay, however, is there a way or formula to be able to identify or extract the previous rate of pay after that? I have included a sample below, any guidance or info would be greatly appreciated. There are different #'s of rows of rate of pay based on how long someone has been in company, as well as different rates of pay and effective dates.
<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>
I have a large spreadsheet which contains multiple rows of rate data by employee. I have sorted by effective date with most recent date on the top to get the most recent rate of pay, however, is there a way or formula to be able to identify or extract the previous rate of pay after that? I have included a sample below, any guidance or info would be greatly appreciated. There are different #'s of rows of rate of pay based on how long someone has been in company, as well as different rates of pay and effective dates.
Employee #</SPAN> | Name</SPAN> | Rate of Pay</SPAN> | Eff Date</SPAN> |
655220</SPAN> | John Smith</SPAN> | $ 99,000.00 </SPAN> | 1/1/2014</SPAN> |
655220</SPAN> | John Smith</SPAN> | $ 95,000.00 </SPAN> | 1/1/2013</SPAN> |
655220</SPAN> | John Smith</SPAN> | $ 97,000.00 </SPAN> | 1/1/2012</SPAN> |
722109</SPAN> | Jane Smith</SPAN> | $ 75,000.00 </SPAN> | 1/1/2011</SPAN> |
722109</SPAN> | Jane Smith</SPAN> | $ 70,000.00 </SPAN> | 1/1/2010</SPAN> |
555444</SPAN> | Ken Jones</SPAN> | $ 40,000.00 </SPAN> | 1/1/2014</SPAN> |
555444</SPAN> | Ken Jones</SPAN> | $ 38,000.00 </SPAN> | 5/1/2013</SPAN> |
555444</SPAN> | Ken Jones</SPAN> | $ 36,000.00 </SPAN> | 6/1/2012</SPAN> |
555444</SPAN> | Ken Jones</SPAN> | $ 34,000.00 </SPAN> | 7/1/2011</SPAN> |
555444</SPAN> | Ken Jones</SPAN> | $ 32,000.00 </SPAN> | 8/1/2010</SPAN> |
789444</SPAN> | Mike Adams</SPAN> | $ 95,000.00 </SPAN> | 1/1/2014</SPAN> |
111222</SPAN> | Walter Matthews</SPAN> | $ 80,000.00 </SPAN> | 1/1/2014</SPAN> |
111222</SPAN> | Walter Matthews</SPAN> | $ 75,000.00 </SPAN> | 2/11/2013</SPAN> |
111222</SPAN> | Walter Matthews</SPAN> | $ 72,000.00 </SPAN> | 3/24/2012</SPAN> |
111222</SPAN> | Walter Matthews</SPAN> | $ 68,000.00 </SPAN> | 4/19/2011</SPAN> |
239448</SPAN> | Charles Hughes</SPAN> | $ 120,000.00 </SPAN> | 1/1/2014</SPAN> |
239448</SPAN> | Charles Hughes</SPAN> | $ 110,000.00 </SPAN> | 2/19/2013</SPAN> |
239448</SPAN> | Charles Hughes</SPAN> | $ 100,000.00 </SPAN> | 3/22/2012</SPAN> |
239448</SPAN> | Charles Hughes</SPAN> | $ 90,000.00 </SPAN> | 4/8/2011</SPAN> |
239448</SPAN> | Charles Hughes</SPAN> | $ 80,000.00 </SPAN> | 5/6/2010</SPAN> |
239448</SPAN> | Charles Hughes</SPAN> | $ 70,000.00 </SPAN> | 6/8/2009</SPAN> |
239448</SPAN> | Charles Hughes</SPAN> | $ 60,000.00 </SPAN> | 7/29/2008</SPAN> |
239448</SPAN> | Charles Hughes</SPAN> | $ 50,000.00 </SPAN> | 8/10/2007</SPAN> |
239448</SPAN> | Charles Hughes</SPAN> | $ 40,000.00 </SPAN> | 9/15/2006</SPAN> |
239448</SPAN> | Charles Hughes</SPAN> | $ 30,000.00 </SPAN> | 10/12/2005</SPAN> |
<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>