update 2nd row in 1st row in excel

Question

I wanted to update contact in the same row. Both rows have the same email account, first row has no contact, second row has contact. How do I update the contact in first row.

Updated problem I used the suggested solution. But when there is not all the account has 2 rows. It will get the wrong contact. Just like ccc@gmail.com

Answer 1

Follow these steps:

• select column D
• hit F5 or Ctrl+G to open the Go To dialog
• Click Special
• Tick Blanks and hit OK. Now all blank cells are selected.
• without changing the selection, type a = character and hit the down arrow key. That will enter a formula that refers to the cell below.
• hold down the Ctrl key and hit Enter. Now all selected cells contain a formula that refers to the cell below.
• Select and copy column D, then paste as values.

Answer 2

You can use another column with the formula below. Drag it down to fill the other cells.

=IF(AND(ISBLANK($D2),$C2=$C3),$D3,$D2)

Note that if a contact number is blank, it will appear as a 0.