Help with a formuola to show me which hourly rate an employee was on when he made a sale?

[tonywatsonhelp]

Hi Everyone,

Ok really need some help here as i'm totaly stuck
Hopefully I can explain this Ok

Every week I get a list of all the sales since day one, (we have a very old crm and this is how the data comes in,
now all i have to do is put the hourly rate for each employee down column N,
but heres the problem i have some time empolyees get promoted and have more than one rate

So this i my situation,

In the raw data sheet (where i need the formula,
Coloumn A has the date sold
Column C has the employees Number,

Now in sheet "Employees Data" Column AC has the Employees Number,
Column AO Has the start date
Column AP has the end date

Now because nobody is perfect it is possible the end date is before the date sold, in which case the most recent rate applies.

So all I need is for a formula to look at the date sold and the Employees Number, go to sheet "Employees Data" find the employees name that has a date to do with the start date and return the hourly rate in column BE

If you need more info please feel free to ask as im struggling to explain this,
but basically,
find the employee, see how many rows he has, if its just one then the rate is correct, if its more than one find the one with a start date (and end date?) before and after the sale date?

please help been trying to do this all day?

Thanks

Tony

 

[Toadstool]

Hi Tony,

Having to allow for no end date and sales after end date added complexity but I believe this does what you ask.

Here's my test Employee Data:

Tonywatsonhelp.xlsx
	AC											AN	AO	AP	AQ													BD	BE
1	Employee Number												Start Date	End Date															Hourly Rate
2	EMP002												1/1/2009	2/2/2017															$ 12
3	EMP004												6/6/2016	6/6/2020															$ 13
4	EMP007												1/1/2016	1/1/2020															$ 22
5	EMP002												2/3/2017	3/3/2018															$ 14
6	EMP004												6/7/2020	1/1/2021															$ 15
7	EMP007												1/1/2020	6/6/2020															$ 25
8	EMP002												3/4/2018																$ 16
Employees Data

Cell Formulas
Range	Formula
AO8,AO5:AO6	AO5	=AP2+1
AO7	AO7	=AP4

Here's my test List of Sales

Tonywatsonhelp.xlsx
	A	B	C											N
1	Date Sold		Employee Number											Rate
2	01-Jan-16		EMP002											1
3	27-Jun-16		EMP004											2
4	06-Sep-16		EMP007											3
5	16-Feb-17		EMP002											4
6	19-May-17		EMP004											2
7	08-Nov-17		EMP007											3
8	25-Mar-18		EMP002											7
9	20-Jul-18		EMP004											2
10	24-Nov-18		EMP999											No such employee
11	25-Feb-19		EMP002											7
12	10-May-19		EMP004											2
13	26-Jul-19		EMP007											3
14	08-Oct-19		EMP002											7
15	07-Jul-20		EMP004											5
16	07-Jul-20		EMP007											6
ListOfSales

Cell Formulas
Range	Formula
N2:N16	N2	=IFERROR(IFERROR(AGGREGATE(14,6,ROW($A$2:$A$9999)-ROW($A$1)/(('Employees Data'!$AC$2:$AC$9999=C2)*('Employees Data'!$AO$2:$AO$9999<=A2)*('Employees Data'!$AP$2:$AP$9999>=A2)),1),IFERROR(AGGREGATE(14,6,ROW($A$2:$A$9999)-ROW($A$1)/(('Employees Data'!$AC$2:$AC$9999=C2)*('Employees Data'!$AO$2:$AO$9999<=A2)*('Employees Data'!$AP$2:$AP$9999="")),1),AGGREGATE(14,6,ROW($A$2:$A$9999)-ROW($A$1)/(('Employees Data'!$AC$2:$AC$9999=C2)*('Employees Data'!$AO$2:$AO$9999<=A2)),1))),"No such employee")

 

[tonywatsonhelp]

brilliant, I cant get it not to work, so thank you very much toadstool

 

[Toadstool]

brilliant, I cant get it not to work, so thank you very much toadstool

You're welcome!

Basically it's three AGGREGATE functions wrapped by IFERROR functions.

The first AGGREGATE looks for the highest row number (so promotion/raise dates must be ascending) for that Employee where the start date is less than or equal to the Sale Date and the End Date is higher or equal to the Sale Date.

If that fails it falls into the IFERROR which is an AGGREGATE which looks for the highest row number for that Employee where the start date is less than or equal to the Sale Date and the End Date is null (still employed?).

If that fails then it must be the case where the paperwork dates don't match so it just looks for the highest row number for that Employee where the Start Date is less than or equal to the Sales Date.

If all fail then there must be "No such employee" so it gives that message.

Sorry but I was testing using the row numbers returned by the AGGREGATEs and forgot to put in the INDEX which actually retrieves the Hourly Rate, so here it is:

Tonywatsonhelp.xlsx
	A	B	C											N
1	Date Sold		Employee Number											Rate
2	01-Jan-16		EMP002											$ 12
3	27-Jun-16		EMP004											$ 13
4	06-Sep-16		EMP007											$ 22
5	16-Feb-17		EMP002											$ 14
6	19-May-17		EMP004											$ 13
7	08-Nov-17		EMP007											$ 22
8	25-Mar-18		EMP002											$ 16
9	20-Jul-18		EMP004											$ 13
10	24-Nov-18		EMP999											No such employee
11	25-Feb-19		EMP002											$ 16
12	10-May-19		EMP004											$ 13
13	26-Jul-19		EMP007											$ 22
14	08-Oct-19		EMP002											$ 16
15	07-Jul-20		EMP004											$ 15
16	07-Jul-20		EMP007											$ 25
ListOfSales

Cell Formulas
Range	Formula
N2:N16	N2	=IFERROR(INDEX('Employees Data'!$BE$2:$BE$9999,IFERROR(AGGREGATE(14,6,ROW($A$2:$A$9999)-ROW($A$1)/(('Employees Data'!$AC$2:$AC$9999=C2)*('Employees Data'!$AO$2:$AO$9999<=A2)*('Employees Data'!$AP$2:$AP$9999>=A2)),1),IFERROR(AGGREGATE(14,6,ROW($A$2:$A$9999)-ROW($A$1)/(('Employees Data'!$AC$2:$AC$9999=C2)*('Employees Data'!$AO$2:$AO$9999<=A2)*('Employees Data'!$AP$2:$AP$9999="")),1),AGGREGATE(14,6,ROW($A$2:$A$9999)-ROW($A$1)/(('Employees Data'!$AC$2:$AC$9999=C2)*('Employees Data'!$AO$2:$AO$9999<=A2)),1)))),"No such employee")