• 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...

  • 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

L

learneagerly

New Member
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,
 
Hi !

At beginner level :​
Code: 
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 !
​
 
@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).
 
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.
 

Attachments

  • test.xlsm
    18.9 KB · Views: 7
learneagerly said:
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.
Click to expand...
My code just gives same exact result as your expected output
within your initial presentation ‼

Monty, it's up to you !
​
 
learneagerly said:
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.
Click to expand...

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.
 
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: 
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 !
​
 
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.
 
You must log in or register to reply here.
Back
Top