LOOK UP DATA BASED ON ID NO. CHOSEN (again)

Hi again experts.

This is in relation to my last question but it now has to retrieve the data based on the Employee ID number chosen.

I have attached an example workbook, the times in RED are what I need to retrieve from sheet "FP Reader" based on the Empoyee no. chosen, and then the times put in the aproprate day, IN or OUT times.

Can you please help...I have been trying for ages to make this work.

May Thanks


Rob
Book-DATA-LOOKUP.xlsx
RobJanineAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RobJanineAuthor Commented:
sorry, I deleted the last question as I put the wrong zone in.
Here we go with the correct zone now.

Cheers
Rob
0
dlmilleCommented:
Ok, here's a solution - pasted in cell C5, then copied down, and copied across to columns F, I and L

=SUMPRODUCT(('FP Reader'!$H$5:$H$21)*('FP Reader'!$B$5:$B$21=C$4)*('FP Reader'!$J$5:$J$21=$B5)*(TEXT('FP Reader'!$C$5:$C$21,"M/D/YYYY")=IF($B5="OUT",TEXT($A5,"M/D/YYYY"),TEXT($A6,"M/D/YYYY"))))

One of the side-effects of this formula and your formatting is the result of zero value.  Zero means no match, but formatted as you have it would return 12:00:00 AM. So, we enhance the formula to check for zero value, and we're done!

=IF(SUMPRODUCT(('FP Reader'!$H$5:$H$21)*('FP Reader'!$B$5:$B$21=C$4)*('FP Reader'!$J$5:$J$21=$B5)*(TEXT('FP Reader'!$C$5:$C$21,"M/D/YYYY")=IF($B5="OUT",TEXT($A5,"M/D/YYYY"),TEXT($A6,"M/D/YYYY"))))=0,"",SUMPRODUCT(('FP Reader'!$H$5:$H$21)*('FP Reader'!$B$5:$B$21=C$4)*('FP Reader'!$J$5:$J$21=$B5)*(TEXT('FP Reader'!$C$5:$C$21,"M/D/YYYY")=IF($B5="OUT",TEXT($A5,"M/D/YYYY"),TEXT($A6,"M/D/YYYY")))))

The formula might be cleaned up a bit, if we were working with the same datatype across all the dates, but for "simplicity", I convert all to text e.g., TEXT(A5,"M/D/YYYY") so all are compared on the same basis.

Let's break the basic formula down:

=SUMPRODUCT(('FP Reader'!$H$5:$H$21)*('FP Reader'!$B$5:$B$21=C$4)*('FP Reader'!$J$5:$J$21=$B5)*(TEXT('FP Reader'!$C$5:$C$21,"M/D/YYYY")=IF($B5="OUT",TEXT($A5,"M/D/YYYY"),TEXT($A6,"M/D/YYYY"))))

('FP Reader'!$H$5:$H$21) - is the time that we want as the result
('FP Reader'!$B$5:$B$21=C$4) - checks the employee ID number
('FP Reader'!$J$5:$J$21=$B5) - checks for match on IN or OUT
TEXT('FP Reader'!$C$5:$C$21,"M/D/YYYY")  - sets up the date check, and
IF($B5="OUT",TEXT($A5,"M/D/YYYY"),TEXT($A6,"M/D/YYYY")) - compares that date to the correct row - the one having "OUT" in it, as it holds the date in column A

See attached demo workbook:

Dave


Book-DATA-LOOKUP-r1.xlsx
0
RobJanineAuthor Commented:
Thanks,  only thing is I need to allow for alot more potinual data. eg.I need to look at all of column H from H5 downwards.
I tryed changing it but the formula read #N/A

any suggestions??
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

dlmilleCommented:
I just had to coerce a null value (due to your if statement on FP Reader, re: time clocked in/out) to a zero in the null scenario.

How about 1000 rows?

See attached.

Dave
Book-DATA-LOOKUP-r1.xlsx
0
RobJanineAuthor Commented:
Sorry formula puts #VALUE! if it looks at rows with no data
0
dlmilleCommented:
Would you like to look at what I posted?

Dave
0
dlmilleCommented:
Just to be clear, I corrected the issue you teed up with #VALUE.  I concatenated the H5:H1000 parameter with 0, thus:

SUMPRODUCT(('FP Reader'!$H$5:$H$1000&0) which eliminates the error in the SUMPRODUCT when you introduce the NULL value in the FP Reader sheet.

As a result, your formulas should now be clean, and they are set for 1000 rows.  Feel free to increase the number of rows, as needed.

Please advise if you have any additional issues.

See attached,

Dave
Book-DATA-LOOKUP-r2.xlsx
0
RobJanineAuthor Commented:
Thanks for your help...your formula worked, but is to slow. When i import the data into 'FP Reader' the formulas are tryng to run and slows the import and sorting of inital data. Is there a way of runing this with code, say on a button or something, or do you have any other suggestions?

Rob.
0
barry houdiniCommented:
Not for points....

Rather than repeat the formula to eliminate 12:00:00 AM one option is to custom format result cell, i.e.

hh:mm:ss AM/PM;;

note semi-colons

regards, barry
0
J79123Commented:
It looks like you're using 2007 or 2010, so you can use tables, which makes the formulas a bit easier to follow. So I put your timesheet data list into a 'table' so that the ranges can be referenced with names and not ranges. This can be a big help if you just want to add data to that table. but it also makes the formulas easy to understand, so that you can change them once you see how things work

Next on your calendar page where you want the data to sum:
 the lines that say the day of the week *next to 'IN' were hardcoded as text value days. But if you change those cells to the date you want to lookup (the date listed in cell below it)' then you can use that 'day name' cell as your match value. Then you don't need to make your formula any longer than necessary. you only need to change the cell format to DDDD to show the name of the day.

next when trying to match the punch in/out times to the day that's being reported, all you need to do here is match the integer value of someone's punch in time to the report date that you are looking up.
for example the excel true number value of 8/31/11 5:10am 40786.22. and the date you want to lookup might be 8/31/11 (excel value 40786). So the int function is probably quickest way to get to the day information in a date/time value.

another format trick... if there are no matches the time will sum to zero, which with time formatting looks like 12:00. but cell formatting lets you specify how you want positive, negative and zero values to look. so I changed the cell format to show a time format for positive numbers and a null value for zero values. (right click and select cell formatting to see how that works) No formula necessary to clean up the non-matches (zeros)

Finally sumproducts are used in the formulas because they let you sum with multiple criteria and let you manipulate the data you are looking up (like the int function part) but this means that each formula is a mini array. Not sure how big your data is.. but it could get slow if it gets huge.
And this also means that if you need to change these formulas, instead of typing enter after making changes, you need to hit control+shift+enter. you will know you did it right if brackets appear around the formula in the formula bar.
 Book-DATA-LOOKUP-with-sumprods.xlsx
0
Rory ArchibaldCommented:
Probably more efficient to use a key column if you can, and then reduce the formula to:
=IFERROR(IF(INDEX('FP Reader'!$H:$H,MATCH(C$4&"|"&INT($A6)&"|"&$B5,'FP Reader'!$A:$A,0))="","",INDEX('FP Reader'!$H:$H,MATCH(C$4&"|"&INT($A6)&"|"&$B5,'FP Reader'!$A:$A,0))),"")

See attached.
Book-DATA-LOOKUP-r1.xlsx
0
Rory ArchibaldCommented:
@J79123,
Your SUMPRODUCTS do not need array-entering, BTW.

Regards,
Rory
0
Rory ArchibaldCommented:
@dlmille,
FYI, in this instance rather than using &0 in your SP formula, you can just replace the * with a comma and text and empty cells will be ignored:

=SUMPRODUCT('FP Reader'!$H$5:$H$21,('FP Reader'!$B$5:$B$21=C$4)*('FP Reader'!$J$5:$J$21=$B5)*(TEXT('FP Reader'!$C$5:$C$21,"M/D/YYYY")=IF($B5="OUT",TEXT($A5,"M/D/YYYY"),TEXT($A6,"M/D/YYYY"))))

rather than:

=SUMPRODUCT(('FP Reader'!$H$5:$H$21&0)*('FP Reader'!$B$5:$B$21=C$4)*('FP Reader'!$J$5:$J$21=$B5)*(TEXT('FP Reader'!$C$5:$C$21,"M/D/YYYY")=IF($B5="OUT",TEXT($A5,"M/D/YYYY"),TEXT($A6,"M/D/YYYY"))))
0
dlmilleCommented:
Thanks, rory, that's a good tip.

Dave
0
dlmilleCommented:
@RobJanine - Please try this solution, incorporating input from barryhoudini and rorya.  Let's see if this is fast enough.  Otherwise, I can look into a macro to update the data.

Dave
Book-DATA-LOOKUP-r3.xlsx
0
dlmilleCommented:
Here's your solution in a macro.  The assumption is that the last row for formulas is the one above the TOTAL line in column B, that the formulas should be underneat the employee number, skipping the column to the right of each.  The app pastes an original formula, copies to all the appropriate cells by column, converting each column to VALUES (eliminating the formula) as it progresses...

Let me know if this suits.

Here's the code:
Sub updateFormulaToValues()
Dim wkb As Workbook
Dim wks As Worksheet
Dim myCell As Range
Dim lastRow As Long, lastCol As Long
Dim fRange As Range
Dim firstFormula As Range
Dim entirePasteRange As Range

    Application.ScreenUpdating = False
    
    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("Sht 1 - Table 1")
    
    Set fRange = wks.Range("B:B").Find(What:="TOTAL", LookIn:=xlFormulas, LookAt:=xlWhole)
    
    If fRange Is Nothing Then
        MsgBox "Could not find TOTAL line", vbCritical, "Aborting..."
        Exit Sub
    End If
    
    lastRow = fRange.Row - 1
    fmula = "=SUMPRODUCT(('FP Reader'!$H$5:$H$1000),('FP Reader'!$B$5:$B$1000=A$4)*('FP Reader'!$J$5:$J$1000=$B1)*(TEXT('FP Reader'!$C$5:$C$1000,""M/D/YYYY"")=IF($B1=""OUT"",TEXT($A1,""M/D/YYYY""),TEXT($A2,""M/D/YYYY""))))"
    
    Set firstFormula = wks.Range("A1")
    firstFormula.Formula = fmula
    
    lastCol = wks.Cells(4, wks.Columns.Count).End(xlToLeft).Column
    
    For Each myCell In wks.Range("C4", wks.Cells(4, lastCol))
        If IsNumeric(myCell.Value) And myCell.Value <> "" Then 'copy formula down
            firstFormula.Copy
            wks.Range(Cells(5, myCell.Column), Cells(lastRow, myCell.Column)).PasteSpecial xlPasteFormulas
            Application.CutCopyMode = False
            
            'now convert to values
            wks.Range(Cells(5, myCell.Column), Cells(lastRow, myCell.Column)).Copy
            wks.Range(Cells(5, myCell.Column), Cells(lastRow, myCell.Column)).PasteSpecial xlPasteValues
            
            Application.CutCopyMode = False
        End If
    Next myCell
    
    Range("A1").Clear
    Range("A1").Activate
    Application.ScreenUpdating = True
End Sub

Open in new window


See attached.

Dave
Book-DATA-LOOKUP-r3.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RobJanineAuthor Commented:
Thankyou all so much for all your work.

rob.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.