Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










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

Sponsored Links:

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...

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
Find Latest Delay Position Of Each Number Which Is In The Game
Find latest delay Position of each number which is in the game

View Replies!   View Related
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.


View Replies!   View Related
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

View Replies!   View Related
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.

DateClient1/01/2010ABN1/01/2010ABN2/01/2010BNP2/01/2010HSBC3/01/2010ABN4/01/2010BNP4/01/2010HSBC4/01/2010ABN5/01/2010BHP

The results should appear as

Count Unique1/01/201012/01/201023/01/201014/01/201035/01/20101

What formula would I use to calculate this unique count?

View Replies!   View Related
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.

View Replies!   View Related
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
999 1/10/2008
999 1/20/2008
998 2/15/2008

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

=if(and(vlookup(A2,Sheet2!A1:D4,2,false)<=Sheet1!B2,vlookup(Sheet1!A2,Sheet2!A2:D4,3,false)>=Sheet1! C2)),vlookup(Sheet1!A2,Sheet2!A2:D4,4,false),"error")

View Replies!   View Related
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)?

View Replies!   View Related
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.


View Replies!   View Related
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"

View Replies!   View Related
Top Ten List
I have a list of about 300 names with corresponding values - names in column A, values in column B for example. There are some values in the range. These will be changing on a daily basis. On a different tab, I want to list the top ten values in one cell and the corresponding name in a cell next to it. I am using the Max function but it is duplicating some of the names because the values are the same. Is there a better function that I should be using?

View Replies!   View Related
Ten Oldest Dates
I need a formula on my "Problem Areas" sheet that will search and then list
from my "Overall" sheet the ten oldest dates (column O), that have not been
closed (column B), and do not match the text "Repair Clin" (column S). Each
row of data begins with the purchase order# (column A).

On the "Problem Areas" sheet it will only list the PO# and Date Opened.

View Replies!   View Related
Top And Bottom Ten (10)
I'm trying to create a new report and looking to get the top and bottom ten from a list. My original idea was to use the LARGE and SMALL funcions. However, when I use those because some values may be the same, the corresponding lookup values would be incorrect.

If I have Vendor A,B,C

VendorA 100%
VendorB 100%
VendorC 98%

My report would read this:

VendorA 100%
VendorA 100%
VendorC 98%

I'm using INDEX with MATCH to bring back the vendor. I then figured, I could just link the cell to the top 10 cells from where I am pulling from, which wrks for top ten, but won't work for the bottom ten.

I'm pulling the the data from a sheet with a query so I won't know where the ending data is unlike where the starting data begins. I then thought that maybe I could use an option button to control the query and just change the sort order. But, I really don't want to use VBA on this report, which I think I would have to do for that idea to work.

View Replies!   View Related
Tallying Totals Of Ten
I have a column of wickets (M). M5 and M6 are the number of wickets for one match, M7 and M8 for the next, and so on. Two wicket entries for each match.

I needed a formula to count the amount of times the combined wicket total for a match is greater than 10. Initially there were only a few M values, so I used this:

IF(M5+M6>=10,1,0)+IF(M7+M8>=10,1,0)+... and so on. Now I want to expand it to have more M values and this formula would become huge.

View Replies!   View Related
Extrapolating A Top Ten
Every month I use a worksheet that has the following information:

Countries Amount
argentina $5
brazil 10
chile 13
china 54
denmark 8
Finland 23
France 15
India 23
Indonesia 4
Japan 3
Russia 68
south africe 39

The list is actually a lot longer but is there a formula to allow me to extrapilate the top ten amounts without using a macro to sort and filter?

View Replies!   View Related
Finding Ten Closest
I'm looking to try and find the ten closest markets to a certain market and having trouble trying to break it down. once finding the closest ten from the annual sales, either bigger or smaller I'll be linking it to another sheet to automatically update the other sheet....

View Replies!   View Related
Format Cell Value To Ten Characters
I have the following code which formats the cell values in column A to 10 characters on entry:

Sub FormatToTenCharacters()
Dim Cell As Range, LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For Each Cell In Range("A1:A" & LR)
If Len(Cell.Value) < 10 Then Cell.Value = "'" & Application.Rept("0", 10 - Len(Cell.Value)) & UCase(Cell.Value)
Next Cell
End Sub
For example:

Enter excel in A1 and it changes to '00000EXCEL
Enter 123456 in A2 and it changes to '0000123456
Enter abc456 in A3 and it changes to '0000ABC456

I want to format it as text so that it does not have the apostrophe (').

View Replies!   View Related
Summing Ten Largest Values
is there a simple way to sum the ten largest (or smallest) values? I just can't think of any formula to do the job, and a filter isn't an appropriate permanent solution.


View Replies!   View Related
Calculate A Top Ten List
Now that I have my Keno board established ,I would like to be able to have a list of the "top 10 numbers drawn that day" I have attached a demo sheet of what I would like to do.

View Replies!   View Related
Routine Taking Ten Minutes To Update

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Application.EnableEvents = False
If Intersect(Target, Range("E:E")) Is Nothing And _
Intersect(Target, Range("H:J")) Is Nothing Then
Exit Sub
End If

Is there a way to make this run a little slicker , as at the moment it is taking almost 10 mins to update RngDate is $L6:$FB1038

View Replies!   View Related
Balance Sheet With Ten Days Notice
I am creating a balance sheet for my company that seems pretty simple (date, transaction type, amount, and balance), except for one thing: 10 days before every bill is due (and before every purchase is made), expense amounts are supposed to show up in the balance sheet as a deduction from the current balance, so that the sheet projects what the balance will be in 10 days. Do I need a macro for that? If so, what would it look like? It needs to be blank if it's more than 10 days out, and then suddenly appear in the balance sheet once it's 10 days away from due date.

View Replies!   View Related
Input The Name And Have That Data Populate A Different Ten Cells
how to do some complex stuff in Excel but sometimes I don't know how to do some really easy stuff. After I have named a data set of say a certain ten cells in one column, is there a function or a way to input the name and have that data populate a different ten cells? For instance if I have a huge amount of data all coded and I want to take equal but different portions and run them through a template, how can I make it so I just have to enter the a code for any of the data I have coded and have it populate momentarily where ever I need it to go? I'm sure this is very easy to do unless I have explained it inadequately.

View Replies!   View Related
Highest 3 Year Average Over A Span Of Ten Years
I have a list of people with 10 years of salary history for each (in ten consecutive columns on the spreadsheet).

I need to calculate the HIGHEST 3 consecutive year average salary for each (if they have less than 3 years with salary, then it should just average the years the do have, be it 1 or 2 years).

Here is the kicker: some people have breaks in service (for these years, there is a blank in that entry). These years should be ignored and skipped in calculating the avergaes.

So if someone had salary figures in years 1, 2, and 4, but a blank in year three, the average of years 1, 2, and 4 would constitute one three year average (whether or not it is the highest is a whole other matter...).

I have been round-and-round the best way of doing this. I was thinking of maybe creating a UDF that calculates a three average, then do it up to 8 times (one for each starting year) in 8 "helper columns", and taking the highest average.

View Replies!   View Related
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.

View Replies!   View Related
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?

View Replies!   View Related
Scrol Bars To Only Move The Form To The Edge Of The Sheet Instead Of Ten Or More Cell Beyond
I have forms and want the scrol bars to only move the form to the edge of the sheet instead of ten or mor cell beyond.

View Replies!   View Related
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"


View Replies!   View Related
Latest Value Appear In The Follwoing Cell
i have this kind to table

200
100
250
65

and i want the latest value "65" will appear in the follwoing cell

and when more values added like if the value "70" added then in the cell the value "70" will be appear.


View Replies!   View Related
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.

View Replies!   View Related
Position Of Latest Thread
XYZ001

XYZ002XYZ003

XYZ004XYZ003XYZ004XYZ001XYZ002XYZ004

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?

View Replies!   View Related
Execute A Data Download Every Ten Seconds Until There Is A Value In C11 And Then Send An Email And Close
All this macro is supposed to do is execute a data download every ten seconds until there is a value in C11 and then send an email and close.

What seems to be happening is that it downloads, emails, closes... but then in ten seconds the sheet reappears and does it all again.

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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
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...................

View Replies!   View Related
Maximum / Latest Date In Row
If I have a row full of dates and I want to find the most recent date, how do I do that?

View Replies!   View Related
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?

Columns are:

Product Code|Last Sales Date|Cost Price

View Replies!   View Related
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?


Option Explicit

Sub Get_Availability_Data()

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 & "" & ................................................

View Replies!   View Related
Search For Text :: Return The Last (latest) Date
I have text in column X and dates in column A...

How can I return the last (latest) date (column A) that "hold" was used (in column X)?

View Replies!   View Related
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).

View Replies!   View Related
Finding The Newest / Latest Date In Each Record
I have a table that contains names and dates. I need to find the most recent / latest date in each record. For example:

Names Dates
A 01/02/09
A 05/25/09
B 06/09/09
B 07/30/09
B 01/02/09
C 03/09/09
C 02/28/09
D 12/12/09

The results should be:
A 05/25/09
B 07/30/09
C 03/09/09
D 12/12/09

View Replies!   View Related
Formula: Spreadsheet To Automatically Update The Latest Results
I've adapted a spreadsheet to automatically update the latest results.

But my formula (D11), that I got from here, has now stopped working.

I suspect this is due to the formula I've used columns H and I. But I have no solution.

Also the very similar formula used in M15 to M44 has also stopped working, probably for the same reason.

View Replies!   View Related
Formula At The Top Of The Sheet To Show The Latest Figure In Column
Having problems with the following:

First Column Second Column Cumulative (B-A)
1 10 9
2 20 27
3 30 54
4 40 90

Every day,a new row is added and i want a formula at the top of the sheet to show the latest figure in column C.

View Replies!   View Related
Conditional Sum: Return The Latest Date Before The Stock Turns To Be Greater Than The Value In Cell D3
In the attached workbook - the stock Inventory is increased, every second day, by the value shown in cells of column A. Column B displays the date of the update. I'm looking for a Formula (might be an Array Formula) that will return the latest date before the stock turns to be greater than the value in cell D3. I managed to solve it, in cell F3, but with the help column C.

View Replies!   View Related
Get The Average Of Every Ten Cells Out Of 1300 Cells?
I have 1300 vertical cells.

Is there a quick way to get the average of every ten cells? i.e. to end up with 130 averages of successive groups of ten cells (cell 1-10, 11-20, 21-30 etc.)?

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved