Removing Specific Date / Row Entries?

Sep 18, 2013

I have this data set which has customers D.O.B's. This a test data set for the MGM Grand Casino and some customers are under aged below 21 or not even born yet (basically wrong inputs). So ultimately I want to retain the row entries of the customers who were born between (1930 - 1992).

mgm_cleaned TEST  ABCDEFGHIJKLM43928-Sep-20048-Sep-200405.4722000004-Oct-194944969-Sep-20049-Sep-200408.5720.25000004-Oct-1949459320-Apr-200423-Apr-2004010.9255000004-Oct-1949469420-Apr-200420-Apr-200409.2941000004-Oct-1949479121-Apr-200421-Apr-2004019.637.25000004-Oct-1949489221-Apr-200421-Apr-2004016.2941000004-Oct-1949499323-Apr-200323-Apr-2003010.96-10.25000004-Oct-1949509623

[Code] .........

View 2 Replies


ADVERTISEMENT

Google Spreadsheets - Counting Entries On Specific Date

Jan 13, 2014

How to perform this function on Google Spreadsheets (which appears to be slightly different to Excel);

Calculate the sum of the numbers in column D that occur on the 10th January 2014?

And also, Calculate the sum of the numbers in column D that occur between 9am and 10am on the 10th January 2014.

View 1 Replies View Related

Removing Duplicate Entries From Two Lists?

Aug 15, 2008

Let's say I have two mailing lists, A and B.

A:

bob@bob.com
jeff@jeff.com
tom@tom.com
cindy@cindy.com
jose@jose.com

B:

fred@fred.com
jeff@jeff.com
angus@angus.com
cindy@cindy.com
chuck@chuck.com

Now I want to create list C, a modified version of list B, which is made up of everybody in list B, EXCEPT for those people who are also present in list A (in this case, Jeff and Cindy).

So basically list C should look like this:

C:

fred@fred.com
angus@angus.com
chuck@chuck.com


What would be the fastest and most efficient way to create list C?

View 7 Replies View Related

Removing Blank Entries That Show As Zero

Aug 2, 2006

I have a workbook containing several worksheets. I use one worksheet to collate information from the others. I do this by referencing the relevant cells I need from the other worksheets with the '=' command.

When this displays it shows as a '0' if the original cell is blank. Is it possible for this to show as a blank unless there is any data. I have tried the ISERROR function but it still leaves the entry as a '0'.

View 4 Replies View Related

Loop For Removing Double Entries In A Range

Apr 24, 2007

I´working on an excel sheet where i´m copying a range to another place. So far so good. Then I want to remove all double entries for each rows in the new range. I managed to get a code working for one row. When I want to loop it for all the rows in the copied range, I get a an error popoing up when the loop starts working on the second row. Error 457: "This is already associated with an element of this collection" The line creating this error is coll.Add cell.Value, CStr(cell.Value)

Sub Sortere_StederBeta()
Dim coll As New Collection
Dim lcount As Long
Dim cell As Range
On Error Resume Next
Range("B4:U33").Copy
Range("B36").Select
Selection.PasteSpecial Paste:=xlPasteValues
For i = 36 To 65 Step 1
Range(Cells(i, 2), Cells(i, 22)).Select
Set coll = Nothing........................

The problem seems to come from the fact that the Coll (New Collection) is not reseting for the next loop. I tried to set the Coll to Nothing but doesn´t have any effect.

View 5 Replies View Related

Removing Duplicate Entries From List Of Names And Addresses

Jun 12, 2006

I'm trying to remove duplicate entries from a list of names & addresses.

What I'm after is some VBA code which will examine row by row the 'post code' field and the 'Full name' field and highlight/delete duplicate entries.

Its best illustrated by an example

Full Name Address Postcode
Mr C. Verougstraete 6 somone st, Leicester HY8 9YK
Mrs R. Brazier 8 high st HY9 9LK
Mr C. Verougstraete 6 somone st, HY8 9YK

The last record is obviously a duplicate of row 2 therefore will be removed.

View 3 Replies View Related

Removing Specific Duplicate Values?

Apr 2, 2013

I have a data set that I add information to weekly. I then add a value at the end of the row, example active or inactive. I've found that, when using the remove duplicate function, it does not always remove the second instance, resulting in a loss of that added data(active or inactive). So I guess my question is, can I somehow specify which instance of duplicated data is removed?

View 6 Replies View Related

Removing A Specific Sheet From A Loop

Feb 9, 2007

I have got an Excel program that retrieves colunms of data from a Database using MS Query on the first sheet of the workbook. I then have some code that abstracts the data from from the first sheet into each seperate sheet depending on the number value in the Family Groups column. This is all done using a loop.

This all worked fine until I tried to insert another sheet which contains a couple of columns of static values which I dont wish to be affected by the VBA loop. when I now run the code it overwrites the contents of this sheet.

May question is how can I put some code into my loop to prevent it from updating this sheet in the work book?

View 9 Replies View Related

Removing Specific Info From Cell

Nov 18, 2008

I have 40,000 part numbers that range in style.

For example:
13538-AL
BC-DIB-13
13750U
13762-1

I want to take all the numbers that have the ending tag "-XX" that are LETTERED but NOT the numbered combo (just like example 13538-AL) and remove the tag to just have the root 5-digit number.

I have all the end tag combos I would like removed. SO if I needed -AL, -LG, -CG, -SS, etc. removed, I would need the formula that would find all the 5-digit numbers with those end tags and remove those tags, leaving the other numbers alone.

View 9 Replies View Related

Removing Date And Seconds From Cells That Include Date And Time

Apr 30, 2014

I have only one column (A) with thousands of cell entries. Each Cell contains a date and time. (x/xx/xxxx 12:34:56)

For example, Cell A1 = 1/24/13 18:45:22

I want the new cells to only have the time, but have the seconds removed (and not simply hide the seconds). This is because I'll need to run Conditional Formatting to find every cell with a similar time - but I only need similar times within the same minute...it doesn't matter if the seconds are off.

The new cell should just be A1 = 18:45

The real reason I need this is because I need to find every entry that has the same time (with hour and minutes), which I figure I'd use conditional formatting for. My problem is that many entries have the same time but the seconds are off.

Example:
1/22/13 12:23:11
1/24/13 18:45:22
1/24/13 18:45:36
1/26/13 22:33:41

In this example, I'd want Conditional Formatting to highlight cell A2 and A3 because they have the same time of 18:45 (not including the seconds). I figured the best way to go about this would be to remove the date and remove the seconds, and run the search that way. However, any way to accomplish goal without deleting the date and seconds would be just as useful.

View 4 Replies View Related

How To Get Average Time For Four Specific Entries

Apr 7, 2013

I have a database of over 10,000 entries. I am trying to get the average time for four specific entries (department, test 1, test 2, test 3). I did an averageifs for the tests individually and calculated the average time individually.

The three tests comprise of the department.

Average of test 1 = 40.8
Average of test 2 = 39.9
Average of test 3 = 94.8

Average of dept using the averageifs is 41.3
Average of dept by taking the average of the values above is 58.8

Why is that discrepancy there?

View 6 Replies View Related

Adding A Column With Variables For Specific Entries?

Jan 31, 2013

I have a table which looks like this:

Name 1 IDNumber Name 2 Name 3 Column 5

Tom20148 John Malmo
Tom20148 Will Malmo
Bob20206 Will Malmo
Tom20206 Will Paris
Bob20206 Rob Rotterdam
Bob20207 John Rotterdam
Ray20207 John Paris
Tom20208 John Malmo
Ray20208 Rob London
Ray20209 Rob Paris
Bob20209 Will Malmo

Is it possible to have excel go through this list and assign each row a number in column 5 based on the names and the IDNumber? Basically, I would want each entry that is identical in name 1, 2 and 3 to be assigned numbers 1, 2, 3, 4, 5 etc based on their IDNumber. So Tom/John/Malmo with the IDNumber 20148 would get the number 1 in column 5, while the next match (Tom/John/Malmo/20208) would get the number 2 in column 5. For each different match of Name 1,2 and 3, I would want the count in column 5 to start at 1. So Bob/Will/Malmo/ 20206 would get number 1, Bob/Will/Malmo/20209 number 2 etc.

View 4 Replies View Related

Highlighting Duplicates Entries Across Specific Sheets

Jan 20, 2012

I have 13 sheets of data in my open workbook. Column D, which includes blank cells at times, across each of these sheets maps the same info. of orders. However, I know there are several duplicates, and I want to clean them out. I am trying to figure out a way to highlight all the duplicates that appear only in column D beginning with sheet 4 and ending with sheet 13. I will leave the duplicates that appear on Sheets 5-13 alone, but I want to have them highlighted, so I can review the ones that appear on sheet 4 first, and then delete them.

View 1 Replies View Related

Find Specific Withdraw Entries From Bank Statements

Jan 6, 2010

I use online banking for paying my monthly bills. I get different confirmartion for each pay transection. Pls look attached Excel file. Now I want to find specific bills expense with amount from all my 12 months bank statements. How can I get this task done.

Note: Each time get different confirmation which appears on my bank statement beside paid bill name. for example January 2009 statement GAS Z8A.... Feb 2009 GAS S2W. I hope this clarify. What I have to do if I want to get all my 12 months GAS money withdraw from bank statement. I use csv file for bank statement.

View 11 Replies View Related

Counting Date Entries Between A Date Range

May 7, 2009

I am trying to insert a formula that counts the number of dates within a certain date range. why this sample file does not work, the answer I am looking for in cell C1 is 3.

View 2 Replies View Related

Removing Time (12:33) From Rubric That Contains Date

Oct 17, 2013

I have a column which contains dates and time.

Like this:

8/1/2013 10:34

I would like to delete the time, and leave the date.

like this:

8/1/2013

Can I do this?

View 3 Replies View Related

SUM By Both A Specific Month, And A Specific Year From A Single Date Field

Nov 30, 2007

So, this works perfectly by itself: {= SUM(IF(MONTH(I4:I17)=1,G4:G17,0))}
And this works perfectly by itself: {=SUM(IF(YEAR(I4:I17)=2007,G4:G17,0))}

But this doesn't work at all: {=SUM(IF(AND(MONTH(I4:I17)=1,YEAR(I4:I17)=2007),G4:G17,0))}

SUM by both a specific month, and a specific year from a single date field?

View 3 Replies View Related

Removing The 00/01/1900 Date When Looking Up Another Cell In Another Worksheet

Apr 12, 2009

I am trying to reference a cell on another worksheet, the cell is formatted to be a date. when i put in the following formula =Sheet2!A1 and i don't input a date into A1 on sheet 2 the cell containing the formula shows the date 00/01/1900, is there any way of stopping this showing, ideally the cell would be blank until I inputted a date into A1 on sheet 2

View 9 Replies View Related

Removing Seconds In Date / Time String?

Sep 26, 2013

I need a macro, if possible, to remove the seconds in a date/time string.

I can format the cells to remove the seconds, but the actual value still contains the seconds. For example:

The cell shows 9/24/2013 19:21 but the formula bar shows 9/24/2013 7:21:17 PM.

I need to remove the seconds so I can us the time in an averageifs formula as a criteria.

I would like a macro because I want to do other manipulations of the spreadsheet with the idea of importing the data at some point into a database.

If I can automate it, or at least decrease tha amount of time I use to get the data in the correct format.

View 4 Replies View Related

Removing Duplicates From List Based On Latest Date?

Jan 6, 2014

I am working on a large data file (leasing file), that has many duplicates. The names on the file are duplicated due to the various variable costs associated with leasing. I need to remove the duplicates names based on the latest contract end date.

View 4 Replies View Related

Removing Specific Data From Series Data In A Chart

Nov 1, 2011

I have a combination chart with data from Sun thru Sat (7 days). My manager wants to display only the weekday data (M,T,W,Th,F) and remove the weekend data (Sat,Sun). However, I also need this data to be included in other charts I need. I was thinking in years past, that I was able to remove specific data from the chart directly without having to alter or change the spreadsheet data? I have copied a second set of data and hid the weekend data, however, I have gaps in the data, which I need to remove in the chart display. Not sure what to do, or the best avenue?

View 2 Replies View Related

Find Missing Entries For Given Date?

Mar 12, 2013

How I can go about finding missing entries. I have an excel sheet that has column A as employee name and column E with a date. There are 10 employees so there should be 10 entries for each date. I need to be able to determine which employee hasnt entered information for a given date.

View 1 Replies View Related

Highlight Duplicate Entries In Same Date?

Apr 24, 2014

highlighting the duplicate entries in the same date comparing C:C data.

View 3 Replies View Related

Counting Entries In Date Range?

Feb 12, 2014

I'm looking for a way to count entries of numbers per day of the week within a date range. Example

Start End Mo Tu We Th Fr Sa Su TOTAL
2/10 2/23 1 1 1 1 0 1 1 12
3/1 3/31 1 2 1 1 1 1 2 40

I don't know if that possible in excel or its to much

View 6 Replies View Related

Show Date/time Between Entries

Oct 11, 2008

Basically each line signifies a date & time that a deposit was placed and a date & time that a withdrawal was actioned. My goal is to be able to determine what kind of average response time we have between the time a withdrawal was requested and the time a withdrawal was actioned.

View 2 Replies View Related

Applying Mask For Date Entries

Jan 24, 2007

I'd like to enter dates without having to use the forward slash symbol between the day, month and year values. I've read a post by Dave Hawley explaing how to do it, but I can't get it to work for me. There must be some step that I am not aware of.

View 9 Replies View Related

Filtering Out Entries With Null Date Field

Nov 21, 2006

I have a column formatted as a date. I would like to filter out all rows which have a blank date value. I don't see any blank value to select when I try to select auto-filter for that row. Any suggestions?

I would like to filter out the blank values because I found that if I pivot on the data and there are blank values in the field, I can not use the group option for that field.

View 9 Replies View Related

Conditional Date & Time Stamp Entries

Oct 23, 2007

I have a column (D) where I put percentages complete of a task. When that reaches 100% I want to put the date in column (C).

I found many samples online but nothing I can modify to do this.. This one is pretty straight forward but I'm not sure how to have it look for the value and not just empty or not. I would be greatful for any help you can offer.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If IsEmpty(Target(1)) Then Exit Sub
If IsEmpty(Target.Offset(0, -1)) Then
Target.Offset(0, -1) = Date
Target.offset(0, -1).numberformat = "yyyy-mm-dd"
End If
End Sub

View 6 Replies View Related

Create Calculator To Show Start And End Date Based On Specific Date Provided

Jan 12, 2014

I am trying to create a calculator which will show start date and end date based on the specific date provided.

EX: column A has January 1, 2013, column B has Wednesday, Column C should have a start date which supposed to be 4 days ago (December 28).

View 6 Replies View Related

Pivot Table - Stop It Summing Date Entries

Dec 5, 2013

How do I stop a pivot table adding data together?

e.g.

1st Nov -100
1st Nov - 100
2nd Nov - 200
2nd Nov -200

I want this but instead get this:

1st Nov - 200
2nd Nov - 400

I need the pivot to split it out,

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved