Returning results based on multiple criteria

M

Matt Cook

New Member
Joined
Oct 13, 2022
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Hi

So I cannot get my head around this problem.

Sheet 2 is an export of recorded Exposures for employees on a given date.

On Sheet 1 I need to know only if an exposure was recorded on a given date by the employee. The Exposure could be 00:00 I still need to know this.
You will see on Sheet 2 the employee names sometimes have numbers on their names and they sometime record multiple Exposures on a given day.

I will need to update Sheet 2 on a daily basis

Could somebody help me please.

Sheet 1

Book3
ABCDEFGHIJK
101/01/202402/01/202403/01/202404/01/202405/01/202406/01/202407/01/202408/01/202409/01/202410/01/2024
2Iliana Malone
3Ruben Rubio
4Hadassah Peck
5Yousef Kane
6Ellianna Lowe
7Julius Haynes
8Lexi Casey
9Armando Bullock
10Winnie Edwards
11Adrian Alexander
12Lyla Portillo
13Wallace Carroll
14Zara Pace
15Dior Ho
16Calliope Malone
17Ruben Pugh
18Landry Hudson
19Peter Gomez
20Natalie Ross
21Wesley Lin
Sheet1


Sheet 2

Book3
ABCD
1DateOperative NameTotal Exposure TimeTotal Exposure Points
207/01/2024Ellianna Lowe (10005258)00:000
307/01/2024Hadassah Peck (58942001)00:0516.67
407/01/2024Julius Haynes (65982145)00:000
507/01/2024Lexi Casey00:000
607/01/2024Ruben Rubio (54986521)00:000
707/01/2024Yousef Kane00:15112.5
806/01/2024Adrian Alexander (12324565)00:000
906/01/2024Armando Bullock00:000
1006/01/2024Calliope Malone (12654587)00:000
1106/01/2024Dior Ho (10002345)00:15112.5
1206/01/2024Landry Hudson (21548754)00:1672.53
1306/01/2024Lyla Portillo00:000
1406/01/2024Natalie Ross(21659854)00:0645
1506/01/2024Peter Gomez00:0620
1606/01/2024Ruben Pugh00:026.67
1706/01/2024Wallace Carroll (10102154)00:0211.27
1806/01/2024Wesley Lin (5421548700:000
1906/01/2024Winnie Edwards00:0620
2006/01/2024Zara Pace00:0516.67
2105/01/2024Adrian Alexander (12324565)00:000
2205/01/2024Arianna Curry00:000
2305/01/2024Arianna Curry00:000
2405/01/2024Armando Bullock00:0516.67
2505/01/2024Bentley Maldonado06:003888
2605/01/2024Bentley Maldonado00:000
2705/01/2024Briggs Rogers00:0633.8
2805/01/2024Briggs Rogers00:33247.5
2905/01/2024Caden Sawyer (32165498)00:4537.5
3005/01/2024Caden Sawyer (32165498)00:000
3105/01/2024Calliope Malone (12654587)00:000
3205/01/2024Dior Ho (10002345)00:1182.5
3305/01/2024Elaina Li00:000
3405/01/2024Elaina Li00:000
3505/01/2024Elisha Conway00:0526.27
3605/01/2024Elisha Conway00:1136.67
3705/01/2024Ellianna Lowe (10005258)00:000
3805/01/2024Hadassah Peck (58942001)00:0516.67
3905/01/2024Helena Enriquez01:1562.5
4005/01/2024Helena Enriquez00:040
4105/01/2024Iliana Malone00:000
4205/01/2024Iliana Malone00:000
4305/01/2024Jemma Knox00:000
4405/01/2024Jemma Knox00:1765.77
4505/01/2024Jorge Tyler00:0443.2
4605/01/2024Jorge Tyler00:000
4705/01/2024Julius Haynes (65982145)00:0532.67
4805/01/2024Landry Hudson (21548754)00:0516.67
4905/01/2024Leland Zuniga00:000
5005/01/2024Leland Zuniga00:0217.28
5105/01/2024Leslie Schneider00:000
5205/01/2024Leslie Schneider00:1033.33
5305/01/2024Lexi Casey00:0554
5405/01/2024Lyla Portillo00:000
5505/01/2024Madelyn Guerra00:0826.67
5605/01/2024Madelyn Guerra00:000
5705/01/2024Makenna Nunez (65327845)00:000
5805/01/2024Makenna Nunez (65327845)00:000
5905/01/2024Marina Schmidt (01234567)00:000
6005/01/2024Marina Schmidt (01234567)00:2170
6105/01/2024Natalie Ross(21659854)00:0310
6205/01/2024Nevaeh Fernandez (65255414)00:1040.33
6305/01/2024Nevaeh Fernandez (65255414)00:000
6405/01/2024Peter Gomez00:000
6505/01/2024Raymond Mora00:0943.8
6605/01/2024Raymond Mora00:000
6705/01/2024River Wallace00:0723.33
6805/01/2024River Wallace00:000
6905/01/2024Ruben Pugh00:000
7005/01/2024Ruben Rubio (54986521)00:000
7105/01/2024Ryann Herrera00:000
7205/01/2024Ryann Herrera00:52390
7305/01/2024Valentin Griffith00:30121
7405/01/2024Valentin Griffith00:000
7505/01/2024Wallace Carroll (10102154)00:000
7605/01/2024Wesley Lin (5421548700:000
7705/01/2024Winnie Edwards00:000
7805/01/2024Yousef Kane00:0310
7905/01/2024Zara Pace00:000
8005/01/2024Zayden Chavez (12345678)00:30121
8105/01/2024Zayden Chavez (12345678)00:23102.32
8205/01/2024Ellianna Lowe (10005258)00:0520.17
8305/01/2024Hadassah Peck (58942001)00:0723.33
8405/01/2024Julius Haynes (65982145)00:0645
8505/01/2024Lexi Casey00:000
8605/01/2024Ruben Rubio (54986521)00:0716.86
8705/01/2024Yousef Kane00:1248.4
8805/01/2024Adrian Alexander (12324565)00:067.2
8905/01/2024Armando Bullock00:0526.27
9005/01/2024Calliope Malone (12654587)00:000
9105/01/2024Dior Ho (10002345)00:000
9205/01/2024Landry Hudson (21548754)00:010
9305/01/2024Lyla Portillo00:000
9405/01/2024Natalie Ross(21659854)00:010
9505/01/2024Peter Gomez00:0826.67
9605/01/2024Ruben Pugh00:033.6
9705/01/2024Wallace Carroll (10102154)00:000
9805/01/2024Wesley Lin (5421548700:1550
9905/01/2024Winnie Edwards00:31232.5
10005/01/2024Zara Pace00:1290
10105/01/2024Adrian Alexander (12324565)00:000
10205/01/2024Arianna Curry00:1024.08
10305/01/2024Arianna Curry00:2170
10405/01/2024Armando Bullock00:000
10505/01/2024Bentley Maldonado00:000
10605/01/2024Bentley Maldonado00:000
10705/01/2024Briggs Rogers00:0554
10804/01/2024Adrian Alexander (12324565)00:000
10904/01/2024Adrian Alexander (12324565)00:000
11004/01/2024Arianna Curry00:000
11104/01/2024Arianna Curry00:0516.67
11204/01/2024Armando Bullock00:0860
11304/01/2024Armando Bullock00:0516.67
11404/01/2024Bentley Maldonado00:013.33
11504/01/2024Bentley Maldonado00:000
11604/01/2024Briggs Rogers00:45181.5
11704/01/2024Briggs Rogers00:000
11804/01/2024Briggs Rogers00:000
11904/01/2024Caden Sawyer (32165498)01:59670.37
12004/01/2024Caden Sawyer (32165498)00:1040.33
12104/01/2024Caden Sawyer (32165498)01:59670.37
12204/01/2024Calliope Malone (12654587)00:1033.33
12304/01/2024Calliope Malone (12654587)00:1550
12404/01/2024Calliope Malone (12654587)00:1136.67
12504/01/2024Dior Ho (10002345)00:1343.33
12604/01/2024Dior Ho (10002345)00:0516.67
12704/01/2024Dior Ho (10002345)00:026.67
12804/01/2024Elaina Li00:000
12904/01/2024Elaina Li00:000
13004/01/2024Elaina Li00:30100
13104/01/2024Elisha Conway00:0516.67
13204/01/2024Elisha Conway00:000
13304/01/2024Elisha Conway00:40300
13404/01/2024Ellianna Lowe (10005258)00:1953.88
13504/01/2024Ellianna Lowe (10005258)00:18135
13604/01/2024Ellianna Lowe (10005258)00:0516.67
13704/01/2024Hadassah Peck (58942001)00:000
13804/01/2024Hadassah Peck (58942001)00:1056.33
13904/01/2024Hadassah Peck (58942001)00:000
14004/01/2024Helena Enriquez00:000
14104/01/2024Helena Enriquez00:000
14204/01/2024Helena Enriquez00:000
14304/01/2024Iliana Malone00:000
14404/01/2024Iliana Malone00:000
14504/01/2024Iliana Malone00:1653.33
14604/01/2024Jemma Knox00:000
14704/01/2024Jemma Knox00:0312.1
14804/01/2024Jemma Knox00:1278.4
14904/01/2024Jorge Tyler00:450
15004/01/2024Jorge Tyler00:1035.43
15104/01/2024Jorge Tyler00:000
15204/01/2024Jorge Tyler00:000
15304/01/2024Julius Haynes (65982145)00:22165
15404/01/2024Julius Haynes (65982145)00:16120
15504/01/2024Julius Haynes (65982145)00:026.67
15604/01/2024Landry Hudson (21548754)00:0516.67
15704/01/2024Landry Hudson (21548754)00:0620
15804/01/2024Landry Hudson (21548754)00:000
15904/01/2024Leland Zuniga00:000
16004/01/2024Leland Zuniga00:0520.17
16104/01/2024Leland Zuniga00:1033.33
16204/01/2024Leslie Schneider01:00338
16304/01/2024Leslie Schneider00:0516.67
16404/01/2024Leslie Schneider00:0752.5
16504/01/2024Lexi Casey00:000
16604/01/2024Lexi Casey00:000
16704/01/2024Lexi Casey00:000
16804/01/2024Lyla Portillo00:0826.67
16904/01/2024Lyla Portillo00:1033.33
17004/01/2024Lyla Portillo00:0426.13
17104/01/2024Madelyn Guerra00:0516.67
17204/01/2024Madelyn Guerra00:0634.93
17304/01/2024Madelyn Guerra00:0921.68
17404/01/2024Madelyn Guerra00:0316.9
17504/01/2024Makenna Nunez (65327845)00:000
17604/01/2024Makenna Nunez (65327845)00:000
17704/01/2024Makenna Nunez (65327845)00:1056.33
17804/01/2024Marina Schmidt (01234567)00:1756.67
17904/01/2024Marina Schmidt (01234567)00:2934.8
18004/01/2024Marina Schmidt (01234567)00:000
18104/01/2024Natalie Ross(21659854)00:000
18204/01/2024Natalie Ross(21659854)00:000
18304/01/2024Natalie Ross(21659854)00:1035.43
18404/01/2024Nevaeh Fernandez (65255414)00:1033.33
18504/01/2024Nevaeh Fernandez (65255414)00:000
18604/01/2024Nevaeh Fernandez (65255414)00:0319.6
18704/01/2024Peter Gomez00:000
18804/01/2024Peter Gomez00:000
18904/01/2024Peter Gomez00:000
19004/01/2024Raymond Mora00:000
19104/01/2024Raymond Mora00:1140.87
19204/01/2024Raymond Mora00:0316.9
19304/01/2024River Wallace06:002352
19404/01/2024River Wallace00:1550
19504/01/2024River Wallace00:040
19604/01/2024Ruben Pugh00:1033.33
19704/01/2024Ruben Pugh01:3075
19804/01/2024Ruben Pugh00:000
19904/01/2024Ruben Rubio (54986521)00:000
20004/01/2024Ruben Rubio (54986521)00:0930
20104/01/2024Ruben Rubio (54986521)00:000
20204/01/2024Ryann Herrera00:000
20304/01/2024Ryann Herrera00:000
20404/01/2024Ryann Herrera00:2530
20504/01/2024Valentin Griffith00:1033.33
20604/01/2024Valentin Griffith00:0516.67
20704/01/2024Valentin Griffith00:0413.33
20804/01/2024Wallace Carroll (10102154)00:1550
20904/01/2024Wallace Carroll (10102154)00:2583.33
21004/01/2024Wallace Carroll (10102154)00:1240
21104/01/2024Wesley Lin (5421548700:000
21204/01/2024Wesley Lin (5421548700:2066.67
21304/01/2024Wesley Lin (5421548700:000
21404/01/2024Winnie Edwards00:000
21504/01/2024Winnie Edwards00:33356.4
21604/01/2024Winnie Edwards00:000
21704/01/2024Yousef Kane00:000
21804/01/2024Yousef Kane00:013.33
21904/01/2024Yousef Kane00:000
22004/01/2024Zara Pace00:0826.67
22104/01/2024Zara Pace00:1140.87
22204/01/2024Zara Pace00:2583.33
22304/01/2024Zayden Chavez (12345678)00:000
22404/01/2024Zayden Chavez (12345678)00:1680.69
22504/01/2024Zayden Chavez (12345678)00:000
22604/01/2024Zayden Chavez (12345678)00:2583.33
Sheet2


Thank you in advance
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
I forgot to mention that I cannot use VBA due to workplace limitations.

Thanks
 
Upvote 0
Try this, In cell B2 of sheet1 and copy to the right and down.

Excel Formula: 
=LET(b,Sheet2!$B$2:$B$300,SUMPRODUCT((TRIM(TEXTBEFORE(b,"(",,,,b))=$A2)*(Sheet2!$A$2:$A$300=B$1)*(Sheet2!$C$2:$C$300>=0)))

If the result is 0, then there are no matches, if the result is greater than 0, then there are matches.
 
Upvote 1
Solution
Thank you so much for your reply. It works for me :)

I do have a problem though, In the example I submitted here I didn't use all of the columns in my actually report. I figured I could just change the Column heading over to make it work but as I don't understand the formula you've given me I can't get it to work.

In the real report (sheet 2) the date would be in column B, the name in Column C and the Exposure time in Column F

Could you please amend it so that it would work for that.

Actual Sheet 2 is below for example:

Book2
ABCDEFG
1DateOperative NameContractSubcontractorTotal Exposure TimeTotal Exposure Points
207/01/202400:000
307/01/202400:0516.67
407/01/202400:15112.5
507/01/202400:000
607/01/202400:000
707/01/202400:000
806/01/202400:000
906/01/202400:0620
1006/01/202400:000
1106/01/202400:000
1206/01/202400:0211.27
1306/01/202400:0516.67
1406/01/202400:15112.5
1506/01/202400:000
1606/01/202400:026.67
1706/01/202400:1672.53
1806/01/202400:0620
1906/01/202400:0645
2006/01/202400:000
2105/01/202400:000
2205/01/202400:000
2305/01/202400:2170
2405/01/202400:23102.32
2505/01/202400:000
2605/01/202406:003888
2705/01/202400:000
2805/01/202400:0443.2
Sheet2


Thanks again for your time, sorry I made the mistake.

Also 1 more thing. What are the lower case b's representing?

Thank you
 
Upvote 0
DanteAmor said:
Try this, In cell B2 of sheet1 and copy to the right and down.

Excel Formula: 
=LET(b,Sheet2!$B$2:$B$300,SUMPRODUCT((TRIM(TEXTBEFORE(b,"(",,,,b))=$A2)*(Sheet2!$A$2:$A$300=B$1)*(Sheet2!$C$2:$C$300>=0)))

If the result is 0, then there are no matches, if the result is greater than 0, then there are matches.
Click to expand...
I think I figured it out how to make it work for my actual data as I got it wrong in the example I originally posted.

Thank you so much for your assistance. Amazing.

I'd still like to know what the lower case b's represent though.
 
Upvote 0
Matt Cook said:
I'd still like to know what the lower case b's represent though.
Click to expand...

For a better understanding review the following:

LET function - Microsoft Support

support.microsoft.com support.microsoft.com

You create the name of the variable directly in the formula, it can be any text as long as it is not a reserved word or the name of an existing function in Excel.

In this case I have named the variable "b" and for this formula, the variable "b" means a range of cells "Sheet2!$B$2:$B$300", so I can use the variable "b" in the formula instead of continually repeating everything the data "Sheet2!$B$2:$B$300".

:cool:
 
Upvote 1
You must log in or register to reply here.

Forum statistics

Threads
1,223,884
Messages
6,175,177
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top