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


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

Formulas are another option, along the lines of


Copy down.

  • Is Power Query only available on PC? – Todd Dec 12 '19 at 19:35
  • Of course. It is included in Excel 2016 and later (called Get and Transform) and can be downloaded as a free add-in for Excel 2010 and 2013. – teylyn Dec 12 '19 at 20:30

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

enter image description here

  • 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, .


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

enter image description here

  • 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.