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

patrickmg17

New Member
Joined
Sep 26, 2023
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
thatthisanothersome moreagainlastly
86468​
4654​
54654​
651651​
Note 1
65168​
Note 26546+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
 
I'm confused, are you saying you need to this without enabling macros?
Yeah Im working this on multiple computers but the main one is in excel 2016 and cant enable any add-ins so that means i cant run any macros to solve it
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Without macro then the alternative is formula. However, formula can't delete cell or row, so I don't think you can do it with formula.
 
Upvote 0
I'm confused, are you saying you need to this without enabling macros?
I work between different computers and the main one this needs to run on uses excel 2016 and is blocked from using add-ins including macros so I need a new solution with out using any
 
Upvote 0
So theres no feasible solution? could it just rewrite the cells in another sheet? So it doesn't 'delete' the lines, it just doesn't write those lines
 
Upvote 0
So theres no feasible solution? could it just rewrite the cells in another sheet? So it doesn't 'delete' the lines, it just doesn't write those lines
If the result would be in another sheet, I think it's possible with formula. However, I have only basic knowledge of Excel formula. So, I hope somebody else will step in.
 
Upvote 0
uses excel 2016 and is blocked from using add-ins including macros so I need a new solution with out using any
Just wanted to clarify one thing - Excel Macros/VBA are NOT an Add-In.
You do not need to do/add anything special to have/use them - just enable them.
I understand that for Security reasons, many companies may not allow them. But just wanted to clarify that they are something entirely different than Add-Ins you may add on.

It sounds like you may be stuck in between a rock and a hard place - you want the functionality that Macros/VBA offer, but without using Macros/VBA.
I think any potential formula solution is going to be a bit complex/clunky and have to return the data to a new sheet (as formulas cannot delete rows, as was mentioned previously).
You may want to take KerryX on their suggestion to possibly make use of Power Query which was added to Excel with Excel 2010.
There is a whole forum here devoted to tools like Power Query called "Power Tools".
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top