Comparing values in different rows

[malaydash]

I have an Excel sheet in which I have got date wise sales figure in ascending order(oldest to newest) in two different columns (one column for date & the second for sales figure). I want the program to find the last "sales figure" in the sales column (by going backward) which was less than today's (or for any specified date) and then stop looking further & provide the data in the third column.

What if I need the figure for last two sales figure less than today's) or for more?

Hope I made myself clear..

Please help

 

[malaydash]

DATE SALES_IN_TON SALES_LESSTHAN_TODAY'S
16/04/2010 846.3
19/04/2010 847.15 846.3
20/04/2010 857.65 847.15
21/04/2010 851.25
22/04/2010 851.7 851.25
23/04/2010 822.95
26/04/2010 811.95
27/04/2010 810.2
28/04/2010 814.6 810.2
29/04/2010 803.9
30/04/2010 791.5
03/05/2010 744.25
04/05/2010 728.3
05/05/2010 725.2
06/05/2010 696.25
07/05/2010 685.35
10/05/2010 699.8 685.35
11/05/2010 685.9
12/05/2010 680.55
13/05/2010 689.15 680.55
14/05/2010 673.55
17/05/2010 831.45 673.55
18/05/2010 830.65 673.55

Here once again I am putting up my problem. As shown in the above example I have with me the 1st two columns of data (Date & Sales_IN_TON). The third column is what I need to find out. I have written the figure by manually searching the sales column by going backward (Bottom to top) & picking up the value which is less than the sales figure for the date in which I am interested, Some cells are left blank as there is no value less than the current value in the list.

My question here is, "Is there any way to do this by using any sort of formula?"

A solution using of MS-Access will also be appreciated.

Thanks

Regards

 

[JPearson]

I dont believe there is formula that will solve this problem. There is the option to use VBA to look at the cell values and, compare the previous cells and stop when it reaches the first matching criteria.

 

[vbaInet]

That's it, vba. You would:

1. Read all the values from column 2 into an array, but when storing you start from the Ubound() of the array to the LBound()
2. Sort the sheet by the date field in descending order
3. Then start your check, if the first array item is less than then value in the current cell of column 2, set the value of the third column to that array item. Delete that item from the array, or move to the next item in the array, like a pivot.

 

[Brianwarnock]

Sorry vbaInet but I don't think that works as the answer can be further back, see his last 3 entries for an example. Mind you I might just not be bright enough to see it.

But vba code is the only way to tackle this

Brian

 

[vbaInet]

Sorry vbaInet but I don't think that works as the answer can be further back, see his last 3 entries for an example. Mind you I might just not be bright enough to see it.

If you weren't bright enough you wouldn't have spotted it

Alright then, revise to step 3. Only delete from the array or move to next in array when the cell in column 2 is equal to the current pivot value.

By the way, you've not been regularly coming on here Brian

 

[Brianwarnock]

By the way, you've not been regularly coming on here Brian

My Grandson is occupying my time, he's more fun

Brian

 

[vbaInet]

Haha! I bet he is

OP: Instead of having to reorder by date in descending order you save a bit of time. Just ignore step 2 and start step 3, checking from the bottom up.

 

[Brianwarnock]

This has probably been solved now but with a bit of time on my hands I came up with the code below. Just select the cells in columnB and run the macro.

I'm not saying its the best, hell I've not even indented the code, but its a start.

Brian

Sub blah()

Dim currrow As Integer
Dim targetcell As Integer
For Each c In ActiveWindow.RangeSelection
currrow = c.Row
targetrow = c.Row

If currrow = 1 Then
c.Offset(0, 1) = ""
GoTo gonext
End If

If c.Value > c.Offset(-1, 0) Then
c.Offset(0, 1) = ""
GoTo gonext
End If

' We now have the situation were the value has fallen
currrow = currrow - 1
Do Until currrow = 0
If Cells(targetrow, 2) > Cells(currrow, 2) Then
c.Offset(0, 1) = Cells(currrow, 2)
GoTo gonext
Else
currrow = currrow - 1
End If

Loop
c.Offset(0, 1) = "n/a"  'The value fell but there were no smaller values
gonext:
Next

End Sub

 

[vbaInet]

This has probably been solved now but with a bit of time on my hands I came up with the code below.[/code]

No way!! Your grandson let you free for a while?

 

[Brianwarnock]

No way!! Your grandson let you free for a while?

I had him all morning, but the great thing about grand children is you get to have your nights free, if I had my time again I'll skip the kids and go straight to the grand kids.

Brian

 

[vbaInet]

..., if I had my time again I'll skip the kids and go straight to the grand kids.

That's so not happening

 

[malaydash]

Dear All

Thanks a lot to all who has replied in the post & I apologize for the delay in my reply.

As it is clear from your replies that the solution lies in VBA , but VBA is like Greek & Latin to me.

Can anybody help me regarding how to use the solution & where to put in the code?

Thanks & Regards

 

[Brianwarnock]

whilst in your spreadsheet click on Alt F11 this opens up the VBA editor, select the sheet you are working with from the left pane and paste the copied code into the right pane.

You can change the name of the the sub.

to run click on the first cell in the col and highlight down to the last this is the selected range
Then click tools - macro - macros and then the macro name then run, else create a control button on the sheet and allocate the code to that.
Read help but come back if you have a problem.

Brian

 

[malaydash]

Dear Brianwarnock

Thanks for your prompt reply & valuable suggestion. I'll try the method you have suggested & get back to you if I face any problem (this may take 3 to 4 days as I will be out of town on some official work).

Anyway thanks once again

Regards

 

[malaydash]

Dear Brianwarnock

Sorry for the delay, but I was stuck up in something urgent. I tried your solution but couldn't able to get the result. Here with I am attaching an excel sheet. Can you please add the code there & post it here so that it will be easier for me to get this sorted out.

Sorry for asking so much. Hope it is not a pain for you.

Thanks in advance.

Malay

 

[Brianwarnock]

The problem is that you have inserted another column and now we are working on column C not B.
I haven't time to do much with the code as about to go on holiday so just made a the quick column change.
You should examine and understand the code rather than just copy and paste it.

Brian

Sub blah()

Dim currrow As Integer
Dim targetcell As Integer
For Each c In ActiveWindow.RangeSelection
currrow = c.Row
targetrow = c.Row

If currrow = 2 Then
c.Offset(0, 1) = ""
GoTo gonext
End If

If c.Value > c.Offset(-1, 0) Then
c.Offset(0, 1) = ""
GoTo gonext
End If

' We now have the situation were the value has fallen
currrow = currrow - 1
Do Until currrow = 0
If Cells(targetrow, 3) > Cells(currrow, 3) Then
c.Offset(0, 1) = Cells(currrow, 3)
GoTo gonext
Else
currrow = currrow - 1
End If

Loop
c.Offset(0, 1) = "n/a"  'The value fell but there were no smaller values
gonext:
Next

End Sub