Extracting Specific Data from Large spreadsheet

[itjr212]

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.

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>

 

[itjr212]

I actually figured one possible way. I sorted effective date by newest to oldest and then added the formula in a separate column - manually typing first cell in that column (E2) as 1 and then on the cell below it typing the following formula =if(Employee ID which is A2=A1,E2+1,1) that would give you a count in that column of how many rows of data in order you have for a specific person (see example below) and then I just filtered on 1 and 2, and I am able to get the current and prev rates of pay.

Employee #</SPAN>	Name</SPAN>	Rate of Pay</SPAN>	Eff Date</SPAN>	# of Rates in date order</SPAN>
655220</SPAN>	John Smith</SPAN>	$ 99,000.00 </SPAN>	1/1/2014</SPAN>	1</SPAN>
655220</SPAN>	John Smith</SPAN>	$ 95,000.00 </SPAN>	1/1/2013</SPAN>	2</SPAN>
655220</SPAN>	John Smith</SPAN>	$ 97,000.00 </SPAN>	1/1/2012</SPAN>	3</SPAN>
722109</SPAN>	Jane Smith</SPAN>	$ 75,000.00 </SPAN>	1/1/2011</SPAN>	1</SPAN>
722109</SPAN>	Jane Smith</SPAN>	$ 70,000.00 </SPAN>	1/1/2010</SPAN>	2</SPAN>
555444</SPAN>	Ken Jones</SPAN>	$ 40,000.00 </SPAN>	1/1/2014</SPAN>	1</SPAN>
555444</SPAN>	Ken Jones</SPAN>	$ 38,000.00 </SPAN>	5/1/2013</SPAN>	2</SPAN>
555444</SPAN>	Ken Jones</SPAN>	$ 36,000.00 </SPAN>	6/1/2012</SPAN>	3</SPAN>
555444</SPAN>	Ken Jones</SPAN>	$ 34,000.00 </SPAN>	7/1/2011</SPAN>	4</SPAN>
555444</SPAN>	Ken Jones</SPAN>	$ 32,000.00 </SPAN>	8/1/2010</SPAN>	5</SPAN>
789444</SPAN>	Mike Adams</SPAN>	$ 95,000.00 </SPAN>	1/1/2014</SPAN>	1</SPAN>
111222</SPAN>	Walter Matthews</SPAN>	$ 80,000.00 </SPAN>	1/1/2014</SPAN>	1</SPAN>
111222</SPAN>	Walter Matthews</SPAN>	$ 75,000.00 </SPAN>	2/11/2013</SPAN>	2</SPAN>
111222</SPAN>	Walter Matthews</SPAN>	$ 72,000.00 </SPAN>	3/24/2012</SPAN>	3</SPAN>
111222</SPAN>	Walter Matthews</SPAN>	$ 68,000.00 </SPAN>	4/19/2011</SPAN>	4</SPAN>
239448</SPAN>	Charles Hughes</SPAN>	$ 120,000.00 </SPAN>	1/1/2014</SPAN>	1</SPAN>
239448</SPAN>	Charles Hughes</SPAN>	$ 110,000.00 </SPAN>	2/19/2013</SPAN>	2</SPAN>
239448</SPAN>	Charles Hughes</SPAN>	$ 100,000.00 </SPAN>	3/22/2012</SPAN>	3</SPAN>
239448</SPAN>	Charles Hughes</SPAN>	$ 90,000.00 </SPAN>	4/8/2011</SPAN>	4</SPAN>
239448</SPAN>	Charles Hughes</SPAN>	$ 80,000.00 </SPAN>	5/6/2010</SPAN>	5</SPAN>
239448</SPAN>	Charles Hughes</SPAN>	$ 70,000.00 </SPAN>	6/8/2009</SPAN>	6</SPAN>
239448</SPAN>	Charles Hughes</SPAN>	$ 60,000.00 </SPAN>	7/29/2008</SPAN>	7</SPAN>
239448</SPAN>	Charles Hughes</SPAN>	$ 50,000.00 </SPAN>	8/10/2007</SPAN>	8</SPAN>
239448</SPAN>	Charles Hughes</SPAN>	$ 40,000.00 </SPAN>	9/15/2006</SPAN>	9</SPAN>
239448</SPAN>	Charles Hughes</SPAN>	$ 30,000.00 </SPAN>	10/12/2005</SPAN>	10</SPAN>

<TBODY>
</TBODY>

 

[Joyner]

Hello,

This formula may be better to get the count, is cell E2 copied down:

=COUNTIF($A$2:A2,A2)

This will allow you to sort is different orders and still get the count. As long as you first sort on either, date, or pay, high to low, then you can sort on the Employee Id column and still get the corect number.

What do you need to get for your requirement. Do you just need to identify the current and previous pay rates?

 

[Joyner]

Hello again,

here is a formula that will identify the current pay (1) and all other pay rates numbers regardless of sort:

=SUMPRODUCT(($A$2:$A$1030=A2)*(D2<=$D$2:$D$1030))