+ Reply to Thread
Results 1 to 4 of 4

Copy 4 columns into 1

  1. #1
    Registered User
    Join Date
    11-14-2014
    Location
    Baltimore
    MS-Off Ver
    2016
    Posts
    3

    Copy 4 columns into 1

    I'm trying to copy data from a number of columns into one column.

    before.png

    the pic above is what the data looks like currently. And the pic below is what I'm trying to do.

    After.png
    Last edited by 2wheeltraf; 02-18-2020 at 02:06 PM.

  2. #2
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: Copy 4 columns into 1

    Insert a new column A.
    In At put this formula:

    =OFFSET($B$1, MOD(ROW()-ROW($A$1),ROWS($B$1:$B$5)), TRUNC((ROW()-ROW($A$1))/ROWS($B$1:$B$5)),1,1)

    the red text is the amount of rows update accordingly.

    Drag down the formula so all data is included. Copy and paste special values in column A. Then highlight column A and select Go to Special... from the Find & Select menu on the Home ribbon.
    Select Constants and only have "Numbers" checked. Then hit OK.
    This should highlight all the 0's as everything else you have listed is text technically. With all 0's highlighted delete cells and shift up.
    <----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.

  3. #3
    Forum Contributor
    Join Date
    12-15-2019
    Location
    Not here
    MS-Off Ver
    2010
    Posts
    257

    Re: Copy 4 columns into 1

    Maybe try assigning name to range called MyData then use the formula and drag down:

    =INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1)
    This user is no longer subscribed to this thread.

  4. #4
    Registered User
    Join Date
    11-14-2014
    Location
    Baltimore
    MS-Off Ver
    2016
    Posts
    3

    Re: Copy 4 columns into 1

    thank you for your help. I ended up finding the macro below.

    Please Login or Register  to view this content.

+ 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] Copy one column, but loop through a second column multiple times depending on first column
    By redapplesonly in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-08-2019, 10:12 AM
  2. [SOLVED] Loop through Column B msgboxYesNo if Yes copy to Column C, if No copy to Column D
    By WBravard in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-09-2017, 09:46 AM
  3. Copy data from sheet1 column with dynamic date, and copy data to second sheet column
    By Renaliiii in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-25-2016, 04:44 PM
  4. Replies: 1
    Last Post: 05-30-2015, 04:38 PM
  5. Replies: 5
    Last Post: 11-17-2014, 02:40 PM
  6. Vba - find next empty column, insert column, copy paste values & copy paste formulas
    By DoodlesMama in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-20-2012, 12:43 PM

Tags for this Thread

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