Repeating cells

  • Thread starter Thread starter gary
  • Start date Start date
G

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
 
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
 
Back
Top