Need a function or combination of functions to allow me to combine (sort?) multiple arrays into one

[TheOldPuterMan]

I have multiple columns that I need to sort into one. I can not find a way to get the sort function to do this and all other methods I've tried are cumbersome.

Any help here please, thanks

TheOldPuterMan

[#2]

Did you try SORTBY() function. Or use CHOOSECOLS() function then apply SORT() function. Attach a sample file and show your desired output.

[#3]

@TheOldPuterMan 

Here are three ways to achieve this, with examples in an Excel sheet:

1. Custom Sort with Concatenation:

This method utilizes the Sort function and the CONCATENATE function to merge and sort data from multiple columns.

Steps:

• Example Data: Create a table with your data spread across several columns (A, B, C). In this example, let's say column A has names, B has ages, and C has cities.
• Create a new column: Add a new blank column at the end (D).
• Apply CONCATENATE formula: In the first cell of the new column (D1), enter the formula =CONCATENATE(A1,", ",B1,", ",C1). This merges data from columns A, B, and C, separated by commas, into a single cell in D1.
• Copy and paste formula: Drag the formula down the entire D column to apply it to all rows.
• Sort your data: Select the entire table (including headers) and go to the Data tab. Click Sort.
• Custom sort: Choose Custom Sort in the Sort dialog box.
• Sort by new column: In the Sort by column, select the new column you created (D) and choose Sort Ascending or Sort Descending depending on your preference.
• Voila! Your data is now sorted based on the combined information from multiple columns.

2. Power Query (Excel 2016+):

If you're using Excel 2016 or later, Power Query offers a more efficient and automated solution.

Steps:

• Select your data: Highlight the table containing your multiple columns.
• Go to Power Query: Click the Data tab, then Get Data and choose From Table/Range.
• Transform data: In the Power Query Editor, click the Add Column button.
• Create a custom column: Choose Custom Column and define a formula similar to CONCATENATE, combining data from your desired columns with separators.
• Sort and load: Click the Sort button, choose your sort criteria like the previous method, and click Close & Apply. The transformed data with sorted combined values will be loaded back into your Excel sheet.

3. Text to Columns Wizard:

This method helps if you want to permanently combine and sort data into a single column.

Steps:

• Prepare your data: Ensure your data has separators clearly defining each value within a cell. In our example, commas after each piece of information (name, age, city) are essential.
• Text to Columns: Go to the Data tab and click Text to Columns.
• Delimited: Choose Delimited in the wizard and tick the checkbox for the separator (comma in this case).
• Next and Finish: Click Next and ensure your data preview looks correctly combined. Finish the wizard to merge your columns into one with all your desired information.
• Sort as usual: You can now simply sort this new combined column (A or B depending on your choice) by ascending or descending order.

Remember to adjust the formulas and steps based on your specific data and desired combination format. These methods should provide you with efficient ways to sort multiple columns into one in Exce.

[#4 · Accepted solution]

I ended up using a combination of SORT, FILTER, CHOOSE and VSTACK to get what I needed. Your suggestion to look at CHOSECOL() gotme thinking.

Thanks!! TheOldPuterMan