How do I get the value of previous category?

[rheeyeonsang]

Hello,

I'm trying to get the value of a previous category for my personal finance sheet.
I have different accounts and I want to have both old balance and new balance show that is maintained under the same table.
1.png

Bascially, I would need the current 'old balance' to show previous 'new balance' in the same 'account'.
To simplify this question, here is an example.
3.PNG

As you can see, D7 shows C2.

I was trying to use Index (New Balance Array, Row # of previous category).
What I'm having trouble is getting the row# of the previous category.
I was using COUNTIF (START OF CATEGORY : CURRENT CATEGORY, CATEGORY) to get the # of row that the previous category is within the category.
Now, I am stuck on using this number to get the row number in overall.

[Ochimus]

rheeyeonsang,

Macro in attached sample runs when you change any value cell (example assumes you put the value in Col D).
It "undoes" the new price, copies and stores the old value, then reinstates the New Value.
If the Old value and the New one are different, it pastes the "old" value into Col F of that row.
If you change the Col D price again, the first change then becomes the "old" price in Col F, and so on.

Option Explicit
Dim f As Long, OldValue As Long
Dim rng1 As Range
'Dim OldValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ErrorHandler
    
    Set rng1 = Intersect(Target, Range("D2:D5"))

    If Target.Cells.Count > 1 Then Exit Sub
    
    If rng1 Is Nothing Then Exit Sub
    
    Application.EnableEvents = False

    Application.Undo
     
    OldValue = Target.Value
     
    Application.Undo
     
    Application.EnableEvents = True
     
         If OldValue <> Target.Value Then
         Target.Offset(0, 2) = OldValue
         End If
  
    
 
ErrorHandler:

    Resume Next
    Application.EnableEvents = True
 
 End Sub

Hope this helps

Ochimus

[Gregb11]

Since you're using O365, you can use the new XLOOKUP formula. Try this in cell D2 and copy down

=XLOOKUP(B2,$B$1:$B1,$C$1:$C1,"N/A",0,-1)

This gives you what you are showing under "What I want"

[rheeyeonsang]

That's amazing.
The xlookup function worked perfectly.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.