1

Hello and thanks in advance for your help!

I have a spreadsheet with, on the same sheet, employee attendance logs in several locations, each location having its own table. I am looking for a way to find a person's earliest and latest attendance in every store (11 columns) as a person could have worked in several places. I guess vlookup could be an option but I cannot find a way to add several columns to the formula.

Any idea?

Thanks again for your help!

Thank you so much for showing interest in my question, I honestly thought nobody would even bother:-)

https://drive.google.com/file/d/1F50XBFjqw0bxHPWd9tuCtMm4lrEOwZeh/view?usp=drivesdk Here is the link to the screen. Thanks very much indeed!

jim
  • 21
  • Please click on [edit] and show us what's you've done so far. You can link to a screenshot you have uploaded to https://imgur.com or other sites; here's an example https://superuser.com/questions/1378495/finding-the-top-10-15-20-percent-excel-2016-or-google-sheets/1378543#1378543 – K7AAY Nov 27 '18 at 22:14
  • Are you looking for Earliest & Latest Date from Various Data Ranges for an Employee ? – Rajesh Sinha Nov 28 '18 at 09:32
  • Hi, I just posted a screenshot of the example I created to explain you what my issue is. Thank you! – jim Nov 28 '18 at 22:33
  • Are the number of stores fixed, i.e . the list of columns? Or are you possibly going to add more stores to the right of the table in future? One approach possibly could be to create an array formula to return the lowest date from each column for a name and pass it to MIN and similarly MAX for max value. – patkim Nov 28 '18 at 22:44

1 Answers1

3

Based on what I understand, I suggest a solution. However note that

  1. This is not a very flexible solution
  2. If you add more stores, the formula needs to be manually adjusted and eventually it gets bulky
  3. So this may not be the best solution.

In this example sample data is in cells A1:H6. A master table is to be prepared somewhere else in the sheet. This is simply manually done. Stack all names and remove duplicates.

In D9 put the following formula and press CTRL + SHIFT + ENTER from within the formula bar to create an Array Formula. The Formula shall now be automatically enclosed in Curly Braces to indicate that it's an array formula.

=MIN(MIN(IF($A$2:$A$6=C9,$B$2:$B$6,9^99)),MIN(IF($C$2:$C$6=C9,$D$2:$D$6,9^99)),MIN(IF($E$2:$E$6=C9,$F$2:$F$6,9^99)),MIN(IF($G$2:$G$6=C9,$H$2:$H$6,9^99)))

In E9 put the following formula and again as above create an Array Formula.

=MAX(MAX(IF($A$2:$A$6=C9,$B$2:$B$6,0)),MAX(IF($C$2:$C$6=C9,$D$2:$D$6,0)),MAX(IF($E$2:$E$6=C9,$F$2:$F$6,0)),MAX(IF($G$2:$G$6=C9,$H$2:$H$6,0)))

Select both and drag it down up to the intended rows.

See the below screenshot. Hope this helps and works for you.

enter image description here

patkim
  • 5,455
  • Nice solution, even though it's little complicated but getting the required data. Perfect 10 ☺ – Rajesh Sinha Nov 29 '18 at 08:43
  • Hello, yes, this is what I was looking for. I tried it and it works, even though it takes more time For Excel to calculate. Thank you very much for your help! – jim Dec 05 '18 at 20:54