How do I replace multiple cells of text with the first Number value above them? (w/o macros)

[patrickmg17]

Excel 2016 and I cant allow macros due to annoying business restrictions

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.

I know some people have said its not possible to delete rows without macros but i figure i can accomplish the same goal by having a new sheet created and everything ported there EXCEPT the Note 1 lines that should be deleted. Essentially just making a new sheet without the stuff that will be removed

 

[NateSC]

Power Query is not an add in for 2016 and later versions. It is located on the Data Tab and is called Get and Transform Data.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

If I follow the query correctly, it only searches 2 columns for "Note 1". Is there a way to rewrite the query so it looks through the whole table without having to list all the columns? I have some cases where I could use this functionality.

 

[alansidman]

I've revised the code to search all columns and replace in all columns. This is more efficient and cleaner.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    RV=Table.ReplaceValue(Source,"Note 2",null,Replacer.ReplaceValue,Table.ColumnNames(Source)),
    FD = Table.FillDown(RV,Table.ColumnNames(Source))   
in
    FD

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
9
10	that	this	another	some more	again	lastly
11	86468	4654	54654	651651	Note 1	65168
12	86468	6546+4	84615	651651	32131	6545
13	86468	321651	98461	651	65165	65651
14	86468	651651	651651	6516511	6516	651
15	61651	321651	Note 1	4984436	31816	354684
16	161961	49649846	65464	32133	31816	6546514
17	619496	164	652000000	3131	31816	351651
Sheet1

 

[NateSC]

I've revised the code to search all columns and replace in all columns. This is more efficient and cleaner.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    RV=Table.ReplaceValue(Source,"Note 2",null,Replacer.ReplaceValue,Table.ColumnNames(Source)),
    FD = Table.FillDown(RV,Table.ColumnNames(Source))  
in
    FD

How do you re-write the Row Filter code to generically find "Note 1" throughout the Table?

#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([another] <> "Note 1") and ([again] <> "Note 1"))

 

[alansidman]

In this line of code

RV=Table.ReplaceValue(Source,"Note 2",null,Replacer.ReplaceValue,Table.ColumnNames(Source)),

change "Note 2" to "Note 1"