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]

Do a Find and Replace (Ctrl-H). Search for "Note 2" and replace with "=INDIRECT(ADDRESS(ROW()-1,COLUMN()))" (without the quotes). It will reference the cell above it. You can then copy and paste values for the whole table if you want to get rid of the equations.

 

[alansidman]

An alternative with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,"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"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([another] <> "Note 1") and ([again] <> "Note 1"))
in
    #"Filtered Rows"

 

[patrickmg17]

Do a Find and Replace (Ctrl-H). Search for "Note 2" and replace with "=INDIRECT(ADDRESS(ROW()-1,COLUMN()))" (without the quotes). It will reference the cell above it. You can then copy and paste values for the whole table if you want to get rid of the equations.

this works well for all the Note 2 but any point for the Note 1s? I could through the lines but its still 1000s of points

 

[patrickmg17]

An alternative with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,"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"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([another] <> "Note 1") and ([again] <> "Note 1"))
in
    #"Filtered Rows"

Sorry im new to power query, where do i go to paste this code? and this would work in 2016?

 

[NateSC]

this works well for all the Note 2 but any point for the Note 1s? I could through the lines but its still 1000s of points

For the Note 1's, I would filter by that note and then delete the lines that are showing.

 

[alansidman]

1. It will work with 2016
2.
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.

 

[patrickmg17]

1. It will work with 2016
2.
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 its an Add-in anything like macros being and Add-in that needs to be enabled then i cant do it. Dumb but all that access is blocked and makes all my problems harder to solve

 

[NateSC]

You can add a column and use the equation in my Column H to detect which lines to delete. (Adjust A2:F2 to cover your whole range.) Add a filter to that column, filter by "Delete", Select the rows that have "Delete" (select the first row below your headers and then hit shift-ctrl-down), and then Delete those rows (right click, delete rows). Any rows hidden by the filter will not be deleted. This is much faster than deleting them one by one.

One downside, if you still have the equations you created with your find and replace of Note 2 and one of those ends up now being the first row below the header, the note will now mimic the header. If you copy all and paste values before doing the Note 1 filter, it will prevent this from happening. This would only break if you have information that is equation based and needs to stay that way. So, be careful, but you can get there.

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

Cell Formulas
Range	Formula
D3,E7:E8,A3:A5	D3	=INDIRECT(ADDRESS(ROW()-1,COLUMN()))
H2:H8	H2	=IF(ISERROR(FIND("Note 1",CONCAT(A2:F2))),"","Delete")

 

[alansidman]

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.