Get 3rd future value for referent text

[smide]

Hello.


In columns A and B (A2:B600) I have a lists of customer names and their buying prices in columns D and E (D2:E600).
When customer name is in column A his buying price is in column D (same row of course) and when customer is in column B his bying price is in column E.


I'm trying to analyze the future buying prices for each customer, more precisely the 3rd future buying price (from referent/current one) for each customer and to place those results into columns F and G.


When name is in column A result (3rd future buying price) should be in column F and when name is in column B result should be in column G.


example (for customer Name1).


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]results[/TD]
[TD="align: center"]results[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Name1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD]Name1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]25[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Name1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Name1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD]Name1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]25[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD]Name1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]....[/TD]
[TD="align: center"]....[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

results explanation: for cell A2 (Name1 first occurrence) 3rd future occurence (Name1) is in cell A7 and buying price for that 3rd future occurence is in cell D7 = 2, so result in cell F2 is equal to 2

note: just as an option (if possible) I would like also to have reverse formula (3rd past buying price for each customer)

 

[Captain_Conman]

Smide,

Try running this code and let me know how it works for you...

Sub Macro1()


Dim C As Range


For Each C In Range("A1:B300")
    If C <> "" Then
    With Range("A" & C.Row & ":B600")
    Cells.Find(What:=C.Text, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    ActiveCell.Offset(0, 2).Copy
    C.Offset(0, 4).PasteSpecial
    Application.CutCopyMode = False
    End With
    End If
Next


End Sub

 

[smide]

Unfortunately, something's wrong with this code.

It deleted my data (prices) from column E and populate it with some numbers (incorrect results)...
But thanks anyway for trying, I appreciate it.

 

[Peter_SSs]

Try this, copied across and down.

Excel Workbook
	A	B	C	D	E	F	G
1						results	results
2	Name1			3		2
3
4		Name1			8		25
5	Name1			12		17
6
7	Name1			2
8		Name1			25
9		Name1			17
10
3rd Future