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: Pre-formatted sheet with raw data

Desired result from raw data: Desired result from raw data


4 Answers 4


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, 2019 at 19:35
  • 1
    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, 2019 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.


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

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.