john_luczkowski
New Member
- Joined
- Dec 5, 2016
- Messages
- 3
So - here's my scenario. Running reports on new hires in my company. Need to show their sales performance after 30,60,90 days. Unfortunately all i received was some crappy raw data. I'll give you and sample and then explain what I want.
<tbody>
</tbody>
So that's what I'm dealing with above.
The file came with columns A through H.
I added in I manually. ? Question? - For future reference is there a way to do column I through a formula that would search for the person's id number, identify each instance of the id number, and then check the rows to see what the first date in B is? So if it wasn't 1/21 (or whatever date I want to add) if would say Yes (or Y) and then repeat for each row where that person was, then go down to the next id and repeat. The issue is each person doesn't have the same amount of rows b/c some are new hires, some don't last the whole year and leave, etc. So some people have 12 rows, others 5, some 1
Same thing for Column J - except it would be looking to see what the last entry is in column b for each id and if it's not 12/21/16 - then it comes up as N and Y if it is. I think maybe for both of these a VLOOKUP with a nested If formula
Big ask - for columns L, M, N - i want to pull their performance at 30, 60, and 90 days for new hires. So that would be the second pay cycle, the 3rd, and the 4th. Any thoughts on how to get that?
| A | B | C | D | E | F | G | H | I | J | K | L | M | N |
| ID# | Payroll Date | Location | Name | Sales # | #Calls | Sales % | Division | New Hire | Active | Start Cycle | 30 Day | 60 Day | 90 Day |
| 1001 | 1/21/16 | Washington | Tom Ford | 50 | 100 | 50% | West | N | Y | ||||
| 1001 | 2/21/16 | Oregon | Tom Ford | 75 | 100 | 75% | West | N | Y | ||||
| 1002 | 03/21/16 | Washington | Frank Thomas | 25 | 75 | 33% | West | Y | Y | 3/21/16 | |||
| 1002 | 04/21/16 | Washington | Frank Thomas | 30 | 100 | 30% | West | Y | Y | 3/21/16 | |||
| 1002 | 05/21/16 | Washington | Frank Thomas | 40 | 100 | 40% | West | Y | Y | 3/21/16 | |||
| 1002 | 06/21/16 | Washington | Frank Thomas | 50 | 100 | 50% | West | Y | Y | 3/21/16 |
<tbody>
</tbody>
So that's what I'm dealing with above.
The file came with columns A through H.
I added in I manually. ? Question? - For future reference is there a way to do column I through a formula that would search for the person's id number, identify each instance of the id number, and then check the rows to see what the first date in B is? So if it wasn't 1/21 (or whatever date I want to add) if would say Yes (or Y) and then repeat for each row where that person was, then go down to the next id and repeat. The issue is each person doesn't have the same amount of rows b/c some are new hires, some don't last the whole year and leave, etc. So some people have 12 rows, others 5, some 1
Same thing for Column J - except it would be looking to see what the last entry is in column b for each id and if it's not 12/21/16 - then it comes up as N and Y if it is. I think maybe for both of these a VLOOKUP with a nested If formula
Big ask - for columns L, M, N - i want to pull their performance at 30, 60, and 90 days for new hires. So that would be the second pay cycle, the 3rd, and the 4th. Any thoughts on how to get that?
