2

I have a huge data sheet that is being updated every week. It has about 2,000 colums and 6 rows.

Every cell each week is new (Its a list of average prices, so the names of the items remain the same but the prices fluctuate.)

However, there are about 100 prices that have to be the same each week. I need to be able to copy and paste these values in without deleting the other values.

For example:

enter image description here

So when copy and pasting a chunk of the sheet with a few numbers scattered within it, the blank spaces do not replace existing values when pasted in.

2
  • it looks like it has de-formatted my example. fixed
    – Ben
    Jul 4 '15 at 19:38
  • You can put pre-formatted text by selecting it and pressing Ctrl-K.
    – user79865
    Jul 4 '15 at 19:47
2

There isn't an explicit "copy except blanks" command, but the following approach seems simple enough. For this illustration I assume the original data is in the rows 1-6, while the "scattered" data to be pasted there is in rows 7-12.

Put the following, for example, in A14:

=ARRAYFORMULA(IF(LEN(7:12),7:12,1:6))

This will fill the rows 14-19 with the data combined in the way you described. Then copy these rows and paste them to the top of the spreadsheet with CtrlShiftV (equivalently, right click -> paste special -> values only).

Explanation: the IF command takes values from rows 7:12 when they have positive length (i.e., are nonempty). Otherwise it takes them from rows 1:6.

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.