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.
View Complete Thread with Replies
Related Forum Messages:
Find The Latest Row
I've got a multi column sheet containing, amonst other headings, "Date", "Transaction" and then a rolling "Balance". I have multiple rows with the same date and what I'm simply trying to do is have a formula at the top of my spreadsheet that finds the very last row that has data entered so that I can then extract & display the balance as at that point.
Date / Transaction / Value / Balance
01-12-09 / Salary / +£1,000 / +£2,600
01-12-09 / Fuel / -£50 / +£2,550
01-12-09 / Gift / -£100 / +£2,450
03-12-09 / Food / -£30 / +£2,420
03-12-09 / Interest / +£2 / +£2,422
In this example I'd want to return row 5 and then do some kind of lookup to retrieve the value £2,422...
Formula To Find The Best/latest Signal
I have a series of rows 14000+ lines strong in an excel sheet.
The series contain caribou location reports from those caribou that have a
collar attached to them.
Along with other information, each row contains three important columns
related to this question. Collar Number, Date and LC (LC = Position Fix
quality or Signal Strength).
I need a formula that will find the best and latest signal strength for each
collar and for each day!. The formula should result in a 0 or 1. (no or yes).
There are about 30 different collars each reporting on certain days. The LC
values are 1 to 7 with 7 being the strongest.
Find Latest Date Listed Against Specific Name
I have a "activity log" sheet which records the date in column A, the activity in column C and the name of the customer that was contacted in column D. There may be multiple entries of a customer's name in column D.
In a separate sheet, I want to find the latest date from the "activity log" sheet that a each customer was contacted and the specific action on that date. In this sheet, the client name will be in column A, and I want to record the last contact date for the client in the same row in column J and the type of contact in column K.
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.
Find Sum Of Unique Entries
I am using excel 2007.
I am trying to find the sum of unique entries in a table such as below for each respective date.
The results should appear as
What formula would I use to calculate this unique count?
Find Duplicate Entries
Got a workbook with 4 worksheets and sometimes we need to cut and paste a row from one worksheet to another, now thats easy now as we are all human and sometimes it gets copied and we have duplicate logs.
Now each sheet has usually not got that many rows in it, but that spreadsheet is getting sent to the highest people in the company and any mistakes look poor.
What i'm looking for is a way that i can highligh any rows that a duplicated in each worksheet. I can do it in each worksheet using conditional formatting with something like = COUNTIF($A8:$A$20,A8)>1. but don't know how to check that is not on any other worksheet. On every worksheet only need to check Col A for the duplicate information
Find Every Occurrence Of Duplicated Entries
I've been working on my original problem and have gotten a little closer to the results I'm looking for but not quite there. Basically, I'm now running into a problem of only finding the 1st occurence in a range of cells when I want to find every occurrence and show the result. Also, the range of cells to look for in my argument has 2 criteria, the start date and the end date. Can anyone tell me if there's a way to search through a range of cells and return every instance of that cell even if it's duplicated?
I've also attached my project to better understand what I'm trying to accomplish which is the use of a Gantt Chart as an employee scheduler as opposed to a task (or project) scheduler.
Vlookup With Conditions To Find Multiple Entries
I have a table (table1) with material numbers which have a price . This value is time dependent i.e., a material 999 could have a price of $10 for 1/1/2008-1/15/2008 and $20 for 1/16/1008 - 1/31/2008.
A B C D
999 1/1/2008 1/15/2008 $10
999 1/15/2008 1/31/2008 $20
998 2/1/2008 - 2/25/2008 $15
I have another table (table2) in another sheet in the same workbook have a material and date.
A B C
My requirement to take the material value and date in table2 and match it with table1 and get the value of column D in table 1 to column C of table2.
I have tried using vlookup but it only works for the first match and doesn't check for other values
below is the function that i tried
Find And Remove Identical Entries In A List
I regularly have to add a few new lines to what is in fact a very simple data base I've had running for a long time in Excel. About 1200 lines now, one line per person. I add a dozen or so lines (i.e. people) at a time in a different colour.
When I re-sort the whole thing I run my eye down the list to spot partial double (i.e. duplicate) entries (the new ones in their own colour helps). Then I delete the double entries one by one. Pretty stoooopid, in'it?
How can I do this better, faster and more accurately with Excel to find just two duplicated data ( NAME and ID NUMBER) in a person's line (there are 15 columns altogether)? Or: Where and what can I search for (I've just spent nearly an hour trying to find an answer by myself, but don't really know what to search for)?
Find Specific Withdraw Entries From Bank Statements
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.
Find And Mark The Higher Value In Series Of Duplicate Entries
I have a approx. 70 excel sheets with thousands of entries. Within a single sheet there are some duplicate entries (based on an account number). I find the duplicate entries by using the excel 2007 conditional formating and then sort by color to only show the duplicate entries. There are often hundreds of accounts that are duplicates. Sometimes there are more than 2 identical account duplicates (could be 3,4 or more). In each entry there is a number representing the rating of said account. These numbers differ within the duplicates. Example............
I am trying to find a formula that will choose a "winner" and enter a "W" based on the highest rating within the duplicate entries and then ideally assign an "L" to the loser duplicate account(s). Sometimes there is no rating, other times the rating could be the same in which case any account (does not matter) can be assigned a "W" and the others an "L"
Getting The Latest Transaction
i want to create a function / macro for this scenario:
if ID123456 has the status open dated 01/05/07 and the user later decided to change the status of ID123456 to closed on 03/05/07. The graph should count the number of closed then lessen the count of open because the previously open ID is already closed.
Get The Latest Value Of Any Individual
I have a list of fixtures and results arranged by date for a soccer league and have a forumla that I drag down beside each game which creates a value for each side that was competing.
I need for the result of those calculations to become the starting point of the next calculation involving each team, and so on. Is this at all possible?
Would I need a macro to do this or can I get excel to recognise these values and enter them for me?
Import Latest 6 Files
I would like to create a macro that looks in a specific directory "h:data"
It finds the 6 most recently modified files in this directory (csv files)
Then imports these 6 files into seperate worksheets in the same workbook "sorter.xls"
Filter For Latest Date
I have a data field of about 2000 rows & 30 columns.
These contain records on accidents for 56 offices.
One of the columns contains the date an individual had the accident.
There can be a number of records for each office as there are a number of employees in each office who have had accidents over the last few years.
I need to be able to run a macro or set up filters or formulae to show the row containing the most recent accidentin each office, by office. I also need to display the total number of days since the last accident occurred at the end of each row.
The offices are listed in Column B & the date of the last accident is listed in Column V.
Position Of Latest Thread
I have data in above format (including Dup). Is there anyway I can get the postion of latest entry. say if it is xyz011, it should return 8. Match will start counting from first cell and go downward. How can do a inverse counting?
Jump To Latest Date In Column
I need a macro that can jump to the nearest current date in column B from anywhere on the current tab. all the dates are in 10/09/2009 format, and the actual date of today might not be in that column. This can be + or -1 one day search from today. After this macro run's the nearest current date will be the active selected cell in column B.
Marking Up Latest Occurence In 2003
I am looking at a register of documents that are constantly being revised. What I need to do is mark up which ones need to be reviewedDocument RevisionViewedapples1apples2yapples3apples4apples5pears1pears2ypears3pears4oranges1oranges2oranges3
If a revision is marked as Y then previous revisions don;t need to be looked at and can be marked with n/a anything older doesn't need to be reviewed except for the latest one. In the above table if apples revision 2 has been viewed so rev 1, 3, and 4 are no longer relevant and only rev 5 needs to be reviewed. Pears rev 2 has been reviewed so on rev 4 needs to be reviewed. No Orange documents have been reviewed so only rev 3 needs to be reviewed.
The above is just an example my table consists of 8000 entries.
Retrieve Latest Value From Mulitple Columns
I have a list of teams in 2 columns (columns A and B). These teams play matches against each other. The teams repeat themselves but vary in their order in which they play and whether they are home or away; i.e. appear in column A or B.
Each team has a points brought forward value in column C or D which correspond to the teams in column A and B respectively. They also then have a carried forward points value in column E or F produced after the points from the current game (not shown in the attached file).
I need to be able to find a formula which will retrieve the latest (last match) carried forward value for a team and enter it into the brought forward cell. This is made difficult because the team may last appear in columns A or B and there is no logic as to when they last played. It is easy enough to use lookup to find the value from the first row of an array but i cannot work out how to find the value in the last row.
Latest Value In A Series Of Cells (not A Column Or Row)
am looking to find the most recent value in a series of cells that I define. I have set up a worksheet for each month and I want to only pull out the latest total entered for a particular cell reference (cell B16 in each worksheet has the number I need).
I tried using : =LOOKUP(9.99999999E+307,Jul:Jun!B16) but that just gives me a #value error.
I had a search through the archives too and found [url] and [url](neither of which I really understood or could use, although I did have a go), anyone got any ideas? Am sure it's very easy!
Just to clarify, I have 12 worksheets, each with a different month. Each month I enter data into the relevant worksheet. I want a running total on a summary worksheet and need to pull out the latest numbers automatically (they don't need to SUM or anything).
Finding Earliest And Latest Dates
I have a workbook with two sheets.
Sheet1 contains details of all of the jobs I do.
Sheet2 is my customer list.
Customers appear only once in sheet2, but may appear mutiple times in sheet1.
In sheet1 I have customer name, lead date, job date et al.
In sheet2 I have customer name, first date, last date et al
I am trying to populate first and last date in sheet2 from the lead date and job date in sheet1.
Sheet1 has a range of 500 jobs
Return Corresponding Data To Latest Date
I am trying to use a function to search for a particluar value (e.g., ssn) in one column and return the "InDate" in another column. I thought this would be easy, but I'm having considerable trouble. When I do manage to get a value returned, it's the wrong value. I need to return the most recent "InDate" for the ssn that I search for. The button on the " Lookup" sheet is to sort the InDate column descending so that the Excel function will start with the most recent date when it searches. I've tried Lookup and and just about every other similar function with no luck. I originally wanted to search for a ssn and return the information for the last 5-10 most recent visits, but gave up on that long ago.
Latest Text File For Import
I'm trying to automate an Excel macro. I need a way to select the latest .txt file from the Import Data dialog box.
I currently have a "CHDIR" at the beginning of the macro that sets the active directory. When I make the "Import Data" pick I am in the proper place. Now if I can only sort the list of .TXT files by date, select the first file in the list and hit ok to continue I'd be off and running.
Show Latest Information For Employees
I have an excel file with 2 tabs. tab 1: Active and Tab2: tenure. Tabe 1 has the following column headers
Original Start Date
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...................
Showing Latest Transaction On Product List
I have a spreadsheet and it shows a raw data dump of all sales transactions per product. This has been transferred via ODBC and I need some sort of query to filter out the data.
Is there any way I can write a query to only bring in the latest transaction based on the sale date per product?
Product Code|Last Sales Date|Cost Price
Open Latest File By Date Modified
I have manged to search for the last updated file on a folder but i want to use the filename as the source for a copy and paste. it all seems to work bar the linking of the 2 processes the error message 'run time error 9 subscript out of range' is the file is already open should it just not use the same variable to copy the files?
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Dim wsTo As Worksheet, wsFrom As Worksheet
Dim myDir As String, fn As String, a(), n As Long, myFile As String
Dim myDate As Date, temp As Date
myDir = "C:BarkingEMCIN"
fn = Dir(myDir & "*.csv")
Do While fn <> ""
temp = CreateObject("Scripting.FileSystemObject").GetFile(myDir & "" & ................................................
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)