How do I replace multiple cells of text with the first Number value above them?

[patrickmg17]

that	this	another	some more	again	lastly
86468​	4654​	54654​	651651​	Note 1	65168​
Note 2	6546+4	84615​	Note 2	32131​	6545​
Note 2	321651​	98461​	651​	65165​	65651​
Note 2	651651​	651651​	6516511​	6516​	651​
61651​	321651​	Note 1	4984436​	31816​	354684​
161961​	49649846​	65464​	32133​	Note 2	6546514​
619496​	164​	6.52E+08​	3131​	Note 2	351651​

I have the above cells as an example. I want to replace every instance of "Note 2" with the number value above it. So for the first column all 3 "Note 2"s should say 86468. And the ones in column 5 should be 31816. This is the most important part i need, so if you can answer that I'm happy but there is another part i need to figure out as well; for every instance of a row having a "Note 1" in it, i need that whole row of data deleted. Most of these rows are were the Note 2 spots pull their replacements so this part has to come after they are replaced.

Using macros or just some simple replace coding would be great, the sheets i go through are 2000 rows long so keep that in mind, THANK YOU :D

 

[Joe4]

Welcome to the Board!

Within your "block" of data, is it possible to have any empty or blank cells, or will all cells have text or numbers in them?

 

[Joe4]

Assuming that your data begins in cell A1 and there are no blanks in your data, like in your example, this code seems to do what you want:

Sub MyMacro()

    Dim rng As Range
  
    Application.ScreenUpdating = False
  
'   Set range starting from cell A1
    Set rng = Range("A1").CurrentRegion
  
'   Remove all "Note 2" values
    rng.Replace What:="Note 2", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
      
'   Replace blanks with values from cell above
    rng.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
  
'   Copy formulas to hard-coded value
    rng.Copy
    rng.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
  
'   Delete all rows where "Note 1" is found
    On Error GoTo err_chk
    Do
        rng.Find(What:="Note 1", After:=ActiveCell, LookIn:=xlFormulas2, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        Rows(ActiveCell.Row).Delete
    Loop
 
err_chk:
    Err.Clear
    Application.ScreenUpdating = True
  
    MsgBox "Macro complete!"

End Sub

So here is what my sample data looks like:

And here is the output after I run the VBA code:

 

[patrickmg17]

Sorry im new to macros,
it says "Compile error: Named argument not found"

Is something wrong or am i just ignorant to something haha

 

[Joe4]

See here for instructions on how to insert a new module and add and run the VBA code.

Insert and run VBA macros in Excel - step-by-step guide

This is a short step-by-step tutorial for beginners showing how to add VBA code (Visual Basic for Applications code) to your Excel workbook and run this macro to solve your spreadsheet tasks.

www.ablebits.com

Also, does your data start in cell A1?

If you get an error message, does it give you a "Debug" option?
If so, click it, and tell me which line of code it highlights.

 

[patrickmg17]

Yes the data starts in A1, and i already did the same steps as the guide you sent before. Heres what pops up for me, I think something is misnamed somewhere? "FormulaVersion" gets highlighted

 

[Kerryx]

Power query,this may be a bit bloated but think it does all you need

Book1
	A	B	C	D	E	F
1	that	this	another	some more	again	lastly
2	86468	4654	54654	651651	Note 1	65168
3	Note 2	6546+4	84615	Note 2	32131	6545
4	Note 2	321651	98461	651	65165	65651
5	Note 2	651651	651651	6516511	6516	651
6	61651	321651	Note 1	4984436	31816	354684
7	161961	49649846	65464	32133	Note 2	6546514
8	619496	164	6.52E+08	3131	Note 2	351651
9
10
11	that	this	another	some more	again	lastly
12	86468	6546+4	84615	651651	32131	6545
13	86468	321651	98461	651	65165	65651
14	86468	651651	651651	6516511	6516	651
15	161961	49649846	65464	32133	31816	6546514
16	619496	164	6.52E+08	3131	31816	351651
Sheet2

Codes for Table1 - original data

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"that", type text}, {"this", type text}, {"another", type text}, {"some more", type text}, {"again", type text}, {"lastly", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","Note 2",null,Replacer.ReplaceValue,{"that", "this", "another", "some more", "again", "lastly"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"that", "this", "another", "some more", "again", "lastly"}),
    #"Added Index" = Table.AddIndexColumn(#"Filled Down", "Index", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Table1 (2)", {"Index"}, "Table1 (2)", JoinKind.LeftOuter),
    #"Expanded Table1 (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table1 (2)", {"Index", "Attribute", "Value"}, {"Table1 (2).Index", "Table1 (2).Attribute", "Table1 (2).Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table1 (2)", each ([#"Table1 (2).Index"] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Table1 (2).Index", "Table1 (2).Attribute", "Table1 (2).Value", "Index"})
in
    #"Removed Columns"

After the step adding the index in Table 1 above- copy the Table
and amend the code, basically you are creating a long list and going to filter toshow which row number contains the value Note 1
View attachment 99342

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"that", type text}, {"this", type text}, {"another", type text}, {"some more", type text}, {"again", type text}, {"lastly", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","Note 2",null,Replacer.ReplaceValue,{"that", "this", "another", "some more", "again", "lastly"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"that", "this", "another", "some more", "again", "lastly"}),
    #"Added Index" = Table.AddIndexColumn(#"Filled Down", "Index", 0, 1, Int64.Type),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] = "Note 1"))
in
    #"Filtered Rows"

When you Merge the 2 table using the index and expand the table you will see the rows just filter by null

 

[Akuini]

Another option to try:

Sub patrickmg17()
Dim c As Range
Application.ScreenUpdating = False
With Range("A1").CurrentRegion
    Set c = .Find(What:="Note 2", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not c Is Nothing Then
        Do
           c = c.Offset(-1)
           Set c = .FindNext(c)
        Loop While Not c Is Nothing
    End If

    Do
        Set c = .Find(What:="Note 1", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            If Not c Is Nothing Then
                c.EntireRow.Delete
            Else
                Exit Do
            End If
    Loop
End With
Application.ScreenUpdating = True

End Sub

Example:

Book2
	A	B	C	D	E	F
1	that	this	another	some more	again	lastly
2	86468	4654	54654	651651	Note 1	65168
3	Note 2	6546+4	84615	Note 2	32131	6545
4	Note 2	321651	98461	651	65165	65651
5	Note 2	651651	651651	6516511	6516	651
6	61651	321651	Note 1	4984436	31816	354684
7	161961	49649846	65464	32133	Note 2	6546514
8	619496	164	6.52E+08	3131	Note 2	351651
Sheet2

Result:

Book2
	A	B	C	D	E	F
1	that	this	another	some more	again	lastly
2	86468	6546+4	84615	651651	32131	6545
3	86468	321651	98461	651	65165	65651
4	86468	651651	651651	6516511	6516	651
5	161961	49649846	65464	32133	31816	6546514
6	619496	164	6.52E+08	3131	31816	351651
Sheet2

 

[Joe4]

Yes the data starts in A1, and i already did the same steps as the guide you sent before. Heres what pops up for me, I think something is misnamed somewhere? "FormulaVersion" gets highlighted
View attachment 99341

Are you really using Excel 365, or some earlier version?

Try changing the word "xlReplaceFormula2" to "xlReplaceFormula" and see if that fixes the issue.

 

[mohadin]

Perhaps

Sub test()
    Dim xx As Range
    Dim x As Range
    Dim c As String
    With ActiveSheet.Cells(1).CurrentRegion
        Set x = .Find("Note 2", , , 1)
        If Not x Is Nothing Then
            Do
                x.Value = x.Offset(-1)
                Set x = .FindNext(x)
            Loop While Not x Is Nothing
        End If
        Set x = .Find("Note 1", , , 1)
        On Error Resume Next
        c = x.Address
        If Not x Is Nothing Then
            Do
                If xx Is Nothing Then
                    Set xx = x
                Else
                    Set xx = Union(xx, x)
                End If
                Set x = .FindNext(x)
            Loop While x.Address <> c
            xx.Select
            xx.EntireRow.Delete
        End If
    End With
End Sub