+ Reply to Thread
Results 1 to 7 of 7

Copy by pattern?

  1. #1
    Registered User
    Join Date
    10-23-2011
    Location
    Sydney Australia
    MS-Off Ver
    Excel for Mac V16.39
    Posts
    45

    Copy by pattern?

    Hi, I need help to fill a column based with data from other cells in the sheet. The problem is that the data is in different columns.

    For example, If I have data in following four columns (12 cells), I need that data transposed to a single column.

    B1 C1 D1 E1
    B2 C2 D2 E2
    B3 C3 D3 E3

    column A needs to be filled in this order with the data from the following cells;

    B1
    C1
    D1
    E1
    B2
    C2
    D2
    E2
    B3
    C3
    D3
    E3

    I need something I can adapt for different tasks, some data tables have 4 columns, some have 10.
    I can't copy a normal formula as excel doesn't copy the pattern when I fill down. Some of my data tables have hundreds of rows so I can't do manually.

    Any help would be greatly appreciated.

    Brian

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: Copy by pattern?

    In cell A1 of Sheet1 where you want your data transposed into:

    Please Login or Register  to view this content.
    Sheet2!$A$1 is the upper left hand corner of the data square that you are starting with. (I assumed it was starting right in cell A1 of Sheet2, it might be somewhere else, give it the appropriate reference.)

    1 is the position of the row that you are starting the formula in, so it's "1" for "A1" in this example formula.

    4 is how many columns wide your data is; you will need to either change this manually, or possibly run this off another cell as a reference (and that cell could have, for example, the function =COUNTA(Sheet2!A1:AA1) in it or something).

    You will need to pull this down the same number of cells as there are cells in the target range. I would probably wrap an IF(check, do, "") or something.
    Last edited by ben_hensel; 04-18-2018 at 07:22 PM.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,519

    Re: Copy by pattern?

    If you want to avoid a volatile function, then you can use this in A1:

    =INDEX($B$1:$E$3,INT((ROWS($1:1)-1)/4)+1,MOD(ROWS($1:1)-1,4)+1)

    where the data is assumed to be in B1 to E3 of the same sheet. Change the items in red to accommodate more columns of data.

    Hope this helps.

    Pete

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,626

    Re: Copy by pattern?

    I need something I can adapt for different tasks, some data tables have 4 columns, some have 10.
    I can't copy a normal formula as excel doesn't copy the pattern when I fill down. Some of my data tables have hundreds of rows so I can't do manually.
    You could make a dynamic named range formula in Name Manager. These automatically size to fit the data.

    Then use a helper cell ... say B1 with this formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then you can replace the range and 4s in Pete's formula

    =INDEX($B$1:$E$3,INT((ROWS($1:1)-1)/4)+1,MOD(ROWS($1:1)-1,4)+1)

    with

    =INDEX(Named_Range,INT((ROWS($1:1)-1)/$B$1)+1,MOD(ROWS($1:1)-1,$B$1)+1)
    Dave

  5. #5
    Registered User
    Join Date
    10-23-2011
    Location
    Sydney Australia
    MS-Off Ver
    Excel for Mac V16.39
    Posts
    45

    Re: Copy by pattern?

    Ben, when I modified your formula for my data I broke it -sorry. I will have a play with it and see if I can understand what it is doing and hopefully learn a thing or two.
    Pete, your formula worked straight out of the box and I can mark this as solved. You have saved me hours of (unpaid) work.

    A big thank you to you both.

  6. #6
    Registered User
    Join Date
    10-23-2011
    Location
    Sydney Australia
    MS-Off Ver
    Excel for Mac V16.39
    Posts
    45

    Re: Copy by pattern?

    Thanks FlameRetired. I will use that tweak for sure.

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,626

    Re: Copy by pattern?

    You're welcome. Thanks for the feedback and please do mark as SOLVED.

+ 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. Copy Formula pattern
    By miqureshi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-19-2016, 10:23 AM
  2. [SOLVED] Lookup and return rows based on pattern start and pattern end
    By JDI in forum Excel General
    Replies: 18
    Last Post: 11-16-2014, 11:44 PM
  3. [SOLVED] Copy down a column pattern
    By CR78 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-12-2014, 04:55 PM
  4. Copy down hypertext cell range pattern & cell reference formula pattern
    By Underexcelling in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-10-2014, 03:23 AM
  5. [SOLVED] Excel 2007 : copy data in a pattern
    By gjmptw in forum Excel General
    Replies: 2
    Last Post: 03-26-2012, 02:25 AM
  6. copy Merge pattern
    By AnkitGuru in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-28-2012, 11:24 AM

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