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 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 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 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
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
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
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
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
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 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
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
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 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
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
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
Using Offset From Latest Month To Calculate 3-month Average Within A Range
I have a spreadsheet that has columns of monthly values for three years of financial data and where the values for the latest month are added to the last column. Months that have not been completed will have a zero value (e.g. Jul-09).

Jan-09

Feb-09

Mar-09

Apr-09........


View Replies!   View Related
Sum Only The Last Four Entries
I have a spreadsheet that i update weekly. i need is a formula that will just sum the last four weeks. Example column A rows 1-52 will be updated each week in row 53 i just want the last four weeks to be summed.

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

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

View Replies!   View Related
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
May 2007
Jun 2007 20
Jul 2007 0
Aug 2007
Sep 2007 10
Oct 2007 0
Nov 2007 10
Dec 2007 18
Jan 2008 8
Feb 2008 6
Mar 2008 0
Apr 2008
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.


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

Example:

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

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,

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


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

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

Eg
Cust number Product Number Expired Date
1000018000 31/12/2006
1000018000 31/12/2006
10000180002811/2006
1000018001 31/12/2006
1001020010 31/12/2006
1021518000 31/12/2006

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.

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

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

Sub DeleteTheOldies()
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

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

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

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

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

=COUNTA(D6:D15)

TS
TS
VR
IS
IS
OS
VR
OS
8 (total)

View Replies!   View Related
Sum First 13 Entries Only (formula)
Is it possible to have a formula that sums only the first 13 entries in the range f8:f1006, where the value 13 can change as it is a result of another formula linked in cell f6

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