Reflecting the data from one sheet to another based on condition and to send mails

Hi,

I am having a workbook with two sheets named "Data" and "Reps". Based on the Salary condition in Reps sheet the Rep Logins and the Manager Login should reflect in the Data sheet.

Accordingly the data for the particular manager should be sent through the mail.

Find the attached workbook.Book1.xls
Please could anyone help me out sorting out my problem.

Hi team,

Please help me...

Need a little clarification here, Chaitanya,

For the 1st row in Data sheet, Ford has 3 Lakhs as salary. However, the data in the Reps tab is as follows -

Manager Login	REP Login	For Sal
CREPE	XYZ	>=3<5
CREPE	ABC	>=3<5
CREPE	SDF	>=3<5

So which login should be entered for the 1st row?

Hi Arlette,

Thanks for the response..

It does not matters for which login should come for the first row/second row and so on.. But Reps should be equally assigned to the data based on salary condition..

hope this is clear..

Are these (>=3 <5, >5 <7, >9) the only conditions to be checked?

What about >7 < 9?

Hi arlu1201

I am extremely sorry for troubling you much. Please find the attached XL file.

As the criteria (condition) changed from Salary to Experience and inserted a column as "CATEGORY". Based on the category condition the Rep Logins should reflect in the Data sheet.

For categorizing based on Experience "IF(Years of Exp >6, Then "A" Else IF(Years of Exp >4,"B", Else "C"))"

1. Category
2. Reps assigning.

Rep Logins should be equally assigned to the Data sheet. All Rep Logins should get equal number of records based on Category. There is no condition for which category which rep login should be assigned. Only criteria is each login should get equal amount of A/B/C categorized records.


Book1(1).xlsx

Hope you are not irritated on changing this.

thanks in advance.

Hi Arlette

Please help me..

I am not irritated that you changed the requirement. Its good it changed before i actually worked on it. Will work out something shortly.

There is no condition for which category which rep login should be assigned. Only criteria is each login should get equal amount of A/B/C categorized records.

Will the number of records always be divisible by 3 to equally divide them between A, B & C? What if we have like 44 records, where A gets 14, B gets 14 and C gets 14. What should be done with the remaining 2?

Hi Arlette,

Happy to hear from you. I am thankful to you and your helping.
Actually the number of records based on Experience will be less for the category A, B will have more than A and C category will have more than B (C > B > A)
Ex:
A - 41
B - 62
C - 100
Logins: 1. JOHN, 2. DANIEL, 3. GEORGE, 4. HOLY

Each rep login should get exactly 50 Number of records, which will include A, B and C.

If there are 4 Rep Logins, then “A” category records should be more or less equal for those 4 logins. From above if A has 41 records then 41/4, remains 1 record. That 1 record may go for any of the 4 logins. (Here comes “A” category records for 4 logins, each will get 10 records, one of them will have 11 records.)

Category “A”: John – 10 / Daniel – 10 / George – 10 / Holy - 11

Same as for “B” category. (62/4 – two login will get 15 and two of them will get 16).
Category “B”: John – 16 / Daniel – 15 / George – 16 / Holy - 15
For “C” category: (100/4 – Each will get 25 records)

Category “C”: John – 25 / Daniel – 25 / George – 25 / Holy - 25
If we sum up three categories each login should get 50.

Hope, hear you soon..

Hi Arlette,

Please help me out..

For categorizing based on Experience "IF(Years of Exp >6, Then "A" Else IF(Years of Exp >4,"B", Else "C"))"

This criteria does not match what data you have in your attached file.

Does category play any role in the reps login assigning?

Hi Arlette,

Please find the attached workbook.

Sample.xlsx

So as per the new attached file, can i assign any rep id to any category? Or is there any rule for the categories?

Any rep can be assigned to any of the record.

You have 11 reps & 96 records. So the 11 reps which each get 8 records. What should i do with the balance 8 records?

Hi Arlette,

Actually I deal with huge data (more than 5K records), in the attachment I have provided sample of 96 records. Reps count may change. Every Rep Login should get exact of 50 records each according to the criteria on Category.
Ex: If data is of 600 records, and Reps are of 11, each should get 50 based on the category criteria as per my previous post. And here comes 50 records left. Those fifty records need to do nothing.

Hope, I am clear.

So should i create the code to allot 50 records to each rep id?

Hi Arlette,

Yes, you can. Please find the below based on the category of allotting 50 records to a Rep.

Ex:
A - 41
B - 62
C - 100
Logins: 1. JOHN, 2. DANIEL, 3. GEORGE, 4. HOLY

Each rep login should get exactly 50 Number of records, which will include A, B and C.

If there are 4 Rep Logins, then “A” category records should be more or less equal for those 4 logins. From above if A has 41 records then 41/4, remains 1 record. That 1 record may go for any of the 4 logins. (Here comes “A” category records for 4 logins, each will get 10 records, one of them will have 11 records.)

Category “A”: John – 10 / Daniel – 10 / George – 10 / Holy - 11

Same as for “B” category. (62/4 – two login will get 15 and two of them will get 16).
Category “B”: John – 16 / Daniel – 15 / George – 16 / Holy - 15
For “C” category: (100/4 – Each will get 25 records)

Category “C”: John – 25 / Daniel – 25 / George – 25 / Holy - 25
If we sum up three categories each login should get 50.

So if i understand your point correctly, i have distribute 50 records per rep login but i have to also ensure that the 50 are divided equally between A, B & C right?

Exactly Arlette, 50 to each Rep. But, not equally of A, B & C.

Proportionately records should be assigned of three categories data.

In case I am not clear, please let me know, so that will make you understand the same.

Exactly Arlette, 50 to each Rep. But, not equally of A, B & C.

Proportionately records should be assigned of three categories data.

What do you mean?

Hi Arlette,

I am sorry for my previous post. Will update you the sample workbook, so that you will have clear idea. Hope, will get your help for this..

Hi Arlette,

Please find the attached sample workbook with full explanation of what exactly to be.

Sample.xlsxOverview.JPG

Hope, this will be clear for you to understand.

Hi Team,

Any help would be appreciable..