UKH

I'm not an excel person, I guess the answer is pretty easy but I can't get it.

I have a column of dates, next to a column of values.

Then, next to each value, I want to put the last date that value was higher.

Can anyone help?

Thanks in advance.

Fred.

You probably want VBA to do this. VBA is a programming language that comes with excel that allows you to do more sophisticated things that formulas can't do. There are a lot of tutorials on youtube:

https://www.youtube.com/results?search_query=vba+excel+tutorial+beginners

The learning curve will be steep but when you get the hang of it you'll be amazed at what you can do. Work that involves trudging through spreadsheets can be done in a few seconds with VBA.  I think about 10 % of the UK workforce would be redundant if the bosses knew what you could do with VBA! 

In general when learning a programming language, just take it steady. Set yourself a task that's just beyond your abilities and work on it. When you get stuck just google it and chances are somebody out there will have solved your problem. Once you've done that try a slightly harder problem.

P.S. you don't give enough info to solve the problem. It sounds like you have just 1 value and 2 columns, so I don't understand  the 'next to each value, when was that value higher' bit. I might be able to write a VBA script quickly to get you started.

Thanks Red Rover. I used to do VB, about 25 years ago, and I've forgotten most of it by now. I could be familiar with the language, but I've never used it in Excel, only Access and standalone exe programs. I only have this one spreadsheet, and I think I'm a bit too old now to learn such a big subject!

I want to end up with three columns:

Date.          Value.         Last date that value was higher.

Grateful for any help.

I agree that we don't seem to have enough info to be able to offer useful advice. I'd suggest clarifying and understanding what the OP is trying to achieve first, before concluding that VBA may be the best solution. The vast majority of consumer Excel tasks can be (and are best) achieved without recourse to VBA.

OK so the end result you are after would look like this:

Date         Value         last date higher

1                3                 none

2                1                   1

3                2                   1

4                6                   none

5                2                   4

6                1                   4

Is this right? 

If this is correct I'll write a script to do it. I'm not sure how I'll get the script to you as pasting code into UKC probably sets off some cyber-security alarm but we can see. 

You might be right I just find VBA quicker than using the functions for a most things. Maybe everything looks like a nail if you usually work with a hammer! I once had a boring office job that I automated with VBA, giving me loads of free time, it was wonderful! 

Could be messy since most of the lookups require the values to be sorted which would bugger up the date ordering.

I would be tempted by the vba option.

That would be ideal, thanks. The only improvement would be if the 'none' was replaced by the date on that line:

But beggars can't be choosers, so any help is appreciated.

Think this is fairly achievable with array formulas, no need for VBA, if I'm understanding the problem right (based on Red Rover's table).

I've done it with different working separated into different columns for clarity, but they could all be combined to give the usual single-cell monstrous formula that Excel seems to inevitably lead to.

----

Assuming a table (CTRL-T) with column names "Date" and "Value":

Determining whether the current date is the largest value so far is easy:

=IF([@Value]=MAX(IF([Date]<=[@Date],[Value],0)), "Highest to date", "A previous value is higher")

You need to press CTRL-Shift-Enter instead of just Enter after entering the formula, to denote it as an array formula.

Then the meat of the work is split into 3 sections - finding the previous maximum value; finding the index of that value in the table; and using that index to find the corresponding date.

A new column ("Previous max") has formula:

=MAX(IF([Date]<=[@Date],[Value],0))

Again, need to enter as an array formula (CTRL-Shift-Enter).

Column "Previous max index" has formula:

=MATCH([@[Previous max value]],[Value],0)

This is not an array formula, so enter as usual (just press Enter).

Column "Previous max date" then uses that index to find the date:

=INDEX([Date],[@[Prev max index]])

Again, not an array formula - just press Enter.

Then if you just want one mega-formula instead, putting them together gives:

=INDEX([Date],MATCH(MAX(IF([Date]<=[@Date],[Value],0)),[Value],0))

Which must be entered as an array formula.

----

If this doesn't make sense, doesn't work or makes some faulty assumptions let me know, but I think it's OK. Sorry if this is a bit hard to read, don't know how to format things nicely on UKC.

EDIT: Just seen that you actually want the current date if it's the max so far, so the mega-formula alone should do the trick.

That looks possible, thanks. Is  'Date' and 'Value' the names of the column? If so. where do i enter the formula?

I just gave it a go in the third column, and it works brilliantly!

Many, many thanks for your help.

And many thanks to Red Rover for your time and suggestions. UKC is great!

Fred

Yes, "Date" and "Value" are the column names with the data in them. If you just use the combined formula, then you only need one more column, call it whatever you like. The formula should be entered alongside the data. This assumes the data is already in (or can be moved into) an Excel "Table" (what you get when you highlight an area and press CTRL-T). It will work without being in a table, but the formulas would need adjusting slightly.

Glad to help! Work going a bit slow this morning...

OK here is the VBA version. First do this:

1) save your spreadsheet as a new spreadsheet but make sure it is a macro-enabled spreadsheet

 https://www.dummies.com/software/microsoft-office/excel/saving-and-using-an...

2) Enable the developer tab if not already done

https://support.office.com/en-gb/article/show-the-developer-tab-e1192344-5e...

3) Click developer, visual basic and insert a new module: 

youtube.com/watch?v=IIuKCxPlaTY&

4) Copy and paste the following code into the module:

Option Explicit

Sub findDates()

Dim row As Long, a As Long, dateCol As Long, valueCol As Long, answerCol As Long, startRow As Long
Dim highestDate As String

startRow = 2
dateCol = 1
valueCol = 2
answerCol = 3

Dim highestVal As Single
highestVal = Sheet1.Cells(startRow, valueCol)
highestDate = Sheet1.Cells(startRow, dateCol)

Sheet1.Cells(startRow, answerCol) = Sheet1.Cells(startRow, dateCol)

row = startRow + 1
Do While Sheet1.Cells(row, dateCol) <> ""
    If Sheet1.Cells(row, valueCol) > highestVal Then
        Sheet1.Cells(row, answerCol) = Sheet1.Cells(row, dateCol)
        highestDate = Sheet1.Cells(row, dateCol)
    Else
        Sheet1.Cells(row, answerCol) = highestDate
    End If
    row = row + 1
Loop

End Sub
 

5) You will see these lines of code:

startRow = 2
dateCol = 1
valueCol = 2
answerCol = 3

Change them so that startRow is the first row where the data is. Assuming you have the headers "date","value" etc at the top of the spreadsheet then startRow will be 2. dateCol, valueCol and answerCol are the columns where your dates are, the values are and where you want the date the value was last highest to be. These need to be numbers, 1 for A, 2 for B etc.

I'm assuming you are working on sheet1, of not then every time you see a sheet1.cells..... change it to sheet2 or whatever your sheet is.

Then save the code and click the run button (the little green arrow in the bar near the top), it should work. 

It's a bit more work setting it up than using a formula but once you get the hang of VBA it pays off massively, in terms of what it allows you to do that you couldn't before it's a bit like the invention of the cam in climbing! 

No problem! The formula answer is probably a lot less faff for you to use than setting up VBA for the first time but I wrote some VBA anyway as it's good practice. 

Thanks again.

I see you've already sorted your problem, but waste not want not - here's another solution

Assuming dates and values are in headed columns A and B, copy this into C3 (the first row for which it makes sense):

=MAXIFS(A$2:A2,B$2:B2,">"&INDIRECT("b"&ROW()))

Won't work on older versions of Excel but equivalent array functions should.

For a one-off

Select both columns then Sort by Value then Sort By Date

That's a nice simple solution! By the way John I think I met you in a pub in Hathersage when I was a teenager, probably around 07 or 08. I was moaning about not being able to do Goliath's Groove and you told me to bridge it or something. I asked what you'd done round the Peak and you borrowed my guidebook and pointed to some E10. Not sure why that memory has suddenly emerged now! 

It's a simple solution as long as all your dates are in chronological order!

As for meeting you in Hathersage, I confess to drawing a blank on the memory. The GG advice seems about right but I'm somewhat surprised by the rest as I doubt I would have pointed you to my hardest headpoint unless you'd specifically pressed me to do so!

Edit: Maybe I was just having one of those insecure days where validation seems like a good plan! I'm sure we must all have them once in a while.

Actually it wasn't you, the route was Equillibrium which I've just realised wasn't you! Or maybe I've miss-remembered or dreamt the whole thing and it never happened. Sorry for the confusion!