+ Reply to Thread
Results 1 to 14 of 14

copy/paste not consecutive cells

  1. #1
    Registered User
    Join Date
    04-26-2016
    Location
    Finland
    MS-Off Ver
    2010
    Posts
    7

    Question copy/paste not consecutive cells

    Hello all!
    I will try to explain in a clear way my problem. I have to copy and paste from one sheet to another a big number of cells not consecutive.

    For example: sheet 1, A1:A7000, I have to copy cells A1,A10,A20,A30... until 7000 from sheet 1 to sheet 2. In the sheet 2 those cells should be consecutive, therefore A1=A1, A10=A2 etc..
    I tried to use the offset function but it does not work because it starts from A1 and copy A10, but after it does not start from A10 and copy A20 but it starts from A2 and it copies A11.

    I hope I was clear enough!

    Thanks to all!

    Lina

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: copy/paste not consecutive cells

    Put this in Sheet2 A1 & Drag Down

    =INDEX(Sheet1!A:A,IF(ROWS($A$1:A1)*10-10=0,ROWS($A$1:A1)*10-9,ROWS($A$1:A1)*10-10))
    Cheers!
    Deep Dave

  3. #3
    Registered User
    Join Date
    04-26-2016
    Location
    Finland
    MS-Off Ver
    2010
    Posts
    7

    Re: copy/paste not consecutive cells

    Thanks a lot!
    If I should use this formula for different cells for example copy A8:A2000 to G3-G200?
    I tried but I was not able to solve the problem!

    Thanks again!

    Lina

  4. #4
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: copy/paste not consecutive cells

    Put this in G3 and drag down

    =INDEX(Sheet1!$A$8:$A$2000,IF(ROWS($G$3:G3)*10-10=0,ROWS($G$3:G3)*10-9,ROWS($G$3:G3)*10-10))

  5. #5
    Registered User
    Join Date
    04-26-2016
    Location
    Finland
    MS-Off Ver
    2010
    Posts
    7

    Re: copy/paste not consecutive cells

    Thanks again!
    Probably it is stupid question: but if the gap is not 10 but a odd number it will be ok anyway?

  6. #6
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: copy/paste not consecutive cells

    You might have to alter the formula a bit..

    Try it out, and just incase you run into trouble, I'll be happy to help..

  7. #7
    Registered User
    Join Date
    04-26-2016
    Location
    Finland
    MS-Off Ver
    2010
    Posts
    7

    Re: copy/paste not consecutive cells

    yes, I tried several times. The problem is that if it start from A1 the next is A11 and the next is A21 etc..
    Anyway I tried with gap 15 and 57 and the same problem.

    I tried also to read the meaning of your formula because I cannot do anything without really understand the meaning, but not good results! My fault I am sorry..

    thanks a lot for your help!

  8. #8
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: copy/paste not consecutive cells

    So are you still seeking help? Let me know if you still are.. Actually I am unable to understand what problem are you facing. If you could clarify further, I will try to help you out with your problem..

  9. #9
    Registered User
    Join Date
    04-26-2016
    Location
    Finland
    MS-Off Ver
    2010
    Posts
    7

    Re: copy/paste not consecutive cells

    yes I am still seeking help

    I will try to explain the problem: I tried to use your formula for different cells and I don't understand why it does not work. I used that
    =INDEX('List ; Plates 1 - 121'!$F$2:$F$7000,IF(ROWS($G$3:G4)*10-10=0,ROWS($G$3:G4)*10-9,ROWS($G$3:G4)*10-10)) and if I drug it down the first is F2 in the cell G3, the second is F11 in G3 and F21 in G4, it should be F2-F12-F22.
    Moreover for some cells the gap is an odd number that's why I was thinking if it would work anyway!

    Thanks a lot

  10. #10
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: copy/paste not consecutive cells

    Is it possible for you to attach a sample workbook with the most complex scenario according to you?

    I will design a robust formula that should work in all situations.

  11. #11
    Registered User
    Join Date
    04-26-2016
    Location
    Finland
    MS-Off Ver
    2010
    Posts
    7

    Re: copy/paste not consecutive cells

    Yes, here is the file
    Attached Files Attached Files

  12. #12
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: copy/paste not consecutive cells

    So should I be considering the B Column? Where the numbers are repeated 56 times?

    Also, I'd like to know why 56 times and why not any other number?

    Try this formula out in the mean time in Cell H2 and drag down so that you can compare with the B Column. It matches perfectly.

    =IF(AND(LEFT(C2,1)="A",LEFT(C1,1)<>"A"),H1+1,H1)
    Last edited by NeedForExcel; 04-26-2016 at 11:19 PM.

  13. #13
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: copy/paste not consecutive cells

    Duplicate Post: Sorry!!
    Last edited by NeedForExcel; 04-26-2016 at 11:19 PM.

  14. #14
    Registered User
    Join Date
    04-26-2016
    Location
    Finland
    MS-Off Ver
    2010
    Posts
    7

    Re: copy/paste not consecutive cells

    Hello! Sorry for my delay!
    I read your post already 2 days ago. I changed a bit the formula that you suggested me at first and it worked well!
    Anyway, I can try with the second one also!

    Thanks a lot for you help!

    Lina

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 12-01-2014, 12:16 PM
  2. [SOLVED] Copy consecutive cells to non-consecutive cells
    By EC3 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-11-2014, 07:49 PM
  3. Macro to copy and paste into consecutive rows
    By virsilens in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-05-2014, 12:20 PM
  4. [SOLVED] Problems with consecutive copy & paste EXCEL - VisualBasic - .txt
    By GioP in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-26-2013, 08:09 AM
  5. Copy and paste non consecutive cells and paste consecutively in another workbook
    By macquhele in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-12-2011, 02:36 PM
  6. Mass Consecutive Copy/Paste with One Macro
    By nohero in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-01-2011, 08:57 PM
  7. [SOLVED] How do I paste into 'consecutive' cells?
    By warren_ds in forum Excel General
    Replies: 3
    Last Post: 10-07-2005, 09:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1