+ Reply to Thread
Results 1 to 11 of 11

Sort multiple columns to one column, skip blanks

  1. #1
    Registered User
    Join Date
    08-28-2012
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    11

    Sort multiple columns to one column, skip blanks

    Please see sample below. I have three separate data lists in different columns, and each may have up to 30 names with corresponding number values, or may have no names at all. I want to be able to generate a list in a fourth column that will show all values in the order they originally appeared with their corresponding values to the cells to their right. I have no experience with macros that just run automatically and continuously--if someone could let me know how to set that up that would be much appreciated!

    Monkey 5 Mouse 8 Dog 4 Monkey 5
    Elephant 10 Gerbil 8 Elephant 10
    Rhino 15 Rhino 15
    Cat 4 Cat 4
    Mouse 8
    Dog 4
    Gerbil 8

    Thanks,
    Alex

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,822

    Re: Sort multiple columns to one column, skip blanks

    Please try the file attached -

    Below is the code used -

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by NeedForExcel; 05-20-2013 at 02:12 PM.
    Cheers!
    Deep Dave

  3. #3
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,020

    Re: Sort multiple columns to one column, skip blanks

    Hi, AlexCoyne,

    what my browser tells me about your file:
    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  4. #4
    Registered User
    Join Date
    08-28-2012
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Sort multiple columns to one column, skip blanks

    sorry, posted to wrong thread

  5. #5
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,822

    Re: Sort multiple columns to one column, skip blanks

    Sure!!

    Instead of
    Sheets("Sheet1")
    try using Activesheet.

    Regarding Headers, you can change the Counter's start number from instead of 2 to 8.

    Kindly revert if it does not work.

    Thank You,

    Deep

  6. #6
    Registered User
    Join Date
    08-28-2012
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Sort multiple columns to one column, skip blanks

    Hi msexcelathome, thank you! I have a few questions:

    My data start in row 8 of column A, D, and G, and each list has a heading in row 6. I figured out how to change the columns, but when one of my lists doesn't have any data it will copy the heading in, and if there's at least 1 item in the list the heading is not copied. Do you know how I might fix that?

    Also, is there an easy way to make this happen as the items are typed in, instead of running the macro?

    Thanks,
    Alex

  7. #7
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,822

    Re: Sort multiple columns to one column, skip blanks

    Oh yeah.. And regarding
    Is there an easy way to make it populate the list automatically as items are entered?
    Maybe Holger can help.. I still havent learnt that much VBA..


    Cheers!

    Deep

  8. #8
    Registered User
    Join Date
    08-28-2012
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Sort multiple columns to one column, skip blanks

    Thanks... sorry I got a little confused with my replies.
    Alex

  9. #9
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,020

    Re: Sort multiple columns to one column, skip blanks

    Hi, Alex,

    if my guess is correct the entries will span over 2 columns which would need to be filled both - do you really need the action on every change in any column or is it good enough to start when you open the workbook/return to the sheet?

    This is still static, will clear the destination range first (make sure there is an empty column between your data and the destiation):
    Please Login or Register  to view this content.

  10. #10
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,822

    Re: Sort multiple columns to one column, skip blanks

    Here you go!

    You can change the line -
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    .

    Replace the Word "Headers" with your Header name. Assuming the Header names will be common.

    Thank You,

    Deep

  11. #11
    Registered User
    Join Date
    08-28-2012
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Sort multiple columns to one column, skip blanks

    MSExcelatHome,

    Per your recommendations, I am currently working with the following code, but it continues to make the text in row 6 in a column with nothing entered in its list appear in the final list. Do you know what else I could try changing?

    Thanks,
    Alex

    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)

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