Excel 2008 : Complex Searching in multiple column

**shiven.k** · 05-11-2012, 01:08 AM

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

**dilipandey** · 05-11-2012, 01:34 AM

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** · 05-11-2012, 01:36 AM

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>

**shiven.k** · 05-11-2012, 03:05 AM

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

**dilipandey** · 05-11-2012, 02:21 PM

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>

**shiven.k** · 05-14-2012, 02:50 AM

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

**dilipandey** · 05-14-2012, 03:58 AM

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>

**shiven.k** · 05-14-2012, 04:33 AM

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

**dilipandey** · 05-15-2012, 02:29 PM

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>

**shiven.k** · 05-16-2012, 12:36 AM

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.

**dilipandey** · 05-19-2012, 05:52 AM

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>

**shiven.k** · 05-21-2012, 04:36 AM

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

**shiven.k** · 05-21-2012, 05:57 AM

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

**dilipandey** · 05-21-2012, 01:36 PM

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>

**Haseeb Avarakkan** · 05-21-2012, 02:27 PM

Hi Shiven,

See the attached. Does this help?

**shiven.k** · 05-22-2012, 12:18 AM

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

**shiven.k** · 05-22-2012, 12:19 AM

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

**shiven.k** · 09-10-2013, 06:53 AM

Hi Dilip bhai again seek your help

Excel 2008 : Complex Searching in multiple column

LinkBack

Thread Tools

Rate This Thread

Display

Complex Searching in multiple column

Re: Complex Searching in multiple column

Re: Complex Searching in multiple column

Re: Complex Searching in multiple column

Re: Complex Searching in multiple column

Re: Complex Searching in multiple column

Re: Complex Searching in multiple column

Re: Complex Searching in multiple column

Re: Complex Searching in multiple column

Re: Complex Searching in multiple column

Re: Complex Searching in multiple column

Re: Complex Searching in multiple column

Re: Complex Searching in multiple column

Re: Complex Searching in multiple column

Re: Complex Searching in multiple column

Re: Complex Searching in multiple column

Re: Complex Searching in multiple column

Re: Complex Searching in multiple column

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions