Copy value from other row into same column

[most]

Hello,
I need help with solving this issue, I want to find and fill data from another row and paste into same column.
I thought VLOOKUP could solve it, but it only works "down", not "up".

Orginal data looks like these
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Grade[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]Y_15[/TD]
[/TR]
[TR]
[TD]654321[/TD]
[TD]Y_16[/TD]
[/TR]
[TR]
[TD]654321[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

With

=VLOOKUP(A2;A:B;2;FALSE)

I get these results.
But expected result is that line 1 should have Y_15.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Grade[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]Y_15[/TD]
[/TR]
[TR]
[TD]654321[/TD]
[TD]Y_16[/TD]
[/TR]
[TR]
[TD]654321[/TD]
[TD]Y_16[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

Your example data suggests there will only ever be one "non-zero value" for each ID ???

If so,

in C2
=LOOKUP(2,1/((A$2:A$5=A2)*(B$2:B$5<>"")),B$2:B$5)
and copy down the column

 

[most]

Your example data suggests there will only ever be one "non-zero value" for each ID ???

If so,

in C2
=LOOKUP(2,1/((A$2:A$5=A2)*(B$2:B$5<>"")),B$2:B$5)
and copy down the column

Nope, I didn't get it to work, unsure why.
But I managed to solve it with this VBA. (observe that columnref is diffrent from example above)

For Each c In Range(Cells(7, 9), Cells(LastRow, 9))  If c.Value = "" Then
     Set foundRng = Range("B7:B" & LastRow).Find(c.Offset(0, -7).Value)
     If Not foundRng Is Nothing Then
       If foundRng.Offset(0, 7).Value = "" Then
        Set foundRng2 = Range("B" & foundRng.Row & ":B" & LastRow).Find(c.Offset(0, -7).Value)
        c.Value = foundRng2.Offset(0, 7).Value
       Else
        c.Value = foundRng.Offset(0, 7).Value
       End If
     End If
  End If
Next c

 

[most]

Need to bump this up, realized that my code doesn't work.
My code finds the value when it goes "down"(line 13), but not "up"(line 8).
Any one have a suggestion what is wrong? I can't see it...

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td][/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td]ID[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Grade[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td]

126302​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Y_10[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td][/td][td]

126302​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td][/td][td]

126301​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Y_10[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td][/td][td]

126301​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Y_10[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td][/td][td]

126300​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Y_10[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td][/td][td]

126300​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Y_10[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td][/td][td]

126299​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#A9D08E][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td][/td][td]

126299​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Y_11[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Sub TEST()
LastRow = 14
'Find Grade
For Each c In Range(Cells(7, 9), Cells(LastRow, 9))
  If c.Value = "" Then
     Set foundRng = Range("B7:B" & LastRow).Find(c.Offset(0, -7).Value)
     If Not foundRng Is Nothing Then
       If foundRng.Offset(0, 7).Value = "" Then
        Set foundRng2 = Range("B" & foundRng.Row & ":B" & LastRow).Find(c.Offset(0, -7).Value)
        c.Value = foundRng2.Offset(0, 7).Value
       Else
        c.Value = foundRng2.Offset(0, 7).Value
       End If
     End If
  End If
Next c
End Sub

 

[most]

Anyone?

 

[Special-K99]

Nope, I didn't get it to work, unsure why.[?QUOTE]

My solution works fine.
Copy and paste your example data into a blank sheet
So Grade is in B1

in C2
=LOOKUP(2,1/((A$2:A$5=A2)*(B$2:B$5<>"")),B$2:B$5)
copy down the column

Column C are your results and each row has a value dependant on the ID

 

[most]

Okej, I got your code to work now. Problem was that I running it in 'column I' which I guess creates some kind of circular reference.
I solved it by running the lookup in column A and then copy the data to column I, in my original data I need to run it for two columns and about 11000 lines, so it's not very neat or fast but it solved the problem! Thanks for you assistance.

 

[Special-K99]

Well yep, if you dont specify any row/column or cell references I have to assume the top left hand cell is A1.
I can't produce a formula that looks at a specific part of a spreadsheet without mentioning cell references.
Once the formula works it's then over to you to adjust to formula accordingly to reflect your actual sheet.