CODEWITHSUNDEEP
excelfilterediting
Mstuf
Mstuf

Reputation: 1

How to Save Edited Data from Filtered Excel Column when filter cleared

I cannot find answer to my need. All searches lead to more complex scenarios or just how to filter.

I have filtered a workbook to only display Rows with values in Column C between .01 and 5.55 successfully.

I then used a formula in a blank column ( J ) to add 1.35 to each of those values successfully. (=Cxx+1.35)

I need to now move those attained values in Column J to the replace the values in C and have those values retained when the filter is cleared.

In Continuing to search - Thru google I found: https://support.office.com/en-us/art...8-272422419b59

But the method only moves a portion of the cells.

I highlight the modified values in Column J - they appear copied in blocks since there missing row numbers between them. I highlight the Original values in C - (which is the same size data range) and tied Paste - Paste Values and Paste Special / Values but it does not paste correctly. The First Cell is the only one that pastes correctly. Only about half the cells are replaced and those values are incorrect.

Am I using the correct method to accomplish my need ? Or am I going about it wrong ? It seems the "logical" way with other things I do in my spreadsheet but ... have not tried to edit filtered info before.

Thanks for looking

Upvotes: 0

Views: 505

Answers (1)

teylyn
teylyn

Reputation: 35915

Why bother with the filtering?

Use this formula in column J, starting in row 2, copy down and then copy the whole column and use Paste Special > Values to paste into column C

=if(and(C2>=0.01,c2<=5.55),C2+1.35,c2)

Or, if you insist on doing the filtering method:

  • filter the data
  • enter your formula in the visible cells
  • clear the filter
  • select column J and copy
  • select column C and use paste special with the options "Values" and "Skip Blanks" ticked.

Upvotes: 1

Related Questions