?
Solved

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

Posted on 2011-10-09
17
Medium Priority
?
186 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:RobJanine
  • 7
  • 5
  • 3
  • +2
17 Comments
 

Author Comment

by:RobJanine
ID: 36940579
sorry, I deleted the last question as I put the wrong zone in.
Here we go with the correct zone now.

Cheers
Rob
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36940667
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
 

Author Comment

by:RobJanine
ID: 36940696
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 42

Expert Comment

by:dlmille
ID: 36940726
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
 

Author Comment

by:RobJanine
ID: 36940728
Sorry formula puts #VALUE! if it looks at rows with no data
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36940729
Would you like to look at what I posted?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36940753
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
 

Author Comment

by:RobJanine
ID: 36940835
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 36940855
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
 
LVL 6

Expert Comment

by:J79123
ID: 36940903
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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 800 total points
ID: 36940922
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36940929
@J79123,
Your SUMPRODUCTS do not need array-entering, BTW.

Regards,
Rory
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36940937
@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
 
LVL 42

Expert Comment

by:dlmille
ID: 36943153
Thanks, rory, that's a good tip.

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36943756
@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
 
LVL 42

Accepted Solution

by:
dlmille earned 1200 total points
ID: 36943936
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
 

Author Closing Comment

by:RobJanine
ID: 36946477
Thankyou all so much for all your work.

rob.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question