+ Reply to Thread
Results 1 to 6 of 6

Split Data From Single Column Set Into Multiple Column Sets?

  1. #1
    Registered User
    Join Date
    05-27-2013
    Location
    Washington, DC
    MS-Off Ver
    Excel 2016
    Posts
    15

    Split Data From Single Column Set Into Multiple Column Sets?

    I have about 1,000 rows of data organized into 3 columns (which I'll call a column set):

    PHASE CYCLE GREEN
    1 3 4
    1 2 9
    2 3 7
    3 5 6
    1 4 3
    5 9 2
    3 7 3
    2 4 3

    I need to take this single column set and parse it into multiple column sets, based on the value in the first column (PHASE). That is, all the rows where Phase = 1 go together in one column set, all Phase = 2 go in a second column set, etc.

    I am extremely bad at VBA, and I'm not really sure how to even get started with this...can anyone help me?

    Also, this question is posted here as well: http://www.ozgrid.com/forum/showthre...376#post732376

  2. #2
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Split Data From Single Column Set Into Multiple Column Sets?

    Update:= I don't think the code below is what you want.
    Looking at your question again I think you want each unique Number in "A" to have its own set of 2 columns to show results from "B & C", is that correct ??????, Please post expected results or attach file.

    Try this:-
    NB:= This code will delete the unwanted rows.
    Please Login or Register  to view this content.
    Regards Mick
    Last edited by MickG; 11-24-2014 at 12:24 PM.

  3. #3
    Registered User
    Join Date
    11-08-2014
    Location
    Malaysia
    MS-Off Ver
    2010
    Posts
    84

    Re: Split Data From Single Column Set Into Multiple Column Sets?

    Hi, I've posted some questions in this forum before. If you could make a sample output and attach an image (*.png) to your question, others can help you easier & sooner Good luck.

  4. #4
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: Split Data From Single Column Set Into Multiple Column Sets?

    Hi,

    Assuming your data starts in cell A1, this utilisation of autofilter should do as you require.

    Please Login or Register  to view this content.
    Last edited by sweep; 11-24-2014 at 12:16 PM.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  5. #5
    Registered User
    Join Date
    05-27-2013
    Location
    Washington, DC
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Split Data From Single Column Set Into Multiple Column Sets?

    Thank you for the quick replies, everyone! Sweep, your code works perfectly for what I need!

    One additional question, though: say the original column set is coming into excel via odbc from a PostgreSQL database. Then, Excel automatically formats it as a Table. When I try to run the macro on this table, weird stuff happens...all of the values in the "Phase" column become unselected, so the table essentially disappears, and only the first two unique values in "Phase" are parsed as separate column sets.

    Do you know how I would modify this code to work with a Table, or is it easier to just copy the data out of the table and paste it in Excel?

  6. #6
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: Split Data From Single Column Set Into Multiple Column Sets?

    You could convert back from a list to a standard range:

    This assumes that the data you import is the only table on the sheet:

    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. split single spreadsheet into multiple workbooks based on value in column D
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-22-2014, 07:27 PM
  2. [SOLVED] Arbitrary points: Split single column into multiple columns
    By ChasingShadows in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-23-2012, 11:08 PM
  3. Replies: 10
    Last Post: 07-22-2012, 07:32 PM
  4. Replies: 5
    Last Post: 05-10-2010, 09:49 AM
  5. Want to split the data in single cell to multiple column
    By pradeepdeepu_001 in forum Excel General
    Replies: 4
    Last Post: 02-02-2010, 09:11 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