Possible vlookup

[HopDevvil814]

So we are having our employees scan their badges as they get onto campus now, along with a temp check. Once scanned, their data is put into a spreadsheet that includes mm/dd/yyyy hh:mm, name, ID #. I have another spreadsheet from ADP that has the employees information on it. I want to check to see if employee is suppose to be on campus at the time of scanning in using both spreadsheets. I was thinking of using a vlookup, but not sure if this would be the correct method.

The first image is from the scanned badge, second image is from ADP. The ADP image is from one person, showing their 4 hours before lunch, lunch, then 4 hours after lunch.

[etaf]

rather than images , can you post sample spreadsheet with examples of the outcome results you need

[ChemistB]

Second Wayne's request. From the images, I don't see anything in the second image linking back to the first (i.e. name or ID). Also can't tell where you would want the check to occur. Workbooks (or combine the 2 workbooks into a single workbook w. 2 sheets) without confidential information, of course, would speed things up greatly.

[HopDevvil814]

Date/Time	Who	ID #
11/9/2020	Doe, Jane	49168
11/9/2020	Green, Green	64694
11/9/2020	Smith, John	19498

Name

Acct

Hours

ProjHours

Status

Phone 2

Phone 3

Row Type

Day

PrimaryJob

Shift Start Date

Shift Start Time

Shift End Time

Shift End Date

Type

Segment Start Date

Segment Start Time

Segment End Time

Segment End Date

Sch Hours

Doe, Jane

0010011

8

0

789-854-7514

Shift

Mon

11/9/2020

8:00AM

5:00PM

11/9/2020

Regular

11/9/2020

8:00AM

12:00PM

11/9/2020

4

Doe, Jane

0010011

8

0

789-854-7514

Shift

Mon

11/9/2020

8:00AM

5:00PM

11/9/2020

Break

11/9/2020

12:00PM

1:00PM

11/9/2020

1

Doe, Jane

0010011

8

0

789-854-7514

Shift

Mon

11/9/2020

8:00AM

5:00PM

11/9/2020

Regular

11/9/2020

1:00PM

5:00PM

11/9/2020

4

I really just want to check the employees start time to see if they scanned there ID when they are suppose to start their shift. This would imply that they had a temperate check completed.

[etaf]

a spreadsheet would be better, saves us typing things into a creating one to give a full answer

Will the NAMES be a perfect match between the 2 systems, as you don have USER ID on 2nd sheet to match against

you could use a countifs() to see if the dates are correct

[HopDevvil814]

I'll get you a spreadsheet. As for the names, one does include the middle initial, the other does not. I might be able to get this changed. The ADP spreadsheet does not include the Badge # so we cant use that.

[HopDevvil814]

See attached.


So I guess if they scanned their badge and it is between the date/time scheduled then it would come back true.

[etaf]

=COUNTIFS([Badge2.xlsx]Sheet1!$A:$A,B1,[Badge2.xlsx]Sheet1!$K:$K,INT(A1))
Will count if the date & name match - so you can use
=COUNTIFS([Badge2.xlsx]Sheet1!$A:$A,B1,[Badge2.xlsx]Sheet1!$K:$K,INT(A1))>0
will give a true and false
BUT will need both workbooks open

if the names are different , then that will be a problem, as you may have 2 employees with the same name

[HopDevvil814]

Perfect, that's a good starting point. I'm sure I will have some revisions.

[HopDevvil814]

Okay, So I have decided to add another column try to parse out the first name and last name.

Please Login or Register  to view this content.

This is the code I am using, but it doesn't seem to be consistent. Another issue I have with it is that if the employee has two last names, or if they are the II(2nd). How can I get the last name, then take the name after the comma?

[Glenn Kennedy]

With names in B1, as per earlier sheet:

=TRIM(MID(SUBSTITUTE(","&$B1,",",REPT(" ",125)),125*COLUMNS($A:A),125))

copied across and down.

[HopDevvil814]

Thank you for the quick response Glenn! What if I wanted the last name and first name in the same column separated by a comma?

[Glenn Kennedy]

Can you post a small sheet showing what you have and what you want?

[HopDevvil814]

It would be the same data as you just sent me. But, instead of having the last name in column D and the first name in column E, could I have last, first in column D? In column B the names are last name, first name middle initial. Smith, John T

[Glenn Kennedy]

Hahahaha! Glenn reaches for the gin bottle. "could I have last, first in column D" Divided by a common language. I don't follow that bit at all. As requested show me on a sample sheet.

[HopDevvil814]

My bad haha. Here you go

[Glenn Kennedy]

Is this it???

=IFERROR(LEFT(B1,SEARCH(" ",B1,SEARCH(",",B1)+2)-1),B1)&""

[HopDevvil814]

That's the one! Cheers

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[HopDevvil814]

Alright I am back at it, of course Admin wanted some changes. So now I have the two files into 1 xlsx. before it was actually a .csv and a .xls. Also now we have the employee ID instead of trying to compare the names.

I am still using the same code I had before and tried to adjust it. Not sure if my issue is how I have it set up looking at the two sheets.

[Glenn Kennedy]

?? Is this it?

=SUMPRODUCT((Sheet2!$A$2:$A$5=Sheet1!B2)*(INT(Sheet2!$D$2:$D$5)=INT(Sheet1!A2)))

[HopDevvil814]

That works, but only if the values are on the same lines on each sheet. If I move the top employee from row 2 to row 4 on sheet 2 it will come up as a 0.

[Glenn Kennedy]

??? See sheet. I suspect you messed up the formula ranges when doing your test.

[HopDevvil814]

Your'e right, my bad. Its's been a long morning.

=SUMPRODUCT((Sheet2!$A$2:$A$5=Sheet1!B2)*(INT(Sheet2!$D$2:$D$5)=INT(Sheet1!A2)))

So this is looking in the range of A2:A5 and D2:D5, how can I search all of $A and $D? I tried removing the the numbers so it looks like this:

=SUMPRODUCT((Sheet2!$A:$A=Sheet1!B2)*(INT(Sheet2!$D:$D)=INT(Sheet1!A2)))

And it get a #VALUE!

[Glenn Kennedy]

Don't. SUMPRODUCT should NEVER be used with whole column references. It gets soo slow.

[HopDevvil814]

Ah, good to know. The only issue with how we have it right now is that I wont know how many cells the report will have in it. This will be ran daily with a different number of employees working each day.

[Glenn Kennedy]

Run with a range that is sensible. If you have 25 employees ± 10, set it to 100. Be generous, but don't go over the top!!

[HopDevvil814]

Got it! So now the issue I am having is if I change the employee ID on sheet 2, I am still getting 1 or true as a result.

[Glenn Kennedy]

Maybe it's back in the mists of time, at the start of this thread, before I got involved, but I don't recall you asking for the employee ID to be matched as well.

It is now!!

=SUMPRODUCT((Sheet2!$A$2:$A$5=Sheet1!B2)*(INT(Sheet2!$D$2:$D$5)=INT(Sheet1!A2))*(Sheet2!$B$2:$B$5=Sheet1!D2))

[HopDevvil814]

The employee ID is what started today's adventure haha. So we are no longer comparing name and date, now it is employee ID and date. This is because the names were put in different in each system.

I'm going to try this:

=SUMPRODUCT((INT(Sheet2!$D$2:$D$5)=INT(Sheet1!A2))*(Sheet2!$B$2:$B$5=Sheet1!D2))

[Glenn Kennedy]

yea. I got involved regarding pasing Jr II, etc.

=SUMPRODUCT((INT(Sheet2!$D$2:$D$5)=INT(Sheet1!A2))*(Sheet2!$B$2:$B$5=Sheet1!D2))

will do date and ID No, I think!! I hope!!

[HopDevvil814]

Well that was working great, but of course they had to throw in another curveball. On sheet 2 now there is some more columns. As you can see there can be a date listed, but no shift scheduled. So the way we currently have it set up this would come back as true. I think I need to look at the date column along with the 3rd column which would list shift or none. We could say if the dates are the same and if column 3 = shift then true or 1.

[Glenn Kennedy]

Is this what you wanted?

=IF(SUMPRODUCT(--(Sheet2!$F$8:$F$51=INT(Sheet1!A2)),--(Sheet2!$B$8:$B$51=Sheet1!D2),--(Sheet2!$C$8:$C$51="Shift"))>0,TRUE,"")

[HopDevvil814]

That works! What if I want to do the opposite? So on sheet 2 I want to know if the on the employees scheduled date, if they have scanned their badge in or not. Could I just use the code in reverse? I tried this but it is coming up as false.

=IF(SUMPRODUCT(--(Sheet1!$A$2:$A$500=INT(Sheet2!F2)),--(Sheet1!$D$2:$D$500=Sheet2!B2),--(Sheet2!$C$2:$C$500="Shift"))>0,TRUE,FALSE)

Sorry for being a pain, its impossible to please these people..

[Glenn Kennedy]

I'm not entriely sure which criteria you need to apply, but:

=IF(SUMPRODUCT((Sheet2!$F$2:$F$45=INT(Sheet1!A2))*(Sheet2!$B$2:$B$45=Sheet1!D2)*(Sheet2!$C$2:$C$45="Shift"))>0,"Shift","No shift")

[HopDevvil814]

I want to look at the schedule(sheet 2) and compare to see if a person shows up on sheet 1. I would still be using the same criteria, emp ID, date, shift.

It should look at F2 on sheet 2 and look for that data in column A on sheet 1, then look at B2 on sheet 2 and look for that data in column D on sheet 1.


=IF(SUMPRODUCT(--(Sheet1!$A$2:$A$50=INT(Sheet2!F2)),--(Sheet1!$D$2:$D$50=Sheet2!B2),--(Sheet2!$C$2:$C$50="Shift"))>0,TRUE,FALSE)

I tried this one too

=IF(SUMPRODUCT((Sheet1!$A$2:$A$50=INT(Sheet2!F2))*(Sheet1!$D$2:$D$50=Sheet2!B2)*(Sheet2!$C$2:$C$50="Shift"))>0,"Shift","No Shift")

I guess I am not understanding why I cant switch the values around in the SUMPRODUCT. I even tried to simplify it and still didn't have any luck.

I attached the spreadsheet with the code you provided.

[HopDevvil814]

I expect to see the same results that we had previously on sheet 1 column F, but now have those results be on sheet 2 column AA. When I tried moving the script from one sheet to the other I was getting errors. When I had originally requested this help I was going off from the sheet where people had badged in rather than the sheet with the schedule.

[Glenn Kennedy]

Isn't there something fundamentally wrong here?? There is only one date per person on sheet 1... so you can only expect a couple of results on sheet 2.

=IF(SUMPRODUCT((Sheet1!$D$2:$D$5=Sheet2!B2)*(INT(Sheet1!$A$2:$A$5)=Sheet2!F2))>0,"Present on sheet 1","Not Present on sheet 1")


or am I still missing the point

[HopDevvil814]

I think this will work for me. Ive been in meetings all day so I couldn't get back to you sooner. Thank you!!

[Glenn Kennedy]

I know the feeling. I had lead a training session in Sao Paolo for an hour today. In happier days, I would have been down there in Brazil, not in my home office.

Sod COVID.

But, you're sorted!! Yee Haw!!

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.