+ Reply to Thread
Results 1 to 18 of 18

Excel 2008 : Complex Searching in multiple column

  1. #1
    Registered User
    Join Date
    05-10-2012
    Location
    Kolkata, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    30

    Complex Searching in multiple column

    I have data in this format and want to arrange in the given format:
    Data Have:
    AC-No. Date Absent
    114 21/03/2012
    114 22/03/2012
    114 23/03/2012
    114 24/03/2012 True
    114 26/03/2012 True
    114 27/03/2012
    114 28/03/2012
    114 29/03/2012
    273 21/03/2012
    273 22/03/2012
    273 23/03/2012
    273 24/03/2012 True
    273 26/03/2012
    273 27/03/2012 True
    273 28/03/2012
    273 29/03/2012
    1030 21/03/2012
    1030 22/03/2012
    1030 23/03/2012
    1030 24/03/2012 True
    1030 26/03/2012

    Now, I want to arrange the data in this format

    Emp_cd 21 22 23 24 25 26 27 28
    114 0 0 0 A 0 A 0 0
    273 0 0 0 A 0 0 A 0
    1030 0 0 0 A 0 0 0 0
    1084 0 0 0 0 0 0 0 0

    Thanks for help,
    Regards,
    Shiven

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Complex Searching in multiple column

    Hi Shiven,

    Welcome to the forum.

    Use the below formula :-
    {=IFERROR(IF(INDEX($A$2:$C$22,MATCH($F5&G$4,$A$2:$A$22&$B$2:$B$22,0),3)>0,"A",""),"")}

    Complex search - shiven.xlsx

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  3. #3
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Complex Searching in multiple column

    Hi Shiven,

    Just noticed that you need 0 if there is no TRUE,

    Amend the last part of the formula :-

    ,"")}

    to

    ,0)}

    Also since this is an array formula, you need to enter this using key combination, Ctrl + Shift + Enter. Thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  4. #4
    Registered User
    Join Date
    05-10-2012
    Location
    Kolkata, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    30

    Re: Complex Searching in multiple column

    Thanks Dilip Bhai, Its great help.

    Need one more favour, pls

    There are four sheets in my workbook Timesheet, Attend, Leave, Holiday. Here are the abbreviations I want to put in my timesheet relevant for each employee code with the corresponding dates on the top as given on timesheet.

    Conditions:
    1. If the day exist in holiday sheet then it should be marked as “H” against all employee code in the corresponding column
    2. Then is the employee has taken leave as given in Leave sheet then on the day column relevant it should be marked “L”
    3. Again if the employees not taken leave and in Attend sheet -> Absent Column contain “TRUE” then it should be marked as “A”
    4. Again if the employee not taken leave and in Attend Sheet ->Exception Column contain “TOUR” then it should be marked as “T”

    P > Present
    H > Holiday / Weekly Off
    L > Leave
    T > On Duty / Tour
    A > Absent means unregularised absence


    Please provide me email id so that I can forward you the excel sheet

    Please help me in making this sheet automate.
    Thanks for your kind help.
    Shiven
    Attached Files Attached Files

  5. #5
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Complex Searching in multiple column

    You are welcome Shiven,

    In revised scenario, you can use below formula:-

    {=IF(ISERROR(MATCH(E$1,Holiday!$A:$A,0)),IF(INDEX(Attend!$A1:$O2242,MATCH(TimeSheet!$B$2&TimeSheet!E$1,Attend!$A$2:$A$2242&Attend!$O$2:$O$2242,0)+1,11)="TRUE","A","T"),"H")}

    TimeSheet-1.xlsx

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  6. #6
    Registered User
    Join Date
    05-10-2012
    Location
    Kolkata, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    30

    Re: Complex Searching in multiple column

    Thanks Dilip Bhai, but in the sheet "P" & "L" is missing

  7. #7
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Complex Searching in multiple column

    Ok.. Shiven... what is the criteria for P.. could you brief about all categories with one example case in sheet? thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  8. #8
    Registered User
    Join Date
    05-10-2012
    Location
    Kolkata, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    30

    Re: Complex Searching in multiple column

    Conditions:
    1. If the day exist in holiday sheet then it should be marked as “H” against all employee code in the corresponding column (Done, and its working fine)
    2. Then is the employee has taken leave as given in Leave sheet then on the day column relevant it should be marked “L”
    3. Again if the employees not taken leave and in Attend sheet -> Absent Column contain “TRUE” then it should be marked as “A”
    4. Again if the employee not taken leave and in Attend Sheet ->Exception Column contain “TOUR” then it should be marked as “T”
    5. Else rest marked with "P"

    P > Present
    H > Holiday / Weekly Off
    L > Leave
    T > On Duty / Tour
    A > Absent means unregularised absence

  9. #9
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Complex Searching in multiple column

    Hi shiven.. seems like you missed my second part of request..

    with one example case in sheet?

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  10. #10
    Registered User
    Join Date
    05-10-2012
    Location
    Kolkata, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    30

    Re: Complex Searching in multiple column

    Hi Dilip Bhai,

    Good Morning, just considering an example from the sample workbook.

    Suppose, I choose for an employee id=114,

    * If the date exist in holiday sheet then in timesheet it will reflect "H" in the corresponding cell
    * Again, if emp_id=114, has taken leave from 21/03/12 to 26/03/12 then it will be marked as "L"
    * It will check for the "True" in Attend Sheet Absent column if it is found, then it will be marked as "A"
    * Then, will check for the "TOUR" in Attend Sheet Exception Column it is found, then it will be marked as "T"
    * Else all will be marked "P"

    Know its too complex,

    Thanks for your kind suggestion and help...

    It is possible doing with VBA code but I am trying look for some formula based solution.
    Last edited by shiven.k; 05-16-2012 at 12:39 AM.

  11. #11
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Complex Searching in multiple column

    Hi Shiven,

    I had to reformat your data in some sheets and after that I have used following array formula to achieve the desired results:-

    {=IF(ISNUMBER((MATCH(E$1,Holiday!$A:$A,0))),"H",IF(AND(E$1>=VLOOKUP($B2,Leave!$B:$D,2,0),E$1<=VLOOKUP($B2,Leave!$B:$D,3,0)),"L",IF(INDEX(Attend!$A$2:$O$2242,MATCH(TimeSheet!$B2&TimeSheet!E$1,Attend!$A$2:$A$2242&Attend!$B$2:$B$2242,0),11)="True","A",IF(INDEX(Attend!$A$2:$O$2242,MATCH(TimeSheet!$B2&TimeSheet!E$1,Attend!$A$2:$A$2242&Attend!$B$2:$B$2242,0),14)="Tour","T","P"))))}

    and I guess it's working fine


    TimeSheet-2.xlsx

    By the way.. Is mr. Bagchi is still heading Exide for North India ?

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  12. #12
    Registered User
    Join Date
    05-10-2012
    Location
    Kolkata, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    30

    Thumbs up Re: Complex Searching in multiple column

    Thanks Dilip Bhai, for your kind help.

    If you are talking about Mr.Pradeep Bagchi, yes he is now All India Head for Automotive Battery and he is posted at Kolkata only, and north regional sales manager is Mr.Sourav Banerjee.

    Are you too associated with Exide.

    Regards,
    Shivendra
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    05-10-2012
    Location
    Kolkata, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    30

    Re: Complex Searching in multiple column

    Dilip Bhai there is minor issue...considering one example, in Leave sheet there is suppose Emp_no.:1084 have taken leave more in different range of date is not getting updated.

    I got the solution in VBA, but m trying to get the formula based solution.

    Regards,
    Shiven

  14. #14
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Complex Searching in multiple column

    Hi Shiven,

    I was associated with Exide House, Jhandelwalan till Dec 2002/2003..(It was the time when Reliance Mobile Services launched).... and I used to meet and greet Mr. Bagchi.

    Regarding the emp ID 1084, I would suggest to upload the modified and highlight this row /cell. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  15. #15
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,234

    Re: Complex Searching in multiple column

    Hi Shiven,

    See the attached. Does this help?
    Attached Files Attached Files
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  16. #16
    Registered User
    Join Date
    05-10-2012
    Location
    Kolkata, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    30

    Re: Complex Searching in multiple column

    Haseeb bhai thanx for your valueable suggestion ... its superb... may i have ur email id pls

  17. #17
    Registered User
    Join Date
    05-10-2012
    Location
    Kolkata, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    30

    Re: Complex Searching in multiple column

    yes its long back Now, Mr.Pradeep Bagchi is President, Automotive.

  18. #18
    Registered User
    Join Date
    05-10-2012
    Location
    Kolkata, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    30

    Re: Complex Searching in multiple column

    Hi Dilip bhai again seek your help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1