How to dynamically create new list of employees from database based on certain conditions

[kpku2020]

Hi,

I am creating a employee database month wise based on two conditions:
1. An employee should be in the list of the corresponding month if he has joined the organization in current month or prior to current month
2. If the employee is still serving.

For this I have a master ("MASTER" sheet in attached file EmpDBTest.xlsx) data list with few hundred employees in it.

In the April2014 sheet I want the corresponding columns should automatically populate from the data of MASTER LIST based on the above two conditions.

For. e.g. April2014 sheet should have all the employees who joined the organization prior to April 2014 & are still serving and not the employees who has resigned. We need to have all the details like name, no. sl.no. DOJ etc This sheet should not have any employee who joins later in the year say for e.g. Aug2014.

Similarly For May2014 we should have all the serving employees till current date minus any resignation.

I have tried a lot of INDEX, MATCH and other look up functions but unable to get it right.

Requesting everyone for help.

 

[Somendra Misra]

Hi kpku2020,

Try using below array formula foe getting Emp No.

=IFERROR(INDEX(Master!B$3:B$22,SMALL(IF(Master!$E$3:$E$22<='Apr2014'!$B$2,IF(Master!$F$3:$F$22="",ROW(Master!$B$3:$B$22)-ROW(Master!$B$3)+1)),ROWS('Apr2014'!B$6:B6))),"")

Change the array range to get names and DOJ.

Regards,

 

[kpku2020]

Its not working.... Please see the attached file with your formula. The employees column should have 19 emp ids.

 

[Somendra Misra]

Can you explain how 19? as I m getting 15.

Regards,

 

[kpku2020]

You are right. We should get 15 employees.
20 - (4 Resignation + 1 May-14)

But I don't see any employee ID except 1003 in Emp No Column.

 

[Somendra Misra]

After entering the formula in B6 hit Ctrl+Shift+Enter and not just enter as this is an array formula. If every thing went well you will see { } around formula in formula bar.

Regards,

 

[kpku2020]

Yes its working. Great!!

One more thing, I think you can help with that too.

I want the serial numbers in Sl. No. columns to be populated automatically based on the number of employees which is dynamic.

For. e.g. if there are 10 employees who satisfy this criteria then I should have only 10 Sl. No. automatically.

Thanks for your great help once again.

 

[Somendra Misra]

Use below formula in A6 and copy down:

=IF(B6="","",ROWS(A$6:A6))

Regards,

 

[kpku2020]

Thank you for your help. Its working.

 

[Somendra Misra]

Welcome Back anytime.

Regards,

 

[kpku2020]

Hi Somendra,

There is slight change in the conditions basis which we need to show "Serving" & "Retired". Earlier in the formula we were checking ""(NULL) for DOL(Date of Leaving) but ideally we should check both i.e.
DOJ<=CurrMonth
DOL="" OR DOL>CurrMonth

And if the above is true we should decide serving/retired. I tried changing the formula according to the above conditions but something is not working well.

If you can take a look and correct it, that would be good.

Please see the attached file for ready ref.

Rgds.

 

[Somendra Misra]

Hi kpku2020,

Try below array formula in Month wise sheets, adjust the range.

=IFERROR(INDEX(Master!B$3:B$22,SMALL(IF(Master!$E$3:$E$22<=$B$2,IF((Master!$F$3:$F$22="")+(Master!$F$3:$F$22>$B$2),ROW(Master!$B$3:$B$22)-ROW(Master!$B$3)+1)),ROWS(B$6:B6))),"")

Just advise if any issue.

Regards,

 

[kpku2020]

Hi Somendra,
That seems to be working. Everything thing is same which I was trying, except for that "+" in second IF. I have few questions here maybe you can let me know:

1. Will Boolean operators don't work correctly in array function/IF/SMALL. because + is nothing but OR.

2. I am also struggling with another problem this time. with finding the top 10 ranks BASED on condition who is present. For e.g.

Even though a student is coming in top ranks, but if his status is Absent he should not appear in the list.

Please see the attached file. I am able to get the ranks but with condition applied its not working correctly.

Kindly help.

 

[kpku2020]

One more thing....

How can we compare month & year of date of joining and current month using the array formula....In a sample I am trying this =IF(AND(MONTH(Table2[DOJ])<=MONTH($L$1),YEAR(Table2[DOJ])<=YEAR($L$1)),"YES","NO") but its not working....I am not getting the desired result.

Actually the condition IF(Master!$E$3:$E$22<=$B$2, in our formula is not providing desired result.

Any pointers here I would really appreciate...

 

[Somendra Misra]

@kpku2020

LOGICAL function such as AND,OR return single value so even if you do an array function inside them they will return single TRUE / FALSE. But we require an array of TRUE/FALSE in side IF or any other function. I think that is the reason it did not worked SO using + inplace of OR and * in place of AND is a better option in such cases.

w.r.t your second problem for ranking of students see the attached file. Is this what you are looking for?

For your comment #14 can you explain which formula you are ref. to and in which file?

Regards,

 

[kpku2020]

Hi Somendra,

For the ranks problem, I even do not want to display the marks of the students....91,89 at sl. no.5&6. As the students are absent they should not appear in the list.

For #14

I tried modifying the formula
=IFERROR(INDEX(Master!B$3:B$22,SMALL(IF(Master!$E$3:$E$22<=$B$2,IF((Master!$F$3:$F$22="")+(Master!$F$3:$F$22>$B$2),ROW(Master!$B$3:$B$22)-ROW(Master!$B$3)+1)),ROWS(B$6:B6))),"")
In this instead of comparing the Master!$E$3:$E$22<=$B$2 the dates directly I mean (10-04-2014 <= 1-04-2014) I just want to compare the months and years of these dates. This will give me the desired result.

 

[Somendra Misra]

@kpku2020

For Rank problem, please have a look of attached file.

For #14: Try using below array formula:

=IFERROR(INDEX(Master!B$3:B$22,SMALL(IF(MONTH(Master!$E$3:$E$22)&YEAR(Master!$E$3:$E$22)<=MONTH($B$2)&YEAR($B$2),IF((Master!$F$3:$F$22="")+(MONTH(Master!$F$3:$F$22)&YEAR(Master!$F$3:$F$22)>MONTH($B$2)&YEAR($B$2)),ROW(Master!$B$3:$B$22)-ROW(Master!$B$3)+1)),ROWS(B$6:B6))),"")

Regards,

 

[kpku2020]

Hi Somendra,

Everything seems to be working as expected. Thanks once again.

Will get back again when stuck.

Rgds

 

[kpku2020]

Hi

I have come up with new problem too quickly.

Please see the attached file . It has 2 sheets "DB" & "Entry".

On the "ENTRY" sheet in the address column I want to write just about anything and if its found anywhere in any address on DB sheet I should see all the options in a kind of dropdown which has the matching result. Once I select a particular option/record other corresponding fields should also get auto populated.

Regards.

 

[Somendra Misra]

Hi kpku2020,

Glad your initial problem solved.

This seems to be a new problem. For a new problem always try to create a new thread.

Try to go through below link all the comments and see if this can solve your issue of creating a dynamic dropdown. If that gets solved pulling rest values will not be difficult.

http://chandoo.org/forum/threads/dropdown-based-on-search-string.15410/

Regards,