• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to use IF formula for large database

MaunishP

Member
Hi Team,
How to use if formula for data which gives me door access database. I will require every team members start time and end time from door access database. Searching it manually is okay, however searching it for 150+ members is diffcult. I have attached sheet which contains 2 sheets. Output & Raw Data.

Kindly help me getting formula here.

Thanks,
Maunish Patel
 

Attachments

  • Door Access Database.xlsx
    11.5 KB · Views: 6
Why not use a Pivot Table
Add Name as a Row Label
Add Door Status as a Column label
Then add the Time twice to the Value Field
Change one Time to Min, One to Max
Format Table as desired

upload_2014-8-28_8-17-9.png

If you have multiple dates you can also add the date as a Second Row Label
upload_2014-8-28_8-19-43.png
See attached file:
 

Attachments

  • Door Access Database with PT.xlsx
    15.7 KB · Views: 6
Thanks Hui, for your valueable inputs. This works however some times team member dont use main door to exit [while leaving office ]or main door to enter [while coming to office]. I would require the first time for team member entering through any door and last time of exit while leaving office premises through any door.
 
Hi,

Your output give no information about dates. If it is not required at all, than see the file, else tell more.

Regards,
 

Attachments

  • Door Access Database.xlsx
    12 KB · Views: 2
Add a Grand Total to the Columns
That will show the min and Max values for each person/date
 
Back
Top