1

I'm having a problem replacing certain values in my sheet. I want to replace the value of the cell with another value in the same row if it is above the latter value and I want to do that for every row, how do I do this?

For example 1, In row 2, in the cell C2 and D2, the value is greater than Z2, so I want to replace C2 and D2 with the value in Z2. And in the next row (row 3), in the cell F3,H3, and L3 the value is greater than Z3, so I want to replace these 3 cell with the value in Z3. I want to do this for multiple rows, how do I go about doing it?

Initially, I was thinking of using conditional formatting to first highlight the cells, and then manually changing the value but isn't it a bit too tedious, because you need to do a new rule for each row? I'm not sure if VBA or something is required as I have no experience in it.

EDIT: Example 2.

What I want it to be: What I want it to be Example 2.

So in the case of my example 2 picture, I wanna get E1 (Which has the value of 5) to be replaced by G1(Which is 4) while I wanna get B2 (which has the value of 11) to be replaced by G2 (Which is 2). Or in other words, I'm trying to replace any cell in each row with row G value. With condition that, that cell has greater value than row G.

  • 2
    Hi, sorry but would you care to add soma data samples. As stated it's not clear which are the cell you would be looking at to compare with Z:Z, or even how many values would be replaced. At glance it looks like VBA would resolve this problem very easely. – dmb Mar 12 '19 at 16:40
  • 1
    Would a normal IF statement not work for you? – Burgi Mar 12 '19 at 16:50
  • Please use standard cell references (Column followed by row). I've edited your question accordingly. If you actually want to replace values in cells, you will need VBA – cybernetic.nomad Mar 12 '19 at 17:03
  • if the cells in columns A:E contain formulas, wrap them in a MIN. Original A1 =SomeFormula() edit to =MIN(SomeFormula(),$G1) . If the cells do not contain formulas, then use VBA. – Ted D. Mar 12 '19 at 18:25
1

As you know, writing a formula in a cell replaces the value of a cell. Thus, without using a VBA solution, which in my opinion is absolutely not indicated here, the existing values cannot be changed. However, by using a New WorkSheet, you can view the desired effect of the MIN to the Original Sheet's data.

Thus:

Open a new worksheet.

screenie

=MIN(Sheet1!A1,Sheet1!$G1)

MIN function gets the smallest number of given inputs. So this will get the value of your input cell unless it is larger than the override value in G. Note the absolute reference $ to column G so that when you copy this formula across and down, the inputs are fixed how you want them.

So, again, this doesn't replace the initial values, but it gives you a new set of data that does have the values you want.

  • This solution is akin to help columns, but bigger, a whole different worksheet. Because it was not part of the OP, I missed that detail when reading the answer I thought was to the OP. Perhaps make it a bit clearer, along the lines of "The values cannot be changed, but by using a New WorkSheet, you can view the desired effect of the MIN to the Original Sheets data." – Ted D. Mar 12 '19 at 18:35
  • I totally disagree. I think you're assuming the OP is aware of things they are not, and that their requested solution is making assumptions that it is not. I submit that the OP is asking for 'the following data in such a format', which this solution accomplishes with minimal effort. As you know, since you suggested the same thing in a comment some time subsequent to my having provided it as an answer. That said, I've edited my answer as you suggested. – Alex M Mar 12 '19 at 18:40
  • What does 'totally' include? Do you disagree that making a whole new sheet is more extreme than creating helper columns? I guess if I can gloss over the original text of your answer, then you can read that the OP is "having a problem replacing certain values in my sheet" and interpret it to mean 'the following data in such a format'. I disagree that my comment 'suggested the same thing'. In no way do I suggest creating new data or new sheets, but rather suggested ways to implement changes to the existing data per the OP. – Ted D. Mar 12 '19 at 19:27
  • 1
    My position is that OP saying 'replacing values' is no more than the XY problem, and the overwhelming likelihood is that my solution is sufficient to need. This is a matter of interpretation, and your interpretation differs from mine. If OP returns to comment, perhaps we'll learn which interpretation more closely reflects reality. You're right I misspoke that you suggested the same thing; you suggested using the same function. Got me there. – Alex M Mar 12 '19 at 19:32
  • 1
    Sorry I'm actually confused with what the argument is about but I ended by using just the IF statement like what Burgi suggested. But the solution is similar to what y'all suggested too. I created helper column and just replaced the values if they were smaller than my G column, following which, I pasted over the values. But THANKS FOR ALL YOUR INPUTS! – Jax Mar 14 '19 at 15:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.