Repeating cells

[gary]

How do I repeat the stuff in column A when there's stuff in Col B?
My spreadsheet looks like this:

A B
000771605-0 1976 0000000

000771607-2 1976 0000000
0284831

000800014-5 2009 0000000
0681378
0681864
0682617

000804444-2 1981 0000000
0284831

000804444-2 1983 0000000
0284831

000804448-6 1980 0000000

000804448-6 1982 0000000
0681804

000812407-4 2006 0000000
0681865
0682683
0682694
0682695
0682697
0684836

000812415-1 2003 0000000
0681865
0682683
0682694
0682695
0682697
0684836

======================================

Here's what I need:

A B
000771605-0 1976 0000000

000771607-2 1976 0000000
000771607-2 1976 0284831

000800014-5 2009 0000000
000800014-5 2009 0681378
000800014-5 2009 0681864
000800014-5 2009 0682617

000804444-2 1981 0000000
000804444-2 1981 0284831

000804444-2 1983 0000000
000804444-2 1983 0284831

000804448-6 1980 0000000

000804448-6 1982 0000000
000804448-6 1982 0681804

000812407-4 2006 0000000
000812407-4 2006 0681865
000812407-4 2006 0682683
000812407-4 2006 0682694
000812407-4 2006 0682695
000812407-4 2006 0682697
000812407-4 2006 0684836

000812415-1 2003 0000000
000812415-1 2003 0681865
000812415-1 2003 0682683
000812415-1 2003 0682694
000812415-1 2003 0682695
000812415-1 2003 0682697
000812415-1 2003 0684836

Thank you in advance,

GaryWC

 

[Guest]

Hello Gary,

is this what you are looking for


Sub adapt()
Dim rng As Range
Dim lLastRow As Integer
Dim lLastCol As Integer
Dim i As Integer
'Now we get the dimensions of the data
Set rng =
Worksheets("sheet2").Range("a1").SpecialCells(xlCellTypeLastCell)
lLastRow = rng.Row
lLastCol = rng.Column
'Now for the loop
For i = 2 To lLastRow
'find cell wit less than 7 positions in col a and copy to col b
If Len(Cells(i, "a").Value) < 7 Then
Range("A" & i).Select
Application.CutCopyMode = False
Selection.Copy
Range("b" & i).Select
ActiveSheet.Paste
End If
'find cell wit less than 7 positions in col a
'when content col a = col b
'copy content col a one row less to actual row col a
If Len(Cells(i, "a").Value) < 7 And Range("A" & i).Value = Range("b" &
i).Value Then
Range("A" & i - 1).Select
Application.CutCopyMode = False
Selection.Copy
Range("a" & i).Select
ActiveSheet.Paste
End If
Next i
'adapt format cells in col b
Columns("B:B").Select
Selection.NumberFormat = "0000000"
End Sub

"gary" schreef in bericht

How do I repeat the stuff in column A when there's stuff in Col B?
My spreadsheet looks like this:

A B
000771605-0 1976 0000000

000771607-2 1976 0000000
0284831

000800014-5 2009 0000000
0681378
0681864
0682617

000804444-2 1981 0000000
0284831

000804444-2 1983 0000000
0284831

000804448-6 1980 0000000

000804448-6 1982 0000000
0681804

000812407-4 2006 0000000
0681865
0682683
0682694
0682695
0682697
0684836

000812415-1 2003 0000000
0681865
0682683
0682694
0682695
0682697
0684836

======================================

Here's what I need:

A B
000771605-0 1976 0000000

000771607-2 1976 0000000
000771607-2 1976 0284831

000800014-5 2009 0000000
000800014-5 2009 0681378
000800014-5 2009 0681864
000800014-5 2009 0682617

000804444-2 1981 0000000
000804444-2 1981 0284831

000804444-2 1983 0000000
000804444-2 1983 0284831

000804448-6 1980 0000000

000804448-6 1982 0000000
000804448-6 1982 0681804

000812407-4 2006 0000000
000812407-4 2006 0681865
000812407-4 2006 0682683
000812407-4 2006 0682694
000812407-4 2006 0682695
000812407-4 2006 0682697
000812407-4 2006 0684836

000812415-1 2003 0000000
000812415-1 2003 0681865
000812415-1 2003 0682683
000812415-1 2003 0682694
000812415-1 2003 0682695
000812415-1 2003 0682697
000812415-1 2003 0684836

Thank you in advance,

GaryWC