Sum Last 5 Entries From Range
I have a row of golf scores "I3:X3" they either have a number value or "DNP".
I want to SUM only the last five number values and exclude the rest. The list will get a new entry every week. ie I want to SUM the last 5 most recent scores. There may be 15 or 16 scores or only 3, but I want the last 5 (or less) most recent.
View Complete Thread with Replies
Related Forum Messages:
VBA Counting Used Entries In A Range
i need to count the number of used entries in a range.
so fex the range is A1:A10 but there are only 2 entries what i search is the number 2
is there a VBA function or so doing that.
ps i have this range.Rows.Count but this is counting me the length of the range even if there are no entries..
Prevent Duplicate Entries In Range
Dim myCount As Byte
Dim myColumn As String
Dim myRow As Long
Sub Worksheet_Change(ByVal Target As Range)
myCount = 0
If Range("B" & myRow).Value <> "" Then myCount = myCount + 1
If Range("C" & myRow).Value <> "" Then myCount = myCount + 1
If Range("D" & myRow).Value <> "" Then myCount = myCount + 1
If Range("E" & myRow).Value <> "" Then myCount = myCount + 1
If myCount > 1 Then....................
The above code pops up the message box when a second entry is made within the specified range, but OK button does not function, contents are not cleared, and Excel locks up.
Delete Entries In A Range That Equal 0.0000%
I have a very big range of data from B4, to a variable other end from which I would like to delete all entries equal to 0.0000 leaving just those with an entered value.
I guess it's just an if question cycling through the rows and columns? Slight complication is it's on the 3rd sheet of a Workbook, as set out in the sample file.
After this manipulation has been done, I then wish to copy the data from the range B4: end of data into the same cells in the output sheet.
Move Or Remove Negative Entries In A Range
My check register was exported to a excel spread sheet where the deposits and debits are all in one column E. The debits are entered as a negative number. I want to create two separate debit and deposit columns with the appropriate data entered. What the best way of moving the data accordingly?
Loop For Removing Double Entries In A Range
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)
Dim coll As New Collection
Dim lcount As Long
Dim cell As Range
On Error Resume Next
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.
Count Unique Entries Within Variable Date Range
Using the DCOUNT function is generally a straight forward proposition but I'm not getting the expected results and would like for someone to take a look and help me understand why.
Goal: create a count of unique entries within a defined variable date range
I have a data table with duplicate values and need to count unique entries, the result of which will be used in a calculation. Due to a requirement to track the counts in a rolling 30-day period, the flexibility of daily selecting the date ranges is a necessity, which is why I chose to use DCOUNT and feed dates into the criteria cells.
I've been attempting to use the DCOUNT function but I'm not getting the correct result.
Oddly, after duplicating the table and formula on the "Count Repeated Items Once" page, even those results are incorrect.
It seems, too, that COUNTIF does not like (accept) dynamic named ranges. Hard coding the range into the formula yields a result of TRUE, but using a dynamic named range gives FALSE. Anyone else experience this and is there a work around (that is, if I have not erred in its use)?
Count Unique Entries Within Variable Date Range ..
I've been struggling for hours on what should be a simple formula. I have 6 columns containing various dates. On each row I want to count of the 6 columns how many dates were unique and after 3/15/09. I've been using the following formula however it still counts a cell even if it's prior to 3/15/09. =SUM(IF(FREQUENCY(A1:F1,A1:F1)>3/15/2009,1,0)). I've attached a sample file for reference.
Validate Cell Entries To Named Range List
I have been trying to figure out how to use a named range (on another sheet) to validate the user entered data in a specific column. All my attempts at utilizing worksheet_change event have ended in errors. I've created a simple workbook that shows what I'm trying to do and have attached it to this message. The 'Sample (Data)' worksheet has the data table that would be completed by the user. On the 'Validation' worksheet I have named the range to be used to validate the data as 'rngVal'. So when something is entered under the 'Expense Type' heading, the code would verify that the value entered was contained in the 'myVal' validation list. If it was not present in the list, a msgbox would instruct you to try again. After stealing bits of knowledge and code from many of the posts on this site I cobbled together the following (which results in an error at the 'set rngFind' line):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngFind As Range
'Set Target = Cells(ActiveCell.Row, ActiveCell.Column)
Set rngFind = Range("rngVal"). Find(Target.Value)
'If data in column C changes, do the following
If Target.Column = 3 Then
'If the value is found on the validation list, do nothing.
If rngFind Is Nothing Then
MsgBox "You must enter one of the following in this cell:"
.EnableEvents = False
.EnableEvents = True
Sum Only Same Entries
I would like help with a worksheet.
I have a column of Data which is a "material description".
In an adjacent column I have the square meterage of the material.
I would like to total all the square meterage of each material and have it shown in another column.
How can I do this?
I have searched far and wide for an example however I am probably using the wrong terms in my search.
Attached is a sample workbook.
Remove Entries In Col A That Appear In Col C
Hello all, I was wondering if anyone would be able to help with a macro or even formula that would remove the entries in Col A that appear in Col C. Attached is an example with dummy data, Col D is how the list should be. If a number appears in A but not C then all instances of it should be kept, conversely if it is in C but not A then it does not need to be kept. Most importantly I need to be able to delete those that appear in both by the same number of times they appear in C.
Hope this all makes sense. Please feel free to ask if it is not clear.
Averages For Last 12 Entries
I need to calculate average for last 12 valid entries in a column. I say valid because if there are blanks or zeros in the cell they need to be discounted.
The solution needs to work on percentages as well.
To explain further
Col A Col B
Jan 2007 1
Feb 2007 5
Mar 2007 5
Apr 2007 0
Jun 2007 20
Jul 2007 0
Sep 2007 10
Oct 2007 0
Nov 2007 10
Dec 2007 18
Jan 2008 8
Feb 2008 6
Mar 2008 0
May 2008 10
Jun 2008 1
The requirement would be to calculate average from Jun 2008 backwards for 12 months where there aren't zero or blank entries.
How Many Different Entries For A Certain Criteria
I have been searching a bit in a lot of the already posted threads but without any luck on my problem.
Normaly in excel you always want to count how many entries for a certain criteria.
Now I just want to know how many different entries for a certain criteria.
in column A1:A10 I have the value "S" -> group
in column B1:B4 I have the value "40" -> material number
in column B5:B8 I have the value "60" -> material number
in column B9:B10 I have the value "70" -> material number
So for the group "S" it is listet with total 3 different "material number" (40,60,70)
So my result should be = 3
how should the formula be on counting for the group "S" how many different values are in a defined area (e.g. B1:B10)
I have tried with sumproduct,
Return Last & 2nd Last Entries
I am trying to construct formulas that dynamically extract correct values. I need to construct two formula one in Column I and Column J. Column L and M have solutions that I should have. The formulas need to look for data in columns and extract them. For example, if an entry in E3 then for This week it will extract it and for last week it would extract entry in D3.
Vba Sum Up Textbox Entries
I try to sum up values a user enters into 3 different textboxes using the following
cells(1,1) = userform.textbox1.value + userform.textbox2.value + userform.textbox3.value
Let's imagine the user enters "10" into each of the three textboxes, cell a1 should contain 30, however, what i get is 101010.
Flagging Same Entries
If I have a multiple entry of a product with the same end date against a customer number how could I flag this as something I need to look at?
Cust number Product Number Expired Date
So in this case the first two would be flag with say “yes” and the others “no” as the customer number, product number and expire date don’t match.
Sum Unique Entries
I have attached a sample workbook to try and explain what I'm after.
Basically I want to sum the amount of occasions a person has had checks and each occasion could have 1,2 or 3 individual checks.
Deleting Old Entries
The following code works fine if the data is grouped (not necessarily sorted) by column B (but not necessarily by column A), this code will delete the duplicates rows, but retaining the latest entry (by column A) of each name in column B.
Dim RowNdx As Long
For RowNdx = Range("B2").End(xlDown).Row To 2 Step -1
If Cells(RowNdx, "B").Value = Cells(RowNdx - 1, "B").Value Then
If Cells(RowNdx, "A").Value
I'm trying to evaluate the last 8 entries of a particular column of data that I regularly update (K4:K5000). This data is filled with numbers ranging from about -50 to 50 and will occassionally have a blank entry.
I'd like to evaluate these last 8 entries using a couple of different conditions.
#1 - In column D4:D5000, I have three entry values - X, Y, or Z. It will never be blank. I would like for the Excel to automatically review the last 8 entries of columns K and D and do two things...
a. provide an output of a count of how many X, Y and Z there are for the last 8 entries (format: 4-3-1)
b. provide an output of an average of the values of K for each of X, Y, and Z... (i.e., avg of X = 4.4; avg of Y = 2.1; avg of Z = 10.7)
#2 - Next, I would like to add a third column to the mix (Column C4:C5000 with entries >0 and 0, Y>0, Z>0 there are for the last 8 entries (format: 4-3-1)
b. provide an output of an average of the values of K for each of X>0, Y>0, and Z>0 (i.e., avg of X>0 = 9.4, etc.)
I want to put a formula in a sheet.
Suppose In issue column there are few nos but until those nos will not be posted in Return column, those will not be posted again in another day's issue column, similarly until those will not be returned, those nos will not be posted in issue column. But for the first two days it will not work ad those are first time entries.
But this will be continued working on the next sheet for the net month.
28/11/09 29/11/09 30/11/09 Issue Nos. Return Nos. Issue Nos. Return Nos. Issue Nos. Return Nos. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
Display Last 8 Entries Only
I have data on sheet A columns (A:DR) over 400 rows, adding more all the time.
On sheet 2 i would like to display only the last 8 entries (rows) from sheet 1 columns A,B,DO,DQ
So i will end up with 8 rows of data on sheet 2, and 4 columns.
Formula To Add The Entries
If i have the following chart, what formula do i need to add all the entries EXCEPT and entry OS. i have the formula to add the entries but i need to exclude any "OS" entries. Like the following, but i want it to total 6. (minus the 2 OC entries)
I m writing the numbers 1 to 100 in rows of 10. I now need to change the code to offset this, starting at any cell i choose.
if i was to type e.g D6 into cell A1, the numbers would start at D6.
I have been trying a number of different functions!
I have the following countif function that is searching a worksheet (Cases Closed) for the name John in Column O and excluding Solutions in column x. The problem I have is there are duplicates cases in Column C that are being counted two and three times.
Is there anyway to have the following function exclude duplicates records in Column C? Just count unique records in Column C?
=(COUNTIF('Cases Closed'!O:O,"John"))-(COUNTIFS('Cases Closed'!O:O, "John", 'Cases Closed'!X:X, "*Solution*"))
Restrict Entries To 1 Per Row
I am looking for a VBA event(?) code that would erase another cell if I enter a value in another cell, because the criteria is that only one of the cells are to be allowed a value per row.
ex: cells A1, B1, C1
if I enter 1 in cell A1 and then enter 1 in cell C1, I want cell A1 to erase and cell C1 would have the value 1. I am looking to create these sets for 200 rows.
Time Between Cell Entries
How would I go about displaying in a cell the time from the last entry to the current but show 0 time if the range is empty?
E5 to R6 currently has space for numbers to be entered.
E3 has the command =NOW()
I would like E7 to show "xxx minutes have passed since last entry" where xxx would show 0 while the range is empty.
Return Top 10 Entries For Each Category
I have a product database that contains in column A the product name (eg "Granny Smith"), in column B the category (eg "Apple") and in column C the number of sales (eg 10,123).
I was looking to use code to list the top 10 products for each category, so to show the top 10 names I would enter in something like...
Highlighting For Duplicate Entries
I would like to check column B and if any of these values match values in column J hightlight the cell on the same row as the match on column E green. I don't know much about excel but this would be a tremendous help because i'm looking through quite a bit of data. I've tried using conditional formatting and i can't seem to get that to work for my needs. Let me know if you need anymore information.
Copy Over Todays Entries
First id like to start by explaining that the original source of the code here was given to me by JBeaucaire, from another thread(thanks again). I adpated the last script he wrote to work on this new spreadsheet im trying to automate a little but I need some additional help. Right now as is, I plan to run the report on a weekly basis and having the reps save a new file for the end of each week. Ideally I would have an "update" macro where it would check the first sheet called ALL for any new new entries.
Right now in the dummy sheet the group numbers are all the same and the Names are all the same. In a real world scenario they would almost all be diffrent. I say almost because in some cases a Customer can be Termed, reinstated and Renewed all in the same month. So on the main work sheet where the data entry occurs could have duplicate group numbers. Group names often has dupes because of the way group #'s are attached. So what im looking for is some instruction on how I can have this update just the new days data. Heres the code I have so far for break out the data. Attached is the dummy sheet.
Count Number Of Different Entries
I have a list of ID's, many of which appear several times. Is there a formula that will give me the number of different ID's? That is:
the formula would give the answer "3" for 3 different numbers in the 4 total numbers.
Reference Last X Entries In Formula
I input sales for each day of the week on a separate sheet since the beginning of the year. I am taking the average of the last 12 (Tuesdays) but with each input I need to change the function. Say it was Average(B11:B22) with the new entry in B23 I would change Average(B12:B23) I would like it to do it automatically with each entry.
Find New And Existing Entries
I have a list of new payments received (sheet 2) along with their agreement numbers which I want to match to the list I had last month (sheet 1) to see which agreements are still paying, which have stopped and if there are any new ones.
The problem is that the the list of new payments received shows the agreement numbers with random prefixes and suffixes so my lookup returns #N/A
Sheet 1 - 24020130
Sheet 2 = 24020130FLE
My basic thinking (and I mean basic) is to do an edit/replace on sheet 2 entries and replace the FLE's with with nothing thus removing them completely then running the lookup but that doesn't tell me which are new payments received.
Then I could make sheet 1 text blue, sheet 2 text red, combine them, re-sort and manually go through the list but I'm hoping there's a sexy formula just waiting to be unleashed.
Delete Unique Entries
I was wondering if there might be a better way to write this macro. What it does is clears unique items from a Range( leaves duplicates ) I've looked all over the net I can find all kinds of function and subs to remove duplicates but haven't been able to find anything that just removes single entries. I"ll bet there's a more elegant way to write this maybe using a Collection or a Dictionary.
Dim MP1_Rnge As Range
Set MP1_Rnge = Range("A1:A100")
For Each Cell In MP1_Rnge
If Not IsEmpty(Cell) Then
If Cell.Row = 1 Then..........
Make Entries Into Time Log
I am creating a log to keep track of my start and stop times when I work on a project. I have two columns labelled START and END and I can use a macro button to make the current date and time appear in the cell under these headings. What I haven't been able to find is a way of getting the macro to move to a new blank cell ready to accept the next entry. For instance, move one cell to the right or move one cell down and one to the left.
Can anyone tell me how to do this? (recording it just names the cell so it always goes back to that named cell.)
Using Mutiple Entries In A Calculation
I'm trying to create a workbook that will calculate times. Currently I have a worksheet that has, for example Incident #1, Date/Time In, and Date/Time Out. At the end of the row it will calculate total time in hours.
I need to create a second worksheet that will have Incident # to correspond to the incident # on Sheet1, with Date/Time In and Out. However, on this sheet there could be more than one occurance of Incident#1, and multiple times in an out. That total will be deducted from the total of incident#1 on Sheet1.
The easiest way to explain this would be...say for the sake of argument Sheet1 calculates the duration of a vacation in hours (lets say 168 hours). Sheet2 will calculate how much of that time was spent doing business (lets say 2 hours on day1, 3 hours on day 4). In the end I want to subtract 5 hours from 168 hours giving me 163 actual vacation hours.
My problem is, I don't know how to go about using the values in multiple rows in Sheet2 that correspond to the row in Sheet1.
Eliminate Duplicate Entries WITHIN A Row
I have a table of data like this:
A , 1 , 1
B , 2 , 1
C , 3 , 4
D , 1 , 3
E , 3 , 3
I need to eliminate duplicate entries WITHIN a row. So, I need the table to become:
A , 1
B , 2 , 1
C , 3 , 4
D , 1 , 3
E , 3
Is there an easy way to do this for a large sample? Also, there are actually more like 8 columns of this data, if thats important.
Verifying Text Entries
I was given two files from an old DOS pos system to make a current mailing list with.
The first file is a list of all the customers from this company(full list), the 2nd is all the customers active within the previous year(active list). I don't have any access to any information other than what is on the 2 lists I was given and I need to use the active list to make sure that only the current customers get sent anything. Not normally a big issue, if it weren't how these lists were formatted when given to me. The full list is a mailing list that was formatted to run labels from a dot matrix printer and imports to excel in the following manner,
John & Jane Doe
1234 NE Road St
Someplace, OR 97056
The active list is formatted like:
Doe | John & Jane
Because there is no address information on the active list I can't just generate a full mailing list. I am able to seperate the values from the full list by referencing cells onto another worksheet (it seperates as 'name' 'address' 'city/state/zip') but I need to find a true/false formula, or something similar, that will tell me which names on the full list are also on the active list. I can do it the other way around, and check to see which names from the active list are on the full list (all of them) but because of how the values are seperated on each worksheet I can't seem to figure out how to make this work.
Count By Unique Entries
I am hoping this can be done with formulae. Starting at C7 and continuing down the C column there is a list of names which could potentially run from C7 to C5000. This list of names will contain duplicates. For each name there is a corresponding 'reason' in the F column which will contain the word 'Truancy' or 'Late'.
I need a formula that can count the number of UNIQUE names in the C column which correspond to the word 'Truancy' or 'Late' in the E column.
John Potts.................Truancy 2
Matt Jones................Truancy 10
John Potts.................Truancy 4
Matt Jones ...............Late AM
Pete Burns................Late PM
Pete Burns ...............Late Both
Steve Lopez..............Truancy 6
Count of unique names with the word Truancy in the corresponding E column = 3 [John Potts has 2 instances of the word truancy in column E but this is only counted once]
Count of unique name with the Word Late in the corresponding E column = 2 [Pete Burns has 2 instances of the word late in the E column but this is only counted once].
I have also included a sample workbook.
Find Ten Latest Entries
I have a sheet (attached, Excel 2003) that I need an assist on. What I want to do is search the 'All Games' worksheet for the ten latest games a specific player has played.
That by its self might not be so hard, but I want to take those 10 rows (once identified) and take the sum of column G and divide by the sum of column F for those rows only.
I already have a concatenate cell in the 'All Games' sheet for each row that I use to as a link of sorts to get data from column F and G onto the 'Spring 2009' sheet. The way I have the concatenate set up, I can manually sort by it and get the 'latest 10' values I need, but I need to get it all into a formula to place on the 'Spring 2009' sheet without the manual sort.
Entries Are Invisible To Filter
I'm using Excel 2003 for PC to keep up with the hours I've worked on different jobs. So I have a column with jobnumbers and a second column with the name of the client to wich these jobnumbers are assigned. When I try to filter on a specific client or I want to filter on of the jobnumbers of that client, I can't choose them in my dropdown menu on top of my column.
Preventing Duplicate Entries
I'm working on a employee evaluation form. I'm using a Pass/Fail system.
I have a cell designated for PASS & one for FAIL. In these cells, I would put
"YES" if they passed, "YES" if they failed that section.
How can I prevent someone from mistakingly entering Yes in both cells?
Identifying Unique Entries
I have a worksheet, where in C3:C100 (or even higher) i will have text strings. A lot of these will be repeated, and i want to pick out just one instance of each text string and display it in E3:E10 (or higher).
So, for example, if i had the following values in column C:One
I would want the following values in column E:One