How to autofill values in excel based on the cells above and below

Question

good people, I have a table full of unique id's (as mentioned below) but there are missing values for few id's.

I want a formula/code to fill in the blanks based on the values above and below of any cell.

For example: here there are two empty rows between two unique id~(620006845180).

can anyone help me with filling up these spaces with the same value as above and below. :( I am stuck.

    620006845180
    xxxxxxxxxxxx
    xxxxxxxxxxxx
    620006845180
    620006845180

@pnuts Yes..Mostly. In few rows, there are different values but for the cells where there are same values, it should be same. — Subhra Sankha Sardar, Jul 08 '17 at 16:00
For example : 658697890 xxxxxxxxxx 658697890 ~ this xxxxxxx should be 658697890. — Subhra Sankha Sardar, Jul 08 '17 at 16:04
Do you always want to fill the empty space with the value from above, or is there a case where it should be from the value below instead? — luckyguy73, Jul 08 '17 at 16:13
Only the cases where there are same values in above and below the empty cells. @AshtonMorgan — Subhra Sankha Sardar, Jul 08 '17 at 16:25
So what should happen if the value above and below are different? — luckyguy73, Jul 08 '17 at 16:27
@AShtonMorgan I will keep those cells blank when there are different values. But when there are same values above and below the empty rows should be filled with the exact same value. Will you help? — Subhra Sankha Sardar, Jul 08 '17 at 16:40

score 1 · Accepted Answer · answered Jul 08 '17 at 17:26

1

ok, sure. this assumes the id's are in column A

Sub FillBlankCells() 

Dim lastRow As Long: lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Dim IDRange As Range: Set IDRange = Range("A1:A" & lastRow)
Dim rng As Range

For Each rng In IDRange
  If rng.Value = "" Then
    If rng.End(xlUp).Value = rng.End(xlDown).Value Then _
    rng.Value = rng.End(xlUp).Value
  End If
Next rng

End Sub

answered Jul 08 '17 at 17:26

luckyguy73

1,850
2
11
21

Works like a charm. Thank you soo much :') – Subhra Sankha Sardar Jul 08 '17 at 18:03

Karpak · Answer 2 · 2017-07-08T18:01:32.393

1

You cannot do this in a same column because of circular reference. But you can use another column to do it.

I assume that you have these numbers in column A and from row 2 and you insert an empty column in B and you enter the following formula in B2. Then copy the rest of the rows of the column B from the cell B2. Then Hide your column A, you will get what you wanted in column B.

=IF(A2<>"",A2,IFERROR(IF(MATCH(B1,A3:A100,0)>0,B1,""),""))

If you feel there could more than 100 empty rows. Then you may need to replace the 100 found in the formula to what is the maximum number of rows that you expect to be empty.

edited Jul 08 '17 at 18:01

answered Jul 08 '17 at 17:55

Karpak

1,927
1
15
16

There are 30 lacs empty rows, and total no. of rows is 88 lacs. A great suggestion. Thank you :) – Subhra Sankha Sardar Jul 08 '17 at 18:13
if continuously 30 lacs empty rows between two numbers, then macro would be better solution. – Karpak Jul 09 '17 at 02:41

How to autofill values in excel based on the cells above and below

2 Answers2