+ Reply to Thread
Results 1 to 7 of 7

Combine multiple columns of data into one column, where the number of columns may vary

  1. #1
    Registered User
    Join Date
    11-16-2015
    Location
    Florida
    MS-Off Ver
    xp
    Posts
    4

    Combine multiple columns of data into one column, where the number of columns may vary

    I've progressed a set of data through several conversions, but I'm having trouble with Conversion 4. Here is a link to the spreadsheet:
    https://docs.google.com/spreadsheets...it?usp=sharing

    What I'm trying to do for Conversion 4 in Sheet 1:
    In Cell A53, I'm looking to create an arrayformula that stacks B40:B45, C40:C45, and D40:D45 into one column, downwards from B53. And similarly to have the content of A40:45 be repeated, respectively, based on however many separate columns of names there will be (In this example, B, C, D represents that there are 3 columns of names. The outcome should look like the layout in "Sheet 2"

    However, there are (3) Three issues I'm facing with the formula currently in A53 :

    A)
    It doesn't seem to track the values (people's names) that I've manipulated previously through the other Conversions.

    B)
    If I have a space between "Prompt" and the #, it puts the # into the second column. I think this has something to do with the "Split" function causing this to happen.

    C)
    As you'll notice, the list keeps repeating itself downwards, but I'm not sure why this is happening. In this example, there should only be 3 blocks of data -- (as shown in "Sheet 2")

  2. #2
    Forum Moderator - RIP 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: Combine multiple columns of data into one column, where the number of columns may vary

    You may get more responses if you upload the workbook here.

    Many of us prefer not to use other file sharing sites.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    11-16-2015
    Location
    Florida
    MS-Off Ver
    xp
    Posts
    4

    Re: Combine multiple columns of data into one column, where the number of columns may vary

    Thanks for the suggestion, Richard!

    If anyone could help resolve this issues, please refer the attached spreadsheet file here:

  4. #4
    Forum Moderator - RIP 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: Combine multiple columns of data into one column, where the number of columns may vary

    Sorry but I don't understand.

    Do we assume that your current conversion 1, conversion 2 ...etc. stages are merely working areas to get you to the final Conversion 4 position.

    If so please explain how the A57:B115 values relate to the original data. i.e. explain in a narrative form how you get the numbers in colmn B and what the 'Prompt' text means in Column A. I'm puzzled that the conversion 4 seems to contain no reference to the original Prompt number.

  5. #5
    Registered User
    Join Date
    11-16-2015
    Location
    Florida
    MS-Off Ver
    xp
    Posts
    4

    Re: Combine multiple columns of data into one column, where the number of columns may vary

    Thanks for your interest in helping, Richard!

    RB: Do we assume that your current conversion 1, conversion 2 ...etc. stages are merely working areas to get you to the final Conversion 4 position.

    Yes. (Thanks for asking!) Ultimately, the final set of data I'm trying to manipulate here is Conversion 3. But for reference, I was feeling it might be necessary to show how I've converted the data previously is so that we can see where/how I am pulling the original set of values from.

    RB: If so please explain how the A57:B115 values relate to the original data. i.e. explain in a narrative form how you get the numbers in colmn B and what the 'Prompt' text means in Column A. I'm puzzled that the conversion 4 seems to contain no reference to the original Prompt number.

    My apologies... As you correctly pointed out, the spreadsheet I uploaded does not have any working formulas for Conversion 4. So perhaps, if you can ignore the values from A57:B115 and -- for that matter -- the three issues I listed at the top of this post. Perhaps the ultimate question I have here with this post is how I can convert the table of data displayed in "Conversion 3" to look like the set of data shown in "Sheet 2" of the spreadsheet.

    --> I'm really trying to create a Template-like program for future use. And I've been trying to figure out how to craft an ArrayFormula function that allows me to combine multiple columns of data, when the # of columns may vary, given "Conversion 3" as the basis. So for example, in this spreadsheet it shows three columns of Data (B40:B45, C40:C45, and D40:D45). However, for future uses, I may have many more columns (imagine the list extending to column ZZ, etc.). Also, just to clarify, the # of rows (40:45) will always stay the same.

    Any thoughts?

  6. #6
    Forum Moderator - RIP 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: Combine multiple columns of data into one column, where the number of columns may vary

    Hi,


    Assuming your starting data is the Conversion 3 stuff as you suggest, does the list starting in A13 help you?

  7. #7
    Registered User
    Join Date
    11-16-2015
    Location
    Florida
    MS-Off Ver
    xp
    Posts
    4

    Re: Combine multiple columns of data into one column, where the number of columns may vary

    Thanks, Richard.

    The look of A13:B30 (in Sheet 2) generally seems to be the direction I'm going for. However, the formula doesn't seem to automatically account for new columns of data being added. For example, if I start adding more names into column E4:E9, the new information doesn't seem to be accounted for in the new table (downwards from A31 and B31).

    Alternatively, for reference, I should mention that someone in the past had used this kind of Arrayformula (as follows), and the idea is that this single formula would have an output of 2 columns and in our case, it would be placed in cell A13 as an arrayformula>>>
    =query(ArrayFormula(iferror(regexextract(transpose(split(concatenate(transpose(A4:A9&" "&B4:9)&char(9)), char(9))), {"^(.+?)\s","\s(.+)$"}))), "where NOT (Col1 <>'' AND Col2='') ")

    <<<But I wasn't able to make it work completely.

+ 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. Combine Data in Multiple Columns to One Column Based on Data in Rows
    By exceldivx in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-09-2014, 07:35 AM
  2. Combine multiple columns of data into one column
    By PMBottas in forum Excel General
    Replies: 8
    Last Post: 08-12-2014, 11:10 PM
  3. Replies: 3
    Last Post: 09-03-2013, 11:53 AM
  4. Combine multiple columns of data into one column???
    By seedman76 in forum Excel General
    Replies: 4
    Last Post: 03-07-2013, 02:20 AM
  5. Replies: 4
    Last Post: 01-12-2012, 12:30 AM
  6. Replies: 8
    Last Post: 02-18-2010, 05:14 AM
  7. Replies: 0
    Last Post: 07-31-2006, 12:13 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