Extracting Specific Data from Large spreadsheet

I

itjr212

Board Regular
Joined
Sep 15, 2009
Messages
50
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.

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

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
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.

[TABLE="width: 495"]
<TBODY>[TR]
[TD]Employee #</SPAN>[/TD]
[TD]Name</SPAN>[/TD]
[TD]Rate of Pay</SPAN>[/TD]
[TD]Eff Date</SPAN>[/TD]
[TD]# of Rates in date order</SPAN>[/TD]
[/TR]
[TR]
[TD]655220</SPAN>[/TD]
[TD]John Smith</SPAN>[/TD]
[TD] $ 99,000.00 </SPAN>[/TD]
[TD]1/1/2014</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[/TR]
[TR]
[TD]655220</SPAN>[/TD]
[TD]John Smith</SPAN>[/TD]
[TD] $ 95,000.00 </SPAN>[/TD]
[TD]1/1/2013</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[/TR]
[TR]
[TD]655220</SPAN>[/TD]
[TD]John Smith</SPAN>[/TD]
[TD] $ 97,000.00 </SPAN>[/TD]
[TD]1/1/2012</SPAN>[/TD]
[TD="align: right"]3</SPAN>[/TD]
[/TR]
[TR]
[TD]722109</SPAN>[/TD]
[TD]Jane Smith</SPAN>[/TD]
[TD] $ 75,000.00 </SPAN>[/TD]
[TD]1/1/2011</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[/TR]
[TR]
[TD]722109</SPAN>[/TD]
[TD]Jane Smith</SPAN>[/TD]
[TD] $ 70,000.00 </SPAN>[/TD]
[TD]1/1/2010</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[/TR]
[TR]
[TD]555444</SPAN>[/TD]
[TD]Ken Jones</SPAN>[/TD]
[TD] $ 40,000.00 </SPAN>[/TD]
[TD]1/1/2014</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[/TR]
[TR]
[TD]555444</SPAN>[/TD]
[TD]Ken Jones</SPAN>[/TD]
[TD] $ 38,000.00 </SPAN>[/TD]
[TD]5/1/2013</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[/TR]
[TR]
[TD]555444</SPAN>[/TD]
[TD]Ken Jones</SPAN>[/TD]
[TD] $ 36,000.00 </SPAN>[/TD]
[TD]6/1/2012</SPAN>[/TD]
[TD="align: right"]3</SPAN>[/TD]
[/TR]
[TR]
[TD]555444</SPAN>[/TD]
[TD]Ken Jones</SPAN>[/TD]
[TD] $ 34,000.00 </SPAN>[/TD]
[TD]7/1/2011</SPAN>[/TD]
[TD="align: right"]4</SPAN>[/TD]
[/TR]
[TR]
[TD]555444</SPAN>[/TD]
[TD]Ken Jones</SPAN>[/TD]
[TD] $ 32,000.00 </SPAN>[/TD]
[TD]8/1/2010</SPAN>[/TD]
[TD="align: right"]5</SPAN>[/TD]
[/TR]
[TR]
[TD]789444</SPAN>[/TD]
[TD]Mike Adams</SPAN>[/TD]
[TD] $ 95,000.00 </SPAN>[/TD]
[TD]1/1/2014</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[/TR]
[TR]
[TD]111222</SPAN>[/TD]
[TD]Walter Matthews</SPAN>[/TD]
[TD] $ 80,000.00 </SPAN>[/TD]
[TD]1/1/2014</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[/TR]
[TR]
[TD]111222</SPAN>[/TD]
[TD]Walter Matthews</SPAN>[/TD]
[TD] $ 75,000.00 </SPAN>[/TD]
[TD]2/11/2013</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[/TR]
[TR]
[TD]111222</SPAN>[/TD]
[TD]Walter Matthews</SPAN>[/TD]
[TD] $ 72,000.00 </SPAN>[/TD]
[TD]3/24/2012</SPAN>[/TD]
[TD="align: right"]3</SPAN>[/TD]
[/TR]
[TR]
[TD]111222</SPAN>[/TD]
[TD]Walter Matthews</SPAN>[/TD]
[TD] $ 68,000.00 </SPAN>[/TD]
[TD]4/19/2011</SPAN>[/TD]
[TD="align: right"]4</SPAN>[/TD]
[/TR]
[TR]
[TD]239448</SPAN>[/TD]
[TD]Charles Hughes</SPAN>[/TD]
[TD] $ 120,000.00 </SPAN>[/TD]
[TD]1/1/2014</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[/TR]
[TR]
[TD]239448</SPAN>[/TD]
[TD]Charles Hughes</SPAN>[/TD]
[TD] $ 110,000.00 </SPAN>[/TD]
[TD]2/19/2013</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[/TR]
[TR]
[TD]239448</SPAN>[/TD]
[TD]Charles Hughes</SPAN>[/TD]
[TD] $ 100,000.00 </SPAN>[/TD]
[TD]3/22/2012</SPAN>[/TD]
[TD="align: right"]3</SPAN>[/TD]
[/TR]
[TR]
[TD]239448</SPAN>[/TD]
[TD]Charles Hughes</SPAN>[/TD]
[TD] $ 90,000.00 </SPAN>[/TD]
[TD]4/8/2011</SPAN>[/TD]
[TD="align: right"]4</SPAN>[/TD]
[/TR]
[TR]
[TD]239448</SPAN>[/TD]
[TD]Charles Hughes</SPAN>[/TD]
[TD] $ 80,000.00 </SPAN>[/TD]
[TD]5/6/2010</SPAN>[/TD]
[TD="align: right"]5</SPAN>[/TD]
[/TR]
[TR]
[TD]239448</SPAN>[/TD]
[TD]Charles Hughes</SPAN>[/TD]
[TD] $ 70,000.00 </SPAN>[/TD]
[TD]6/8/2009</SPAN>[/TD]
[TD="align: right"]6</SPAN>[/TD]
[/TR]
[TR]
[TD]239448</SPAN>[/TD]
[TD]Charles Hughes</SPAN>[/TD]
[TD] $ 60,000.00 </SPAN>[/TD]
[TD]7/29/2008</SPAN>[/TD]
[TD="align: right"]7</SPAN>[/TD]
[/TR]
[TR]
[TD]239448</SPAN>[/TD]
[TD]Charles Hughes</SPAN>[/TD]
[TD] $ 50,000.00 </SPAN>[/TD]
[TD]8/10/2007</SPAN>[/TD]
[TD="align: right"]8</SPAN>[/TD]
[/TR]
[TR]
[TD]239448</SPAN>[/TD]
[TD]Charles Hughes</SPAN>[/TD]
[TD] $ 40,000.00 </SPAN>[/TD]
[TD]9/15/2006</SPAN>[/TD]
[TD="align: right"]9</SPAN>[/TD]
[/TR]
[TR]
[TD]239448</SPAN>[/TD]
[TD]Charles Hughes</SPAN>[/TD]
[TD] $ 30,000.00 </SPAN>[/TD]
[TD]10/12/2005</SPAN>[/TD]
[TD="align: right"]10</SPAN>[/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
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?
 
Upvote 0
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))
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
EXTRAPOLATE DATA FROM A TABLE!!!
Replies
6
Views
1K
JamesCanale
JamesCanale
W
  • Question Question
Extract Data from One Worksheet to Another
Replies
6
Views
1K
mumps
mumps
R
  • Question Question
Combine data from multiple worksheets to master spreadsheet
Replies
1
Views
2K
sijpie
sijpie
D
  • Question Question
How to determine effective interest rate?
Replies
8
Views
648
dtighe
D

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

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