Merge multiple columns into one

In Excel, I'd like to merge/combine the data of multiple, nonconsecutive columns into one (excluding the headers, preferably on another sheet).

Example: I have a sheet containing rows of names which contain data for up to three choices made, along with any options for that choice in the next respective column. The choices are in columns C, E, and G; some of which are blank. The options are in columns D, F, and H; some of which are blank.

From that data, I’d first like to create a new sheet with all the choices combined into one column, and their respective options in the next column. I’d then like to remove any rows that have blank cells in the “options” column.

I’m currently manually copy-pasting these to a new sheet, then finding any blank cells and deleting their respective rows.

What is the most effective way to do this?

Pre-formatted sheet with raw data | Desired result from raw data

4 Answers

Power Query has commands to combine columns. The result can be placed in a new sheet.

Formulas are another option, along the lines of

=Sheet1!A2&Sheet1!C2&Sheet1!X2

Copy down.

I would like to suggest simple multi stage Index formula, help you to merge multiple columns into one.

• Formula in cell T2, fill it down.

  =IFERROR(INDEX($O$2:$O$6, ROWS(T1:$T$1)), IFERROR(INDEX($Q$2:$Q$7, ROWS(T1:$T$1)-ROWS($O$2:$O$6)), IFERROR(INDEX($S$2:$S$4, ROWS(T1:$T$1)-ROWS($O$2:$O$6)-ROWS($Q$1:$Q$7)), "")))

• Also, add Sheet name with data range like,

IFERROR(INDEX(Sheet1!$O$2:$O$6, .

Edited:

Using OPs sample data this should be the formula in Sheet 2.

• Formula in cell V10 of Sheet2, fill it Right` then Down.

  =IFERROR(INDEX(Sheet1!X$2:X$7, ROWS(Sheet2!V9:$V$9)), IFERROR(INDEX(Sheet1!Z$2:Z$7, ROWS(Sheet2!V9:$V$9)-ROWS(Sheet1!X$2:X$7)), IFERROR(INDEX(Sheet1!AB$2:AB$7, ROWS(Sheet2!V9:$V$9)-ROWS(Sheet1!X$2:X$7)-ROWS(Sheet1!Z$2:Z$7)), "")))

N.B.You may adjust cell references in the formula as needed.

Suppose your worksheet name is Company.

You want to copy Column 1, Column 3, Column 8

In Empty Work Sheet :In A1 enter formula =Company!A1, in B1 Enter =Company!C1, in cell C1 Enter =Company!H1

Copy the Entered Data from A1:C1 down the Rows to get desired results.

"Overdo." Definitely not a part of the "Work smarter, not harder" approach.

This problem calls for nothing more than copying a column pair's data and pasting it in the desired output columns, then the next pair (moving to the end of the output columns so as to add to the data, not overwrite it!), and so on until all are in the desired output columns.

Then simply Sort based upon the Options column which moves the blanks to one end or the other of the column (depending upon your A-Z or Z-A order choice). Select all the rows with blanks and delete the data.

If this were a dynamic listing and you needed to do this on a recurring basis, you MIGHT like a formulaic approach, but... maybe not even then since this is such a simple thing.