Finding Closest Date In A List
Apr 9, 2008
I've been turning this problem over it my head for the last day and can't seem to come up with a decent solution, so I figured I'd post it here to see if anybody else had any bright ideas.
I have a data sheet with a dropdown list on it to select the month and year that determines how the rest of the data sheet populates. Basically I'm giving weekly breakdowns of data for a month at a time, with the "Week Ending" heading (which are dates - Saturdays), populating off of the month and year selected from the dropdown.
So, to give an example, if somebody selects March 2008 in the dropdown, it will populate the week ending dates of 3/1, 3/8, 3/15, 3/22, 3/29, and then pull out the specific data for each week based on those dates.
What I'd like to do is write a formula that will find the first Saturday of the month. The way I have it jury-rigged right now is a list of months with the first Saturday listed next to it and a vlookup to grab the date of the first Saturday. I envision using this report indefinitely into the future and I'd like to avoid having to keep adding/changing the manual list of Saturdays, instead preferring to have a way to determine the date.
View 9 Replies
ADVERTISEMENT
May 14, 2009
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 9 Replies
View Related
Mar 3, 2009
I need to scan a list of numbers and find the nearest number to say 80... ex
1
50
23
43
79
102
The formula would tell me the closest number is 79.
View 9 Replies
View Related
Nov 25, 2006
a spreadsheet in Excel. I have names with scores. Then I have the winning score. I need a formula to find the score closest to zero and to display the name of the winner.
Ex: Names A1:A4 and Scores B1:B4. Winning Score in B6 and list name in B7.
Ana 16
Bob 2
Charles 8
David 11
Winning Score 10
Answer should be 11 which is David, since David is only -1 away compared to the others.
View 9 Replies
View Related
Feb 26, 2014
I have two problems sort of rolled into one. I have a spreadsheet with a list of skills to perform in cells a2:z2. Under those I put in dates where those skills are performed so a3:a100 , b3:b100 and so on. At the moment I have the these dates populating another sheet with the list of skills going a2:a200 and the dates in b2:b200.
Annoyingly I have been asked set it up so it inputs the other way so my problems are: The skills in the second sheet are in a different order than in the first sheet so its not a straight correlation between the two so I would need to search for the title and match it to the second sheet. Then the second problem is as the will be numerous entries I need to find the next available space in the first sheet. So can you add data at the bottom of a list??
View 6 Replies
View Related
Dec 15, 2011
This might be pretty tricky. I'm trying to create formulae that can find non-empty cells in a list, and separate by date.
Example:
The following are given:
DateItem
1/23AAA
1/24
1/25BBB
1/26
1/27
1/28
[Code]....
I would like:
List 1 (Jan) List 2(Feb)
AAA BBB
BBB CCC
The original equation was for finding non-blank items without separating by date was given to me below Hopefully it's much easier with it: [URL]...
View 1 Replies
View Related
Mar 21, 2013
I have a list of Y-intercepts (Column B - highlighted in Yellow) and I'd like to sort it based on a certain value (in J4) and get the 10 closest numbers above that value and the 10 closest values below that value followed by the remaining numbers.
View 7 Replies
View Related
Apr 9, 2014
I have a list with random ordered numbers (each number in a cell of a row): 1, 2, 6, 8, 11, 12, 16...
On the other hand I get a value (x). I want a formula that:
- If x is in the list, returns x
- If x is not in the list, returns the next higher value. For example, if x=8, returns 11
I want also a variation of the same formula, that returns the next lower value. For example, if x=8, returns 6.
Note that x will never be smaller than the first number on the list or higher than the highest number in the list.
View 1 Replies
View Related
Jan 15, 2008
in column A I have a set of dates starting say fromA2 till A300 in accending order.
in the other hand I have a date let's say in B2
I am looking to a formula to find out the nearest date value of B2 from the A column and obtain the row number.
View 5 Replies
View Related
Dec 26, 2012
I have a list with all our company cars (300). I also have a list with all the transactions of the fuel cards (12000 lines). Now I need the find for every company car (exact match) the nearest fuel-consumption-date (closest match to the given starting date) and take the mileage of that transaction. I've added an example. I have tried linking carnr and date (like carnr&"#"&date) and match these, but I don't always get the right car number.
View 1 Replies
View Related
Apr 9, 2014
I cells B5:B15 I have a list of dates from last year. I need a formula that I can put in cell D5 that gives me the date that is closest to the same day last year.
Example:
If today is 4/9/14
and
From B5:B15 there were the following dates:
3/12/13
3/19/13
3/29/13
4/5/13
4/8/13
4/12/13
5/6/13
5/29/13
Cell D5 would contain
4/8/13 since it is the date closet to same day last year.
View 4 Replies
View Related
Dec 19, 2009
I am looking for a formula to return the expected row/location in range B2:B13 as shown in col. F
Col. B dates are and cannot be sorted
The idea is to find the exact or the closest lower date to those in col. E
as per two criterias (col. D+E)
I prefer no helper column and no macro
(see attached WB)
View 14 Replies
View Related
Jun 23, 2014
I have the following table of stocks with corproate action types and dates with a ticker / identifier per stocA1:
Type
B1:
ID
C1:
Date
[Code]....
I am trying to find the NEXT Dividendcorporate action that is CLOSEST to the current date. I've done some google trawling and found a few formulae that seem to work only if the identifiers or dates are in a particular order. ALso having trouble using TWO criteria
View 1 Replies
View Related
Mar 13, 2014
I have a list of ID Numbers and Dates. Let's call this "List 1".
I also have a second list "List 2" which also contains a list of ID Numbers and Dates as well as a third column for Rating.
I am trying to extract the Rating for each ID Number from List 2 and display in List 1. The problem is that the Rating can change with time, so List 2 contains several different Date and Rating values for the same ID Number.
What I need to do is lookup the ID Number from List 1 and return from List 2, for that ID Number, the Rating at the closest date prior to the date for that ID Number in List 1. Example below:
List 1
List 2
ID Number
Date
Rating
111
17/04/13
5
[Code] ..........
View 9 Replies
View Related
Apr 26, 2014
I have two lists of product data, one for buyers and one for sellers (these are listed as A, B, C). The product names are not exactly the same (Eg Playstation and playstation three should be matched), I would have thought using the FuzzyLogic add in to match these would be the way forward! I need to rank the sellers by how many of their items appear on the buyers list
View 8 Replies
View Related
Apr 28, 2009
i have 2 columns of data.
Column A is state
Column B is money owed to that state
So lets say
A1 is florida and B1 is 29,000
I am trying for column C to tell me what values (and if possible the cooresponding state) are the 2 closest values above it and the 2 closest values below it in B1:B50. It can appear as a string of text like 27,000 CA 28944 (OR) 31000 (FL) 31200 (GA)
View 9 Replies
View Related
Oct 7, 2011
On Sheet 1 I have a list of employee names (John, Bob, Ross etc...)in column A and in column B I have a list of employee bonus points (1, 5, 3 etc...). On Sheet 2 I have the same setup but the list of employees on sheet 2 is a lot longer than the ones on sheet 1, all employee names are on sheet 2.
I need a macro that will go down to each name on the list on sheet 1, column A, and copy the employee bonus points then go to sheet 2 and find the that employees name in the list and paste the bonus points in column B. This must be done until the last name on sheet 1 is found and all points are copied to their corresponding names on sheet 2.
View 3 Replies
View Related
May 4, 2009
I need a VBA code which tells me the date of previous monday of any date. The user types in any date to the cell A1, and I need the date of previous monday to be inserted to the cell A2.
For example:
A1: 9/5/2009 --> A2: 4/5/2009
A1: 19/5/2009 --> A2: 18/5/2009
A1: 27/12/2009 --> A2: 21/12/2009
View 4 Replies
View Related
Jun 30, 2014
Find the weekend date of each dates.
I am generating a report , for which i need to find the weekend date of the corresponding date.
Following is the code that I am using to generate the dates. Need to generate the corresponding weekend date as well.
Also attached is the output file I am looking for.
Attached File: IQN- Apr 2014 - Souvik Ghosh.xlsx
View 6 Replies
View Related
Dec 3, 2009
I have dates (12/3/09) in columns A, G, K and P.
There may or may not be a date in any of these cells.
How do I find the very last date in the worksheet.
I want to do this with code.
View 9 Replies
View Related
Sep 14, 2009
I am creating an interactive tool in Excel.
One of the Fields is "Employee Name"
I created a validation list that includes the names "Mary, Joe, Michael, and David"
When I plug in the name "Mary" I want to pick out her qualifications from a list I have created on a separate worksheet.
Passport
Mary
Joe
David
Lunch
Mary
Joe
Michael
Car
Joe
Michael
David
I want Excel to have the ability to find the name "Mary" from those three lists and reply back to me with the headers of either Lunch, Car, and/or Passport based on the lists Mary appears in. I want this to change depending on the Employee Name and I pick and the lists they appear in.
View 14 Replies
View Related
Apr 14, 2013
I am currently trying to organise a student yearbook for my pupils at school. We have given out sheets to students in order for them to nominate their peers for various awards such as 'Funniest Student', 'Most Caring Student' etc.
I am trying to collate all of the data into an excel workbook. I have made two sheets within a workbook, which I have formatted how I want them to look like. I want to insert all the student's responses into one sheet, and somehow, the top 3 most occurring nominations are found and are inserted into the appropriate cells in the separate sheet.
I have attached a copy of the workbook. Due to data protection the student names and tutor groups have been taken out and replaced with the word "REMOVED", yet I will paste them back in at a later time. So feel free to have a play around to see what I am talking about! I have filled the table with some dummy data just so you can see what I mean.
I am not at all sure how I would go about picking out the top 3 most occurring names for each award, but once I can do that, I will be able to insert them into the other sheet. How to automatically produce the top 3 most occurring names.
View 1 Replies
View Related
Nov 1, 2012
I have a spreadsheet with the first 17 tabs containing data and the last tab the 18th one contains a list in col a of 4,000 unique code items.
Is it possible to check if any of the codes items exist in any cell of the first seventeen tabs.
View 1 Replies
View Related
Feb 26, 2010
Excel 2002
I have a number (22.3) in a cell that is there from an Hlookup function. I am then using a vlookup to lookup the 22.3 (from the hlookup) in a column. The number is in the column, but the vlookup function cant find it. If I were to type over my hlookup formula so that I am hand-typing in "22.3" instead of it being their from the hlookup, then the vlookup works just fine. I hope that is not too confusing. I dont understand what is going on. I checked to make sure none of my cells are rounding and therefore creating a look-alike number when it really isn't and that is not the case.
View 9 Replies
View Related
Apr 26, 2014
I know how to make customs lists, but it's not getting me anywhere.
Let's say I have a lot of data containing values as follows and in the following list I sorted them from small to large:
5a
5a+
5b
5b+
5c
5c+
6a
6a+
6b
6b+
6c
6c+
7a
7a+
7b
7b+
7c
7c+
8a
and so on... In the list, the date isn't sorted from small to large, many values (if not all) occur multiple times and on occasion some values are added to the column. With that last part I mean that you can see it as a batch of 500, and if so far the batch only contains 450 values there will be (for the time being until items get added) 50 empty cells in the column as well.
You can't order numerically or alphabetically. I know I can make a custom list and then just sort according to that custom list so the biggest value would come out on top or bottom but that's not what I'm looking for either, as the data needs to be sort by another colomn (dates in chronological order).
So I'm just looking for a formule saying "return the biggest value if the order of things is as I typed above'.
View 14 Replies
View Related
May 15, 2014
The basis of my needs is to be able to find in a list of names, a duplicate name from previous list and then grab and populate a number from that row. Here is an example
A B C D
Doe,Jane $1000 $250 $750
Smith,John $700 $100 $600
Wilson,Alan $1500 $1500 $0
I update the list, some names are added and others taken away. When I add the new list, column C of every name is $0. I want the formula to find names from the previous list and populate Column C with the value of Column C + D of the corresponding name. Also I have a separate formula that simply is D=(B-C) The result of what I would want this to look like is this...
A B C D
Doe,Jane $1200 $1000 $200
Doe,John $500 $0 $500
Smith,John $1000 $700 $300
As you can see there was a new name added to the list (Doe,John) and a name taken away from the list (Wilson,Alan). Column C is the only Column that I am wanting to auto populate. The Value in Column B is a variable that is managed by another program so you don't have to worry about that.
Keep in mind there will be multiple instances of this list and the same name will occur more than once in the spreadsheet. There are not duplicate names that are different customers though as my other program takes care of that. eg. "Doe,John" and "Doe,John #2". I only want it to grab the information from the previous list not ones previous to that list(if that makes sense) third example...
A B C D
Doe,Jane $1500 $1200 $300
Doe,John $500 $500 $0
Miller,Joe $700 $0 $700
Smith, John $1300 $1000 $300
My current method of accomplishing this is to select the previous list and print it. I then go down the new list and cross reference it with the old one keying in the values by hand. This would be a huge time saver as the list is 250 names and growing. I hope I have given enough information but undoubtedly I have not. So feel free to ask me any questions and for more details. I did not want to go to in depth for fear of my post being to long.
View 14 Replies
View Related
Apr 17, 2009
I have a list of numbers in Column A (labeled "Number"), with a list of dates in Column B (labeled "Date & Time"). Every date in Column B is unique, but some numbers in Column A appear more than once.
I am trying to create a simple formula that will automatically search for the LAST time a unique number appears in Column A (ignoring all previous instances of this number), and copy the date right next to it in Column B to another location on another worksheet.
I tried fussing with several of the built-in functions, but I really don't know what I'm doing, and the built-in help documents are filled with too much mathematical/programmer technical terminology for me to understand. All the tutorials I've looked at seem to think I want to add some numbers together, but I don't want to do any calculations!
View 4 Replies
View Related
Jul 28, 2009
In column A we have words, like this:
CHAIR
EXCEPT
WORLD
STREET
OUTSIDE
PEOPLE
WINDOW
LETTER
BOOK
In column B we have another list of words:
NIGHT
DREAM
EXPECT
THOUGHT
TEDIOUS
OFFICE
SETTER
LIBRARY
BEFORE
OFFER
I need a formula in C1 which will check all the words from column B and find the ones which are anagrams of some of the words from column A, and then return those words next to them.
(Anagram is the word made up of another word's letters, without repeating or omitting any, for example anagram of SPARK is PARKS).
In the example above, we would have the words: EXCEPT next to EXPECT (in C3), OUTSIDE next to TEDIOUS (C5), and STREET next to SETTER (C7).
View 10 Replies
View Related
Mar 17, 2014
I need to find 100 or so keywords in a column, I have the list but how would I search multiple keywords as there are 10k columns and it would take forver to search for them manually?
View 4 Replies
View Related
Jul 20, 2013
Finding an average age from a list of dates. This is just a small sample:
7/9/2013
7/10/2013
7/10/2013
7/10/2013
7/11/2013
7/11/2013
7/14/2013
7/14/2013
7/19/2013
7/19/2013
I tried using =AVERAGE(DAY(A1:A10)) CTRL + SHIFT + ENTER, but that just doesn't seem correct.
View 9 Replies
View Related
