Results 1 to 5 of 5

Insert repeating data into a column

This is a discussion on Insert repeating data into a column within the Excel Questions forums, part of the Question Forums category; If there is a column of 500 rows and each cell in the column has a single word, which does ...

  1. #1
    Board Regular
    Join Date
    Dec 2002
    Posts
    86

    Default Insert repeating data into a column

    If there is a column of 500 rows and each cell in the column has a single word, which does not repeat in the column; now my goal is to insert in every other cell a repeating digit-e.g. 4

    so as an illustration:

    start with a column 500 rows

    word1
    word2
    word3
    word4
    word5
    .
    .
    .
    .
    word 499
    word500


    and the goal after the insertion is to get to:

    word1
    4
    word2
    4
    word3
    4
    word4
    4
    word5
    ...

    word499
    4
    word500

    How can this be done (besides manually)?

    Thanks

  2. #2
    acw
    acw is offline
    MrExcel MVP
    Join Date
    Feb 2004
    Posts
    4,814

    Default Re: Insert repeating data into a column

    Hi

    Try this. You don't say what sort of data you have to the right so whether you want to insert an entire row, or just move the current data down will have to be adjusted.

    Tony

    Sub eee()
    Range("a2").Select
    While Not IsEmpty(ActiveCell)
    If Left(ActiveCell, 4) = "word" Then
    ActiveCell.Insert shift:=xlDown
    ActiveCell = 4
    ActiveCell.Offset(2, 0).Select
    End If
    Wend
    End Sub

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Insert repeating data into a column

    Hi justabc:

    Let us have a look at the following ...

    ******** ******************** ************************************************************************>
    Microsoft Excel - y040308h1.xls___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    sourceentryToAddAfterEveryWordupdated**********
    2
    word14word1*
    3
    word2*4*
    4
    word3*word2*
    5
    word4*4*
    6
    word5*word3*
    7
    **4*
    8
    **word4*
    9
    **4*
    10
    **word5*
    11
    **4*
    Sheet8*

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Formula in cell C2 is ... =IF(MOD(ROWS($1:1),2)=0,$B$2,INDEX($A$2:$A$6,INT((ROWS($1:1)-1)/2)+1))

    and this is then copied down as far as required. You may need to enhance the formula for more features -- but this should get you started.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  4. #4
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    Assuming data is in column A

    Code:
    Sub test()
    
        Range("A1").Select
        Do
        ActiveCell.Offset(1, 0).Select
        ActiveCell.EntireRow.Insert
        ActiveCell.Value = 4
        ActiveCell.Offset(1, 0).Select
        x = x + 1
        Loop While x < 500
    
    End Sub

  5. #5
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    Assuming data is in column A

    Code:
    Sub test()
    
        Range("A1").Select
        Do
        ActiveCell.Offset(1, 0).Select
        ActiveCell.EntireRow.Insert
        ActiveCell.Value = 4
        ActiveCell.Offset(1, 0).Select
        x = x + 1
        Loop While x < 500
    
    End Sub

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com