Filter Data To Show Only Employees Who Are TRUELY Tardy
Nov 19, 2009
I have an excel sheet with over 4,000 rows of records regarding our 80+ employees clocking in. There are three columns:
A: Date (MM/DD/YYYY)
B: Time (HH:MM:SS)
C: Employee Name
Our employees are expected to be at work (and clock-in) at or before 8am.
At first, I just filtered the data to display only rows that have a time value of between 8am and 9am (the hour when most people are tardy). However, I now realize this is not a very accurate way of reporting tardiness.
Our forklift driver, for example, almost always arrives at 7:45am, but then works around the entrance and has to scan his card (clock-in) several times throughout the morning to get back into the building. So if he's moving something outside and comes BACK inside at 8:30am, my current "filter" inaccurately reports him as tardy for that day.
Is there some way to filter this data to first reduce it down to their earliest clock-in for the day, THEN isolate only the 8am - 9am data (if we did that, the forklift driver problem would be solved because his only time for that day would be 7:45am - his TRUE clock-in). The forklift driver is just one example. Many other employees occasionally go in/out of that door and end up clocking in multiple times, so are getting wrongly reported as tardy.
I have aworkbook with Employee details data sheet. And, I have built two userforms to search and view the employee’s full details. I don’t know how to link the rowsource for combobox and textbox.
I have attached the excel file of what I'm trying to accomplish.
I have an excel file with 2 tabs. tab 1: Active and Tab2: tenure. Tabe 1 has the following column headers Manager Employee Name Dept Assignment Original Start Date End Date Bill Rate
The Employee names may be repeating in this tab becasue whenever the employee is extended for a new assignment he has a new start date and new end date and new assignemtn and rates....This helps track the historical information about the employees. TAB2: (tenure) also has the following column headers...................
My task is to write a macro on auto filtering. I got 2 columns of relevant data. Cells in column A show the team name and corresponding cell in column C shows the person who belongs to the team.
For clear illustration, this is an example of the list I got.
A1: A, C1: XX A2: A, C2: YY A3: A, C3: ZZ A4: B, C4: XX A5: B, C5, 00 A6: B, C6: 11
What I need to do is to write a macro that auto filters and shows only relevant data.
Assume XX used to be in team A but now moved to team B. When i filter for team A, i need all members who are/were in team A.
For instance, when i want team A, it has to include A1-A4 and when i want team B, it has to include A1,A4-A6.
Im looking for a way to select between multiple choices linked to one criteria. Eg criteria beeing Chain x Article 1, which should give me the option to show and select from multiple unique values linked to that criteria (in attached example values 1 or 30 in column AQ marked in yellow). If possible solution should be formulas/functions.
My goal is for any of my team members to be able to use a macro to delete all of the unwanted text from a fairly large worksheet and only show their specific information. I created it for myself and posted a slimmed down version of it below. Instead of the macro deleting any rows that do not have BRANDON SCOTT in column D, I want a pop up box to enter your name, and then any rows that do not have your name in column D will be deleted. I’m sure it is something simple, but I have not been able to find another forum with this information.
Sub MyTeam2() With Columns("D") . AutoFilter field:=1, Criteria1:="<>BRANDON SCOTT*" .Resize(Rows.Count - 1).Offset(1).EntireRow.Delete .AutoFilter End With End Sub
I am trying to get the correct formula to count the number of days it has been for each employee since the last occurrence of an absence/tardy. For each employee, starting with the beginning of the year, we mark and employee as Absent as an "A" or Tardy as "T". See below the examples.
Captain America ................................A ..........................T .....................................T Incredible Hulk.......................................................................................... A Spider Man ........................................................................................................................ ........A Iron Man ....................................................T
( I added dots because it wouldn't let me space them out?)
We are keeping track so that employees have the ability to make up numbers because after so many occurrences they can become terminated. If an employee has about 60 days in a row without an infraction, that employee can deduct a day from their total on the year.
Any formula to use. Also to include a vlookup so that I could have their name as well with the number of days on a separate tab!
I am trying to get the correct formula to count the number of days it has been for each employee since the last occurrence of an absence/tardy. For each employee, starting with the beginning of the year, we mark and employee as Absent as an "A" or Tardy as "T". See below the examples.
We are keeping track so that employees have the ability to make up numbers because after so many occurrences they can become terminated. If an employee has about 60 days in a row without an infraction, that employee can deduct a day from their total on the year.
Also to include a vlookup so that I could have their name as well with the number of days on a separate tab!
Column H has a list of names i.e. Fred, Barney etc.
Is it possible that when you select all the Barney names in header (the left one) shows the wording "Filter: Barney" ?
Just a thought only sometimes I have to filter and print off a sheet and the area with the names in does not get printed therefore a "Filter: xxx" would be quite useful.
i am using e2k and i have got to filter some data where the filter relevant data is in one column formated as "standard". it contains either only numbers or letters mixed with numbers. in case of a mixed cell the first symbol is always a letter. i need a way to filter only those rows which contain a mixed cell. the filter criteria should be something like !=#*
I need to create a macro to copy the data from time sheets of different employees into the Master sheet. ie., each employee's time sheet details in to a separate sheet. If any employee fails to provide his/her time sheet details. a mail must be sent to the respective employees regarding the Time sheet submission. I am providing the sample data of my files too.
Intentionally TS_Employee5.xlsx timesheet isn't provided. In which case, I have to send a mail to the respective employee, in order to remind him about the time sheet.
I have a list with many duplicate names in it, from which I only wanted 1 instance of each name, to be placed in another sheet. So, I recorded a macro that selects, from the sheet called "Data", column E (the column with the relevant list), copy/paste's it into sheet 3 in column A, selects the new column, and runs an Advanced Filter command on it, to identify and display all the unique names in it in the same place. It ran just fine when I recorded it. But then, when I deleted the data created by the macro and tried to re-run it, I got random data. I found that whenever I click on a different cell, it provides a different output. I only want it to select Column E, and copy paste that, not other random columns depending on which cell is currently selected. This is my
Sub Fetch_Unique_Names() Sheets("Data").Select ActiveCell.Range("A1").Select ActiveCell.Range("E1:E2200").Select Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.Range("A1:A2176").AdvancedFilter Action:=xlFilterInPlace, Unique _ :=True End Sub
I would like to be able to filter my pivot table to shows the rows with no data ?
I can make "no value" appear in the pivot table in 2 ways - either by using column field as well as row fields or by using the "show items with no data" option in value field settings but I cant get filter to show only the "no value" rows
I'm trying to create a formula (used in sheet 2 column B) that would generate the results in sheet2.B based on the contents of sheet 1. sheet2.B2 would contain a formula searching for the both "Y" in sheet1.C and the text in sheet2.A2 (in this case "E&P"). When each match is found, the contents of sheet1.A should be returned to sheet2.B, as shown in the mockup. There will be multiple matches (at least 15-20) for each search criteria, and I don't know how many there will be ahead of time.
I've tried various formulas, and they either have incorrect syntax and return every row in a range, or only return the first match correct match each time instead of all correct matches. I found some UDFs posted online that claim to do this, but I've gotten them into the spreadsheet and they generate # NUM errors. I would prefer to work with Excel-native formulas if possible, because I'm going to eventually hand this off to someone else to maintain and they may not have the level of expertise to deal with UDFs.
I have a very large table and i need to be able to Hide/show specific ranges based on:
Filter +and+ specific cell values in columns
brief example of the table : tablee.png
So... 1. Filter Column "B" (in this case we select "HELPING") 2. Auto hide/show collumns. - IF "C1" = "Required" THAN Show "C:E", IF "C1" ="N/A" , HIDE "C:E" and so on for every column like above.
There are over 80 columns like the "C:E" range. and I only need to show those that are "Required".
I have a database in Excel 2013 and now I want that when a value (a person's name) is entered in a cell. That then the database sort of filters the list for me, so it's still possible to make changes in the entries.
[URL]
Picture above to specify the search, which I would therefore like to edit
Dashboard_Action Pool Team 7.2.xlsm
I have been all morning working on a simplified version of the tutorial from YouTube: Create your own Excel Search Pt. 4. But came back later so only then that I can not change the data:?
I've a table of historical members status list. with this table, I could track each member status history, since the beginning they become a member.
Here is the short of table
Name Member Status Date
Adel New Member 1-Jan-14
[Code] ....
I expect to use Pivot table to show how many people which are still "New Member" and/or "Junior Member" up to now. From the table above, there should be: only one person who still as New Member. because it is only Smith, and only two people with Junior Member. they are Adel and Jhon.
I've search around and found the following useful link, viewing only the last date in a pivot table for each user also A quick way to return the latest date in a subset in Excel
How to know the last status of each user (each member in my case).
I have an issue with a Pivot table: I am using the 'show report filter pages' function and prior to this I have ensured my character length of that field is equal or less than 31 however when I hit the function the tab names are shortened to 26.
It is Gross Profit in Dollars and % for a particular project. Each row is a contract Item. The Overall Gross Profit for the month in dollars is 34,114.86 In preparing a short commentary to discuss this 34,114.86 I need to discuss atleast some contract items. If there were no negative results I have often in the past simply written. The top 5contributors to this Gross Profit were contract items 1 -6, out of 42, delivering 80% of the result. I might also go into what actually those contract items were and why they presented such a decent Gross Margin, ie. Costs came in well under budget(estimate). But with negative results, the bottom 4 contract items represent a large dollar loss of-34k which is 100% basically of the Gross Profit itself. If these had even just broken even we would have a GP double the current amount. So they obviously deserve as much comment.
Q1 In excel orsimilarly in say Crystal Reports what is a good way of further sorting these 42records either in a table or chart, whereby say the top 80% contract items that had a major impact on the gross profit are shown and the rest hidden. Ie. All thelittle sub 1000 or sub -1000 amounts.
Q2 How wouldother people here write a commentary on this data set? Can you script something to illustrate?
I have 2 different formulas that I need changed in a similar way.
The first formula is for cell AV11: =SUM(BI11,BP11,BW11,CD11,CK11,CR11,CY11,DF11,DM11,DT11,EA11)+10
Every cell starts off blank.
What I need is for cell AV11 to always start off blank until data is entered into one of the other cells. The problem is that since the sum always needs to be +10 only when data is entered in the other cells, I don't know how to keep 10 from showing in cell AV11 when no data is typed in the other cells.
The other formula is for cell CO39: =(CU8)+3
I pretty much need the same thing. If no data is entered in cell CU8, then I do not want cell CO39 to show the 3.
If there is data in the cell then it is to be left. If there is no data in the cell then I would like to show the value 0.
I have tried using a circular reference, using the formula =IF(ISBLANK(B1),0,B1) and other similar formulas but they dont work as the formula overwrites the data in it.
