Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a list with three columns A, B, and C. The first two columns are complete (A and B), while the third (C) has many blanked fields.

What I want to do is replace all the blank fields in Column C with the same value form cell A in the same row.

The List contains over 2000 records, of which 65% have a blank Column C value, so I would like to use a formula/function.

Below is an example of what I have and what I want to do (on a much smaller scale)

enter image description here

share|improve this question
    
What about this: =IF(TRIM(C1) <> "", C1, A1) –  CHarmon Jun 13 '12 at 15:34
    
Both have resulted in a value of 0 so far –  Graeme Hutchison Jun 13 '12 at 15:50

2 Answers 2

up vote 6 down vote accepted

Use this trick to fill in the blank cells in Column C.

  1. Select Column C in your table (e.g. C1:C2000).
  2. Press F5. Click the 'Special...' button.
  3. Choose 'Blanks' and click 'OK'.
  4. Type =A2 (I'm assuming the first blank is in row 2, as in your example. It would be =A3 if the first blank were in row 3, etc.). Press Ctrl+Enter.

That should do the trick.

share|improve this answer
1  
That did the trick, although it was easier to just use Sort and Filter, to chose only the blanks. –  Graeme Hutchison Jun 13 '12 at 16:07

Another solutions could be:

  1. (only if you need the orginal order) add a temporary column with value from 1 to last before
  2. order your dataset by column C (A to Z or Z to A is not important)
  3. then tou can easy create two different formulas (D2=A2 if C blanked are up or D2=C2 if C blanked are down)
  4. (only if you need the orginal order) reorder the dataset by temporary column created in step 1
  5. (only if you need the orginal order) delete the temporary column
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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