Add missing entries in column

[gavinj2006]

I have a sheet of order exports to be reimported to an excommerce system, the new system requires that for both lines of the order (order and despatch) have an email address but the export has some (many) missing.

I need to select the column and have Excel add the missing emails where appropriate.

The example containes an illustration of the sheet, the 2 detail lines are the order and despatch details and the empty rows below are details of the specific products ordered.

In the example, cells B8 and B30 needs to have the email address from cell B7 and B29, the rest are correct.

Any help would be much appreciated, thanks

Book1
	A	B	C
1	info	email
2	detail	a@a.com
3	detail	a@a.com
4
5
6
7	detail	a@a.com
8	detail
9
10
11
12
13
14
15
16	detail	a@a.com
17	detail	a@a.com
18
19
20
21
22	detail	a@a.com
23	detail	a@a.com
24
25	detail	a@a.com
26	detail	a@a.com
27
28
29	detail	a@a.com
30	detail
31
32
33	detail	a@a.com
34	detail	a@a.com
35
Sheet1

 

[Ezguy4u]

gavin Let's get the ball rolling. Here is my solution to your problem. Now this means you have to know a little about programming or VBA. If you do then fine. If you don't then we can help you work this. We have to start somewhere so let the question and discussion begin.

Sub Prog1()
Dim CellCnt As Integer

CellCnt = Cells(Rows.Count, "A").End(xlUp).Row

For i = CellCnt To 2 Step -1

If Cells(i, 1) <> "" And Cells(i, 2) = "" Then

Cells(i, 2).FormulaR1C1 = "=r[-1]c2"

End If

Next i

End Sub

23-08-11.xlsm
	A	B
1	info	email
2	detail	a@a.com
3	detail
4
5
6
7	detail	a@a.com
8	detail
9
10
11
12
13
14
15
16	detail	a@a.com
17	detail	a@a.com
18
19
20
21
22	detail	a@a.com
23	detail
24
25	detail	a@a.com
26	detail	a@a.com
27
28
29	detail	a@a.com
30	detail
31
32
33	detail	a@a.com
34	detail	a@a.com
Data

 

[gavinj2006]

That is absolutely perfect, thank you for your help, just saved me days of skimming through spreadsheets!

 

[Ezguy4u]

I also want to congratulate you for keeping your problem simple and using XL2BB. I am able to help when this is the case.

 

[Peter_SSs]

An alternative would be to populate them all at once rather than loop through a row at a time.

Sub Add_emails()
  With Range("B3:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .SpecialCells(xlBlanks).FormulaR1C1 = "=IF(RC[-1]="""","""",R[-1]C)"
    .Value = .Value
  End With
End Sub

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Ezguy4u]

I am going to save Peter's solution in my toolbox for future reference. I really like it.

 

[gavinj2006]

An alternative would be to populate them all at once rather than loop through a row at a time.

Sub Add_emails()
  With Range("B3:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .SpecialCells(xlBlanks).FormulaR1C1 = "=IF(RC[-1]="""","""",R[-1]C)"
    .Value = .Value
  End With
End Sub

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks for that, super slick solution

I updated as suggested

 

[Peter_SSs]

Thanks for that, super slick solution

You are welcome. Thanks for the follow-up.

I updated as suggested

.. and thanks for updating your details.