CODEWITHSUNDEEP
excelvba
Kavan
Kavan

Reputation: 331

Fill blank cells in Excel

I have .csv data like 

 ID | Weight
  A | 10 
  B |
  C | 30
  D | 40
  C | 
  A | 
  B | 20

And I want to fill the corresponding values of Weight from ID something like this

 ID | Weight
  A | 10 
  B | 20
  C | 30
  D | 40
  C | 30
  A | 10
  B | 20

My data is very huge, can someone please help me to do it?

Below is snapshot of my original data, left are the IDs and right are weights, I want to fill the blank spaces by the weights from the corresponding IDs which can be found by scrolling down. Data is VERY HUGE so I gotta find a fast way.

enter image description here

Upvotes: 0

Views: 223

Answers (3)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

Assuming all of the unique ID's have at least one weight, to fill in the rest you can use the following macro.

Please read the comments within the macro to help understand how it works.

We first make a list of all the unique ID's that have weights. We then use that list to populate the ID's that don't have weights.

Since the "work" is done within a VBA array; and the lookups are done using the Dictionary object, it should run quite rapidly, even on a large database.

And it won't make any difference how the original data is ordered.

In the code, I assumed your data was on Sheet1 in columns A & B. If that is not the case, you will need to change that part of the code.

Option Explicit
Sub Weights()
    Dim Dict As Object
    Dim V As Variant
    Dim WS As Worksheet
    Dim R As Range
    Dim I As Long

Set Dict = CreateObject("scripting.dictionary")
Set WS = Worksheets("sheet1") 'set to the sheet with the data
With WS
    Set R = .Range(.Cells(1, 1), .Cells(.Rows.Count, "A").End(xlUp)).Resize(columnsize:=2)
End With

'Read the original data into VBA array
V = R

'Collect the unique ID's into a dictionary
'   if and only if they are associated with a weight
'Key = ID; Value = Weight

For I = 2 To UBound(V, 1)
    If Not Dict.Exists(V(I, 1)) And _
        V(I, 2) <> "" Then _
            Dict.Add Key:=V(I, 1), Item:=V(I, 2)
Next I

'Fill in the blanks
For I = 2 To UBound(V, 1)
    If V(I, 2) = "" Then V(I, 2) = Dict(V(I, 1))
Next I

'Write the results back to the worksheet
R = V

End Sub

Upvotes: 2

pespi bop
pespi bop

Reputation: 1

Hopefully I'm not misunderstanding this. The ID of cells that are blank appear earlier in the column, and you want to fill in the blank cells with the weight corresponding to the ID in previous cells.

There are blank cells in the "Weight" column. For cell in the "Weight" column, it has an ID corresponding to it in the "ID" column. You want to find the blank cells in the "Weight" column, check it's ID, and look for appearances of the ID earlier in the column, and then retrieve the value of weight from it.

This macro fills all the blank cells in the Column B with a formula that searches for earlier appearances of corresponding ID, and then retrieves the weight.

Sub KavanWeightID()

    On Error Resume Next

    Columns("B").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=INDIRECT(""B""&MATCH(RC[-1],C[-1],0))"

End Sub

Upvotes: 0

Elliot Lowe
Elliot Lowe

Reputation: 1

If you quite literally just want to have the weight reflect the ID# * 10 you can go to the first cell under weight and in the formula tab put =A2*10 (In the example I've provided below). Then to apply to all of your data just grab the little box in the left corner of the cell you put the formula in and drag it down to all cells you want to apply the formula too!

Hope this helps!

Example of basic formula for excel

P.S.

If Weight is not ID*10 that's fine just place whatever equation you'd want for weight in and you can follow the same process.

Upvotes: 0

Related Questions