1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy paste 6(n) cells from 2 different columns in a third column

Discussion in 'VBA Macros' started by learneagerly, Mar 14, 2017.

  1. learneagerly

    learneagerly New Member

    Messages:
    3
    Hi ,
    My input is as follows:
    Column A Column B
    1 51
    2 52
    3 53
    4 54
    5 55
    6 56
    7 57
    8 58
    9 59
    10 60
    11 61
    12 62
    13 63
    14 64
    15 65
    16 66
    17 67
    18 68
    19 69
    20 70

    And expected ouput is :
    Column D
    1
    2
    3
    4
    5
    6
    51
    52
    53
    54
    55
    56
    7
    8
    9
    10
    11
    12
    57
    58
    59
    60
    61
    62
    13
    14
    15
    16
    17
    18
    63
    64
    65
    66
    67
    68

    This number 6 can change time to time from 2 to 25.

    Can anybody please help me in this. Expecting to automate this with VBA.

    Thanks & Regards,
  2. Monty

    Monty Well-Known Member

    Messages:
    635
    Hello
    Welcome to forum.
    Can you please upload file to help you.
    jamesexcel1970 likes this.
  3. Marc L

    Marc L Excel Ninja

    Messages:
    2,916
    Hi !

    At beginner level :​
    Code (vb):
    Sub Demo()
            Const C = 4, N = 6
        With ActiveSheet.UsedRange.Columns
            If .Count >= C Then .Item(C).Clear
        End With
            L& = 1
            R& = 1
        For T& = 1 To Cells(Rows.Count, 1).End(xlUp).Row \ N
            Cells(R, 1).Resize(N).Copy Cells(L, C)
            L = L + N
            Cells(R, 2).Resize(N).Copy Cells(L, C)
            L = L + N
            R = R + N
        Next
    End Sub
    Do you like it ? So thanks to click on bottom right Like !
  4. r2c2

    r2c2 Member

    Messages:
    67
    @learneagerly Is there a reason why you need to use VBA for this? You could get the desired result with an index formula.

    For example, this formula in column D gives the result you need.

    =INDEX(data,MOD((ROWS($E$3:E3)-1)/n,1)*n+1 + INT((ROWS($E$3:E3)-1)/2/n)*n, IF(MOD(ROWS($E$3:E3)-1,2*n)>=n,2,1))

    Assuming data refers to your 2 column data range and n refers to the number 6 (or whatever else you want).
  5. learneagerly

    learneagerly New Member

    Messages:
    3
    Hi guys,
    Thanks a lot for the prompt replies.
    Let me make my question more clear as suggested by Monty.

    I tried both the solutions.
    Marc L, the code works fine till the exact multiple of my required number 6 in this case but the remaining numbers are not really considered. I have given in Example sheet of attched file. I need to run the code till thelast empty cell in column 1 & 2 both. Tried solution in sheet "Marc L"

    r2c2, I wouldn't mind the if I can get the expected end result. In the case of using the above formula as attached in column G of sheet " r2C2"; it shows 0,0,0,0 for the remaining cells which are beyond the exact multiples of my current required number 6.

    Thanks once again for the replies. It would be really helpful if you can give me exact solution for my Example in attached file.

    Attached Files:

  6. Marc L

    Marc L Excel Ninja

    Messages:
    2,916
    My code just gives same exact result as your expected output
    within your initial presentation ‼

    Monty, it's up to you !
  7. r2c2

    r2c2 Member

    Messages:
    67
    You used wrong references in the formula. The reference to your data must be absolute (so it doesn't change when you drag the formula down).

    use this formula in D1.

    =INDEX($A$1:$B$20,MOD((ROWS($E$3:E3)-1)/6,1)*6+1 + INT((ROWS($E$3:E3)-1)/2/6)*6, IF(MOD(ROWS($E$3:E3)-1,2*6)>=6,2,1))

    and copy down.

    Please note that, while we are all happy to help here, you must do some homework before coming back for more help.
  8. r2c2

    r2c2 Member

    Messages:
    67
  9. Marc L

    Marc L Excel Ninja

    Messages:
    2,916
    What a nice blog article !

    As my name appears in this article and without news from Monty,
    this is the VBA way according to post #5 attachment :​
    Code (vb):
    Sub Demo2()
            Const C = 4, N = 6
            Cells(C).CurrentRegion.Offset(1).Clear
        For R& = 2 To Cells(1).CurrentRegion.Rows.Count Step N
            Cells(R, 1).Resize(N).Copy Cells(Rows.Count, C).End(xlUp)(2)
            Cells(R, 2).Resize(N).Copy Cells(Rows.Count, C).End(xlUp)(2)
        Next
    End Sub
    Do you like it ? So thanks to click on bottom right Like !
    learneagerly and Monty like this.
  10. Monty

    Monty Well-Known Member

    Messages:
    635
    Sorry Marc..
    It's been a week on mobile..As laptop for repair..Missing forum..
    jamesexcel1970 likes this.
  11. Marc L

    Marc L Excel Ninja

    Messages:
    2,916

    Don't be sorry except for your laptop !

    It's usual some people can't be everyday on forum …
    jamesexcel1970 likes this.
  12. Monty

    Monty Well-Known Member

    Messages:
    635
    Marc.

    Me 3hrs dedicated for this forum.. Every day....But from one week missing something.
    jamesexcel1970 likes this.
  13. learneagerly

    learneagerly New Member

    Messages:
    3
    Hi,
    Thanks a lot!!
    Please bare with me for the mistakes I do.
    Yes I agree with r2c2 about the homework. but the lack of knowledge is the cause. Hope I'll now learn fast with very helpful guys like you & Marc and others on this site.
    r2c2 , I used A:B in column "G" of the example since the number of entries may very every time, so thought of keeping it general instead of absolute reference. Thanks any ways !!! the solution works if I select the absolute reference.
    Marc, Thanks a lot!! the demo2 works exactly as my requirement.
    Lesson learn for me:
    Define the requirement correctly with an example if possible so that will get proper help.
    & try to do some more homework. hope to get better in this as I start working more on complex issues.
    Thanks again for the immediate help. I will definitely be on this site regularly.
    Marc L likes this.

Share This Page