CODEWITHSUNDEEP
vbaexcel
user122508
user122508

Reputation: 15

How to replace blank cells in each row with a unique value

I've got an Excel file that looks like this:

 Person1,Person2,Person3,Person4,Median
 GeneA 1,0,2,,1
 GeneB 1,,2,1,1
 GeneC 2,,,0,0
 GeneD 0,1,1,2,1
 GeneE 0,0,0,,0

The 5th column is the median for each row. I would like to replace the missing data in each row with this median value.

Any ideas for how I could go about doing this? I've got over 160 columns and 60,000 rows of data.

Upvotes: 0

Views: 392

Answers (2)

DisplayName
DisplayName

Reputation: 13386

a VBA solution 

Sub main()
    With Intersect(ActiveSheet.UsedRange, Range("B:F"))
        .SpecialCells(XlCellType.xlCellTypeBlanks).FormulaR1C1 = "=RC6"
        .Value = .Value
    End With
End Sub

Upvotes: 1

ashleedawg
ashleedawg

Reputation: 21639

Here's an example of how one IF statement can be used to fill in the blanks:

animated screenshot example

Also, a note on your medians: perhaps these aren't the actual calculations, but at least one of your sample rows has an incorrect median. Excel has a built-in function to calculate MEDIAN (which you could even use instead of the IF statement, if filling in the blank is the only goal).

More Information:

Upvotes: 2

Related Questions