+ Reply to Thread
Results 1 to 9 of 9

Copying every 3rd cell across.

  1. #1
    Registered User
    Join Date
    02-08-2011
    Location
    Tokyo
    MS-Off Ver
    Office 365
    Posts
    9

    Copying every 3rd cell across.

    Hi Richard,

    Thank you for getting back to me. I have re-uploaded the spreadsheet with a few pointers to explain (see attached) , hopefully its bit more easier to understand. Looking at the spreadsheet I want those highlighted in red (every 3rd cell, colmun b) under the before banner to be copied across from info column to date column a. I have duplicated the data entries under before, then the same data is pasted in after just show its the same data and I just need to extract every 3rd cell in column e to column d, contents only , but I have manually copied the data and placed in where I need it, in the date column as a example. Hopefully the arrows help.

    Let me know if I articulated this properly

    Thanks
    Attached Files Attached Files
    Last edited by foxhound82; 03-15-2022 at 12:43 PM.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Copying every 3rd cell across.

    Sorry but I don't understand what you mean at all.

    Please show a before and after situation and clearly explain how you get from one to the other. i.e. the steps and rules you have followed.
    At the moment I just don't know what you started with.

  3. #3
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,686

    Re: Copying every 3rd cell across.

    If I've understood correctly (and I'm not sure I have) enter the formula =B5 into cell A3, then select cells A3:A5 and drag the fill handle (little black box at the bottom right of the selected cells) down as required.

    If that's not what you mean, follow Mr Buttrey's advise above.

    BSB

  4. #4
    Registered User
    Join Date
    02-08-2011
    Location
    Tokyo
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Copying every 3rd cell across.

    I have posted a updated message above guys.

    Thanks

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,261

    Re: Copying every 3rd cell across.

    Try

    in D4

    =IF(MOD(ROWS($1:1-1),3)=1,INDEX($B$4:$B$18,INT((ROWS($1:1)-1)/3)*3+3),"")

    in E4

    =IF(MOD(ROWS($1:1-1),3)<>0,INDEX($B$4:$B$18,ROWS($1:1)),"")

    copy down

    I am sure there is better 365 solution!
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  6. #6
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,686

    Re: Copying every 3rd cell across.

    If VBA is an option for you then you could try this:
    Please Login or Register  to view this content.
    Click the button at the top of column B in the attached.

    BSB

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Copying every 3rd cell across.

    Maybe a dynamic array:

    =LET(d,B6:B19,i,B4:B17,dr,IF(LEFT(d,7)="Updated",d,""),ir,IF(LEFT(i,7)<>"Updated",i,""),CHOOSE({1,2},dr,ir))
    Attached Files Attached Files
    Glenn



  8. #8
    Registered User
    Join Date
    02-08-2011
    Location
    Tokyo
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Copying every 3rd cell across.

    Hi all,

    Thank you all guys for replying, I have tried to go with the solution that is simple for me to understand and work with. Glen I have tried to use your array you kindly created to resolve the problem i am looking at , however I am kind struggling to make it work on the actual spreadsheet I am using.
    I cannot figure how I amend the formula to reference the layout how it is on the spreadsheet I am working on.
    So I have made a new example that is very close to the actual spreadsheet, please see attached look at tab 2 labelled example 2.
    The example shows every 3rd cell in column B for example Updated :13/03/2022 moved to cell A1, column a. If you can help , how does the array you wrote,
    be edited for pasting the date into cell A1 every 3rd throw from column B?

    Hope that makes sense.

    Thanks in advance, getting closer!

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,256

    Re: Copying every 3rd cell across.

    It seems that you would like cell B3 to display: updated: 14.03.22 and cell B6 to display: Updated:*14 March 2022.
    Note that if you were to put a formula into cell B1 and drag down it would erase the text in cells B1:B2, B4:B5 etc.
    For Example 2, cell B3 could simply be populated using =A1, now select cell B3 and press the Ctrl + c keys.
    Now you can select cell B6 and press the Ctrl + v keys which would automatically paste =A4 into cell B6.
    That said a VBA solution like BSB's may be a better choice, although I know so little about VBA I may be mistaken.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. [SOLVED] Copying formula result to new cell, without copying formula itself
    By nappyjim1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-19-2016, 02:24 AM
  2. Replies: 5
    Last Post: 08-22-2015, 09:43 AM
  3. Replies: 1
    Last Post: 02-03-2015, 06:44 AM
  4. Replies: 8
    Last Post: 05-17-2014, 02:33 PM
  5. Replies: 3
    Last Post: 10-30-2013, 02:05 PM
  6. [SOLVED] Copying today's date into cell when multiple data is entered into adjacent cell
    By SChapman in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 04-16-2013, 04:12 PM
  7. Copying Button when copying cell
    By Einstahb in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-28-2011, 02:55 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