Lookup using 2 criteria

[Itsbarbara]

I have 2 Spreadsheets. Employee and Check_IN.



The first (Employees)is a list of employee ids with a column for each workday.

The second (Check_IN) lists each employee id with the date and time they clocked in (Therefore, each employee is listed several times, once for each date checked in).

I want to combine the id and date on each and return the time clocked in on the Employee SS in the relative date column. Of course, if they did not check in, no value would be entered.


I'm attaching a sample of each and would appreciate any help. I've tried several scenarios and can't seem to solve this...would really appreciate any help!!

[BobBridges]

If I understand you right, Barbara, you want each cell in the tbl_Employee worksheet to look up a time from the EmployeeData worksheet where it matches the correct emp ID and date. Now, if it were me, I'd create on the EmployeeData worksheet a helping column concatenating the employee ID and the date, and then do an ordinary VLOOKUP up the concatenated data. But Pecoflyer has this neat trick he does with array formulae that will probably save you having to make the helping column; I haven't figured it all out yet, but I'll bet he'll be able to come up with something that'll work better than my idea.

[Itsbarbara]

I think I love you.

I have worked for hours, and I tried something similar, however, you simplified it for me...and Viola!!!

This worked:
=VLOOKUP(A2&B$1,[Check_IN.xls]EmployeeData!$A$3:$E$1257,5,FALSE)

[BobBridges]

Always nice to be loved, or even appreciated. But trust me, Pecoflyer's solutions in cases like this are harder to follow but much neater; I've gotta learn to do them myself soon.

[Pecoflyer]

Hi Itsbarbara,

the solution you found thanks to Bob's suggestion is perfect. The use of helper columns is advocated by many MVP's.

The drawback might be that using VLOOKUP on very large tables slows things down but I don't expect there will be 65000 employees/time combinations or more, or several hundred dates.

An alternative , without a helper column is

=SUMPRODUCT(([Check_IN.xls]EmployeeData!$A$3:$A$1257=$A2)*([Check_IN.xls]EmployeeData!$C$3:$C$1257=B$1)*[Check_IN.xls]EmployeeData!$D$3:$D$1257)

I suspect it won't be any faster, but in any case, it will give Bob something to chew on .

FWIW in your EmployeeData table you can enter =C3 in B3, pull down and custom format as dddd. So you don't have to type the days

@ Bob BTW thanks for the advertising, but I'm far from being good at Excel

[Itsbarbara]

Pecoflyer,
Oh, Wow, I would never have figured this one out. (I think I love you too!) This would be much better as the Check_In file is a report that comes directly from the Check In Software (Days of week included) and I need it as clean as possible for the people who will be managing this. Not having to add the helping column is a huge plus.

Only one problem, however, it returns a 12:00 time whenever there is no data in the [Check_In]EmployeeData! I need for that to either, be blank or return a #N/A as that is one of the crucial items that needs to be tracked (whether they just didn't check in bc they were late, forgot, etc).

Is there a way to resolve this?

Also, It worked without a Cntrl-Shft-Enter....but I did it both ways to check.
Can you enlighten me there as well?

Guess you can see that I am just a user, not a guru like you and Bob.

[Itsbarbara]

Well, I used conditional formatting and made the 12:00 AM time a white font. This works for the users if I protect the spreadsheet so they cannot change anything on the tbl_Employee.

I welcome any other suggestions, however. You guys think of things that I cannot.

[Pecoflyer]

Perhaps this will help :

=IF(SUMPRODUCT(([Check_IN.xls]EmployeeData!$A$2:$A$1256=$A2)*([Check_IN.xls]EmployeeData!$C$2:$C$1256=B$1)*[Check_IN.xls]EmployeeData!$D$2:$D$1256)=0,"NA",SUMPRODUCT(([Check_IN.xls]EmployeeData!$A$2:$A$1256=$A2)*([Check_IN.xls]EmployeeData!$C$2:$C$1256=B$1)*[Check_IN.xls]EmployeeData!$D$2:$D$1256))

pulled down and across.

SUMPRODUCT is an array formula in itself, so no CSE is needed. You'll find a nice explanation on this function at http://www.xldynamic.com/source/xld....T.html#classic

[BobBridges]

Nice! Two ideas, Barbara:

1) Even if you'd wanted to keep the helping column (I'm not suggesting it), you could do it by just hiding it, either out to the right where no one would think to look or just making its column width 0. For my own use (I don't really like hidden columns, I like to be able to double check), I often color the whole sheet grey and then restore normal color for the non-helping columns I'm using at the left; it helps keep the eye focused away from the helping columns at the right. But for management types, hiding them may be better—and your idea of doing without helping columns entirely is probably better still, when possible.

2) When there is no data—no check-in time—the formula's result is 0, which your worksheet is displaying as "12:00 AM". Your solution is ingenious; I don't think I'd have thought of that. Pecoflyer's formula fixes it another way. But for my money it's probably simpler still to keep his original solution and use a formatting code that displays 0 values as either blanks, as his suggested "NA" or as anything else you choose.

If you're never made up your own formatting codes, they're not hard. Try this:

a) Examine the format of one of the tbl_Employee cells that displays that 0 value as "12:00 AM". I presume the format Category is "Time" and the Type is "1:30 PM".

b) Now click on "Custom" at the bottom of the Category list; Excel shows you the format code for that standard setting. Yours might say "h:mm AM/PM;@"; I don't know why, but mine says "[$-409]h:mm AM/PM;@". In any case, make one small change: Add a semicolon to the end of the string.

c) Hm, wait a minute; mine claims that Microsoft Excel cannot use that format string. Ok, forget the fancy stuff (I didn't know why it wanted an ampersand in that position anyway) and change it to "h:mm AM/PM;-General;". I'll explain below.

d) Ok, now click OK and look at the results. The cells that should be empty now appear to be.

This has the additional advantage of not needing to change Pecoflyer's original solution, without the IF function; I expect it's faster to run, as a result, but I haven't tested it to see.

---

So what did the pieces of that format string mean? Excel format strings come in four semicolon-delimited parts:

• Positive numbers
• Negative numbers
• Zero
• Character strings

Anything that isn't specified is assumed to use default display. So the original string that my Excel was using, "[$-409]h:mm AM/PM;@", went like this:

• If the value was positive, use "[$-409]h:mm AM/PM". I don't know what "$-409" means, but it happens that normally my Regional Settings call for military time, "hh:mm:ss"; I changed the format code in Excel for the purpose of this test, so maybe perhaps "[$-409]" is a reference to that. What normally goes inside the optional square brackets is something like "Red" or "Bold", to change the default font in such cases.
• The "@" seems to mean that if the value is negative, display it as a character string. I've never tried using that in the second position (only in the last), and it isn't clear to me what it could mean—especially as Excel itself rejected it as soon as I tried to use it myself. Above I had you change it to "General" (or "-General", either one), which is a sort of default-for-everything setting.
• There's no semicolon after the negative value, so zero and strings are displayed normally.

But by putting the semicolon at the end, I specified an empty string, "nothing", for the third value; so zeroes are displayed as blanks. If you want Pecoflyer's "NA" instead, just make the format string

h:mm AM/PM;-General;"NA"

[Itsbarbara]

Bob & Pecoflyer
Just got back to this and see that you guys have helped me even more. I so appreciate it!!

Bob, I wouldn't mind the helping column, but the users will be exporting the data on a weekly basis and replacing the [Check_In]EmployeeData worksheet and I would rather that they don't have to add that column.

I like the custom formatting idea. I now understand that much better. (BTW[$-409] is the formatting code for English(U.S.), [$-804] is code for Chinese, etc. You piqued my interest and I had to look it up ) Thanks a bunch.

Peco,
Thank you for the solution you presented but also for the link to the explanation of the SumProduct function. Your formula looked so daunting and I couldn't figure out how that worked. Now I understand the logic and can replicate it in the future.

You guys are geniuses!!

[BobBridges]

You hear that, Bre'er Pecoflyer? "Geniuses".

We'd better enjoy it now, though, because the next time one of us misses one, someone will be singing us a different tune. I just hope it won't be Itsbarbara; that'd be embarrassing.

[Pecoflyer]

FWIW XL2010 provides the SUMIFS function to replace the SUMPRODUCT, but I never mastered the syntax...

[Itsbarbara]

For the Geniuses (Please ):

I have a pretty little Spreadsheet now that is user friendly, locked cells with formulas, conditional formatting in red if employee checks in late, etc. ....but a couple of problems:

1) Takes a little over 4 minutes to open and calculate.
BTW, it says Calculating 4 Processors and slowly goes through the % being calculated.....as I wait.

2) I need to allow for additional entries I increased the lookup range to allow for additional entries as the year progresses (Probably the reason for problem#1)

ie: 150 employees, * 190 workdays I increased the range from a1:f1256 (current data that I'm working with) to A1:F29000 (allows for remainder of the year as data is

added) ....I'm wondering, should I convert the data to a table?

3)If I do convert to a table, how do I fill the formula relating to the Data Table, It goes to the next column as I fill to the right.....

[BobBridges]

These are just ideas, Barbara:

2) I remember looking at that original we worked on and thinking that it's an awful lot of detail. It shouldn't take 4 whole minutes to load and calculate, but then I assume the real worksheet has a lot more to do; what we looked at is probably just a sample, right?

But why? I mean, who needs to know the check-in time for each of those employees for every single day? Who, even if the information is provided, will actually pay attention to the details, and why?

Without knowing that I can't really offer an opinion, but I can't help suspecting that there are ways to reorganize the data, or to offer selected subsets of it, so that it'll work a lot faster. For example, if certain managers are looking at the info for just their own subordinates, then why have all the data on one enormous worksheet? Maybe each could have a smaller (and therefore faster) worksheet that looked up just his own people's data.

1) But a really effective change would be to for the database's administrator—that's you, I expect—occasionally to convert the lookups for past days to literal data. The date is in the column, right? So every week or so, go in there, copy all the data for the past weeks (columns) and paste-special as values; the VLOOKUPs, SUMPRODUCTs, all the formula will be converted to literal values and the only lookups and calculations left will be the ones for the future columns, which you carefully left unconverted. I'm confident you'll find that makes a big difference to the load time. This is assuming, of course, that once you have a check-in time for a particular date, it won't change thereafter. If you know you sometimes get data entered late, just leave a week unconverted an extra week, or whatever it takes until you're sure its data have settled down. And if you ever do have to change a check-in time after you've already converted it to values, then you'll just have to change in two places, that's all, ie on both sheets. But that won't happen very often, unless I'm misunderstanding the purpose of this thing.

2) But I repeat, I wonder about the purpose of the thing anyway (see above).

3) If you want to get fancy, you could do something with VBA; it might give you the best of both. I'm thinking of a Worksheet_Change subroutine that would spot changes in the first sheet and respond by writing them to the proper place on the second sheet at the same time. Then the second sheet would never have to do any lookups at all (so it would load fast), and yet any post-dated updates would still take place automatically. But without knowing who's going to be looking at this, and it what form, it's hard for me to know whether that's a good idea.

[Itsbarbara]

Yes, the original was just a sample.

They do look at this, thus I have additional columns that summarize the data.

I am a former data administrator (retired), and am creating this for a friend who needs the data for employee evaluations. They spent a chunk of change on a check_in system that uses a key fob that transmits when they pass the station, as the employees are spread out and it is imperative that everyone be at their duty station on time.

Since my last post, I went ahead and tried the Table option for the Data Table, and that helped a lot. I also changed the calculation to Manual so that I wouldn't have to wait.

I have thought of converting the formulas to values, but since I'm not the one who is actually going to be managing this, I had not done that yet (I actually only have a portion of the data). I think that is a good idea though, and I think they only pull the data from the system at the end of the month, so that could be part of their process.

They get the data report from the system in an Excel doc, so they could append the data, calculate, then convert to values, and the summary columns would automatically calculate without any problem.

I dabbled in VBA, but it has been quite a while.

This may just be the best way to approach this so I can hand it off.

Thanks again!