Excel: Find the earliest and latest attendance date from several tables [closed]
up vote
0
down vote
favorite
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!
microsoft-excel
asked Nov 27 at 22:07
jim
61
closed as unclear what you're asking by fixer1234, PeterH, bertieb, BillP3rd, Rajesh S Nov 30 at 10:12
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
up vote
0
down vote
favorite
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!
microsoft-excel
asked Nov 27 at 22:07
jim
61
closed as unclear what you're asking by fixer1234, PeterH, bertieb, BillP3rd, Rajesh S Nov 30 at 10:12
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
Please click on edit and show us what's you've done so far. You can link to a screenshot you have uploaded to imgur.com or other sites; here's an example superuser.com/questions/1378495/…
– K7AAY
Nov 27 at 22:14
Are you looking for Earliest & Latest Date from Various Data Ranges for an Employee ?
– Rajesh S
Nov 28 at 9:32
Hi, I just posted a screenshot of the example I created to explain you what my issue is. Thank you!
– jim
Nov 28 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.
– pat2015
Nov 28 at 22:44
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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!
microsoft-excel
asked Nov 27 at 22:07
jim
61
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!
microsoft-excel
microsoft-excel
asked Nov 27 at 22:07
jim
61
asked Nov 27 at 22:07
jim
61
edited Nov 28 at 22:31
asked Nov 27 at 22:07
jim
61
asked Nov 27 at 22:07
jim
61
asked Nov 27 at 22:07
jim
61
61
closed as unclear what you're asking by fixer1234, PeterH, bertieb, BillP3rd, Rajesh S Nov 30 at 10:12
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
closed as unclear what you're asking by fixer1234, PeterH, bertieb, BillP3rd, Rajesh S Nov 30 at 10:12
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
Please click on edit and show us what's you've done so far. You can link to a screenshot you have uploaded to imgur.com or other sites; here's an example superuser.com/questions/1378495/…
– K7AAY
Nov 27 at 22:14
Are you looking for Earliest & Latest Date from Various Data Ranges for an Employee ?
– Rajesh S
Nov 28 at 9:32
Hi, I just posted a screenshot of the example I created to explain you what my issue is. Thank you!
– jim
Nov 28 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.
– pat2015
Nov 28 at 22:44
add a comment |
Please click on edit and show us what's you've done so far. You can link to a screenshot you have uploaded to imgur.com or other sites; here's an example superuser.com/questions/1378495/…
– K7AAY
Nov 27 at 22:14
Are you looking for Earliest & Latest Date from Various Data Ranges for an Employee ?
– Rajesh S
Nov 28 at 9:32
Hi, I just posted a screenshot of the example I created to explain you what my issue is. Thank you!
– jim
Nov 28 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.
– pat2015
Nov 28 at 22:44
Please click on edit and show us what's you've done so far. You can link to a screenshot you have uploaded to imgur.com or other sites; here's an example superuser.com/questions/1378495/…
– K7AAY
Nov 27 at 22:14
Please click on edit and show us what's you've done so far. You can link to a screenshot you have uploaded to imgur.com or other sites; here's an example superuser.com/questions/1378495/…
– K7AAY
Nov 27 at 22:14
Are you looking for Earliest & Latest Date from Various Data Ranges for an Employee ?
– Rajesh S
Nov 28 at 9:32
Are you looking for Earliest & Latest Date from Various Data Ranges for an Employee ?
– Rajesh S
Nov 28 at 9:32
Hi, I just posted a screenshot of the example I created to explain you what my issue is. Thank you!
– jim
Nov 28 at 22:33
Hi, I just posted a screenshot of the example I created to explain you what my issue is. Thank you!
– jim
Nov 28 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.
– pat2015
Nov 28 at 22:44
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.
– pat2015
Nov 28 at 22:44
add a comment |
1 Answer
1
active
oldest
votes
up vote
2
down vote
Based on what I understand, I suggest a solution.
However note that
- This is not a very flexible solution
- If you add more stores, the formula needs to be manually adjusted and eventually it gets bulky
- 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.
answered Nov 28 at 23:07
pat2015
3,2142722
Nice solution, even though it's little complicated but getting the required data. Perfect 10 ☺
– Rajesh S
Nov 29 at 8: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 5 at 20:54
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
Based on what I understand, I suggest a solution.
However note that
- This is not a very flexible solution
- If you add more stores, the formula needs to be manually adjusted and eventually it gets bulky
- 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.
answered Nov 28 at 23:07
pat2015
3,2142722
Nice solution, even though it's little complicated but getting the required data. Perfect 10 ☺
– Rajesh S
Nov 29 at 8: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 5 at 20:54
add a comment |
up vote
2
down vote
Based on what I understand, I suggest a solution.
However note that
- This is not a very flexible solution
- If you add more stores, the formula needs to be manually adjusted and eventually it gets bulky
- 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.
answered Nov 28 at 23:07
pat2015
3,2142722
Nice solution, even though it's little complicated but getting the required data. Perfect 10 ☺
– Rajesh S
Nov 29 at 8: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 5 at 20:54
add a comment |
up vote
2
down vote
up vote
2
down vote
Based on what I understand, I suggest a solution.
However note that
- This is not a very flexible solution
- If you add more stores, the formula needs to be manually adjusted and eventually it gets bulky
- 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.
answered Nov 28 at 23:07
pat2015
3,2142722
Based on what I understand, I suggest a solution.
However note that
- This is not a very flexible solution
- If you add more stores, the formula needs to be manually adjusted and eventually it gets bulky
- 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.
answered Nov 28 at 23:07
pat2015
3,2142722
answered Nov 28 at 23:07
pat2015
3,2142722
answered Nov 28 at 23:07
pat2015
3,2142722
answered Nov 28 at 23:07
pat2015
3,2142722
3,2142722
Nice solution, even though it's little complicated but getting the required data. Perfect 10 ☺
– Rajesh S
Nov 29 at 8: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 5 at 20:54
add a comment |
Nice solution, even though it's little complicated but getting the required data. Perfect 10 ☺
– Rajesh S
Nov 29 at 8: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 5 at 20:54
Nice solution, even though it's little complicated but getting the required data. Perfect 10 ☺
– Rajesh S
Nov 29 at 8:43
Nice solution, even though it's little complicated but getting the required data. Perfect 10 ☺
– Rajesh S
Nov 29 at 8: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 5 at 20:54
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 5 at 20:54
add a comment |
Please click on edit and show us what's you've done so far. You can link to a screenshot you have uploaded to imgur.com or other sites; here's an example superuser.com/questions/1378495/…
– K7AAY
Nov 27 at 22:14
Are you looking for Earliest & Latest Date from Various Data Ranges for an Employee ?
– Rajesh S
Nov 28 at 9:32
Hi, I just posted a screenshot of the example I created to explain you what my issue is. Thank you!
– jim
Nov 28 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.
– pat2015
Nov 28 at 22:44