# Merge cell ranges into one list

The above image demonstrates a formula that adds values in three different columns into one column.

#### Table of Contents

- Merge three columns into one list - Excel 365
- Merge three columns into one list - earlier Excel versions
- Merge three columns into one list - Excel 2003 and earlier versions
- Get Excel file
- Combine cell ranges ignore blank cells - UDF
- Merge, sort and remove blanks from multiple cell ranges - UDF
- Consolidate sheets - VBA
- Merge two columns - Excel 365
- Merge two columns - earlier Excel versions

## 1. Merge three columns into one list - Excel 365

Excel 365 subscribers can access new array manipulation formulas that make working with arrays and cell ranges much easier, one of those new functions is the VSTACK function.

It allows you to merge multiple cell ranges vertically, meaning the second cell range/array is joined below the first cell range/array. The result is a dynamic array formula that spills values below as far as needed.

This example shows how to merge three nonadjacent cell ranges with different sizes, cell range B3:B7 has five values. Cell range D3:D4 has two values, andÂ F3:F6 has four values. The formula is entered in cell H3 and the array values are spilled to cell H3 and cells below as far as needed.

Excel 365 dynamic array formula in cell H3:

### Explaining formula

The VSTACK function combines cell ranges or arrays. Joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)

Function syntax: VSTACK(array1,[array2],...)

## 2. Merge three columns into one list - earlier Excel versions

This example works in earlier Excel versions from 2007 to Excel 2019, it requires a more complicated formula because these versions don't have the VSTACK function.

The IFERROR function is used a lot in this example and I want to warn you that it also handles all formula errors, this may

Formula in H3:

Copy cell H2 and paste to the cells below.

### Explaining formula in cell H2

The IFERROR function moves the calculation to the next partÂ (formula2) when the first partÂ (formula1) begins to return errors. That is also true for the second part (formula2), when errors occur the calculation continues with the third part (formula3)

IFERROR(IFERROR(*formula1*, *formula2*),Â *formula3*)

Formula1 extracts values from List1.Â Formula2 extracts values from List2.Â Formula3 extracts values from List3.

#### Step 1 - Count cells vertically

The ROWS function counts rows in a cell reference. H2:$H$2 is special, it expands as the formula is copied to the cells below.

ROWS(H2:$H$2)

returns 1.

#### Step 2 - Return value

The INDEX function returns values from a cell range based on a row number and column number.

INDEX($B$3:$B$7, ROWS(H2:$H$2))

becomes

INDEX($B$3:$B$7, 1)

becomes

INDEX({"AA";"DD";"CC";"GG";"HH"}, 1)

and returns "AA" in cell H3.

#### Step 3 - Loop

When the formula starts returning errors the second part of the formula begins.

INDEX($D$3:$D$4, ROWS(H2:$H$2)-ROWS($B$3:$B$7))

It also takes into account the number of values returned from the first cell range, for example in cell H8:

INDEX($D$3:$D$4, ROWS(H7:$H$2)-ROWS($B$3:$B$7))

becomes

INDEX($D$3:$D$4, 6-ROWS($B$3:$B$7))

becomes

INDEX($D$3:$D$4, 6-5)

becomes

INDEX({"MM";"WW"}, 1)

and returns "MM" in cell H8.

## 3. Merge three columns into one list - Excel 2003 and earlier versions

Formula in cell H3:

**Named ranges**

List1 (A2:A6)

List2 (B2:B3)

List3 (C2:C5)

## 4. Get Excel file

merge-three-columns_excel_2003.xls

## 5. Combine cell ranges ignore blank cells - UDF

The image above demonstrates aÂ user defined function that merges up to 255 cell ranges and removes blanks. I will also show you how to sort these values.

A user defined function is a custom function in Excel than anyone can build, you simply copy the code below to a module in the Visual Basic Editor and then enter the function name and arguments in a cell.

I have articles that shows you how to combine two and three columns using array formulas. Check out thisÂ article that demonstrates how to Merge tables based on a condition.

Array formula in cell range B3:B70:

### 5.1 How to create an array formula

- Select cell range B3:B70.
- Copy (Ctrl + c) above array formula.
- Paste (Ctrl + v) array formula to formula bar.

- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.

### 5.2 VBA code

'Name function and declare arguments Function MergeRanges(ParamArray arguments() As Variant) As Variant() 'Declare variables and data types Dim cell As Range, temp() As Variant, argument As Variant Dim iRows As Integer, i As Integer 'Redimension temp in order to let it grow if needed ReDim temp(0) 'Iterate through each cell range For Each argument In arguments 'Iterate through each cell in cell range For Each cell In argument 'If cell not equal to nothing If cell <> "" Then 'Save cell value to array variable temp(UBound(temp)) = cell 'Add another container to array ReDim Preserve temp(UBound(temp) + 1) End If Next cell Next argument 'Remove container from array ReDim Preserve temp(UBound(temp) - 1) 'Count cells occupied by user defined function iRows = Range(Application.Caller.Address).Rows.Count 'Add containers For i = UBound(temp) To iRows 'Add another container ReDim Preserve temp(UBound(temp) + 1) 'Save "" to array temp(UBound(temp)) = "" Next i 'Return array MergeRanges = Application.Transpose(temp) End Function

### 5.3 How to add the user defined function to your workbook

- Press Alt-F11 to open visual basic editor
- Press with left mouse button on Module on the Insert menu
- Copy and paste vba code
- Exit visual basic editor

Sort text from multiple cell ranges combined (user defined function):

Recommended articles

Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]

## 6. Merge,sort and remove blanks from multiple cell ranges - UDF

I used the "Sort array" function found here: Using a Visual Basic Macro to Sort Arrays in Microsoft Excel (microsoft) with some small modifications.

Function SelectionSort(TempArray As Variant) Dim MaxVal As Variant Dim MaxIndex As Integer Dim i As Integer, j As Integer ' Step through the elements in the array starting with the ' last element in the array. For i = UBound(TempArray) To 0 Step -1 ' Set MaxVal to the element in the array and save the ' index of this element as MaxIndex. MaxVal = TempArray(i) MaxIndex = i ' Loop through the remaining elements to see if any is ' larger than MaxVal. If it is then set this element ' to be the new MaxVal. For j = 0 To i If TempArray(j) > MaxVal Then MaxVal = TempArray(j) MaxIndex = j End If Next j ' If the index of the largest element is not i, then ' exchange this element with element i. If MaxIndex < i Then TempArray(MaxIndex) = TempArray(i) TempArray(i) = MaxVal End If Next i End Function

## 7. Consolidate sheets - VBA

**Question:**

I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The columns I am interested in each worksheets are "Date Plan", "Date Completed" and "variance" and "Project Code"I then want data from all these column to be extracted in a Report worksheet and later want to do a trend chart by sorting all dates in chronological order.

Key bit to start is how do I get data out from worksheets. Essentially I want all the data without any loss. There are chances that some of the data between worksheet1 & 2 could be identical, apart from project code.

Also, preference is that this coding or formula should work for any future addition to worksheet data and workbook worksheets.

**Answer:**

This VBA code copies all values from each column header in each sheet to "consolidate" sheet. You can choose which sheets to consolidate, cell A2 and down. See picture above.

You can also choose what column headers to consolidate. Cell B1 and cells to the right. Remember column headers must be on row 1 in each sheet. Cell values don't have to be contiguous in each sheet.

### VBA

Sub Consolidate() Application.ScreenUpdating = False 'Dim Dim csShts As Range Dim clmnheader As Range Dim sht As Worksheet Dim LastRow As Integer Dim i As Long Set csShts = Worksheets("Consolidate").Range("A2") Set clmnheader = Worksheets("Consolidate").Range("B1") 'Iterate sheet cells on sheet "consolidate" Do While csShts <> "" ' Iterate all sheets to find a match between sht and csShts For Each sht In Worksheets 'Find a matching sheet i = 0 If sht.Name = csShts Then 'Select sheet sht.Select 'Select cell A1 on sheet Range("A1").Select 'Iterate columnheaders on sheet Do While Selection <> "" 'Iterate column headers on consolidate sheet Set clmnheader = Worksheets("Consolidate").Range("B1") Do While clmnheader <> "" 'Find matching column headers on consolidate sheet against column headers on current sheet If clmnheader.Value = Selection.Value Then 'Find last row in column LastRow = ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByRows).Row End If 'Save maximum last row number on current sheet If LastRow > i Then i = LastRow End If 'Move to next column header on consolidate sheet Set clmnheader = clmnheader.Offset(0, 1) Loop ActiveCell.Offset(0, 1).Select Loop sht.Select Range("A1").Select Set clmnheader = Worksheets("Consolidate").Range("B1") 'Iterate columnheaders from beginning on current sheet Do While Selection <> "" Set clmnheader = Worksheets("Consolidate").Range("B1") 'Iterate column headers on consolidate sheet Do While clmnheader <> "" If clmnheader.Value = Selection.Value Then Set clmnheader = clmnheader.Offset(1, 0) 'Copy range Do While Selection.Row <= i ActiveCell.Offset(1, 0).Select Selection.Copy clmnheader.Insert Shift:=xlDown Loop ActiveCell.Offset(-i, 0).Select Set clmnheader = clmnheader.Offset(-i, 0) 'Set clmnheader = clmnheader.End(xlUp) End If 'Move to next column header on consolidate sheet Set clmnheader = clmnheader.Offset(0, 1) Loop 'Move to next cell on current sheet ActiveCell.Offset(0, 1).Select Loop End If Next sht 'Move to next cell Set csShts = csShts.Offset(1, 0) Loop Sheets("Consolidate").Select End Sub

Now you can easily sort dates in chronological order and create a trend chart.

**Get excel file**

(Excel 97-2003Â Workbook *.xls)

**Remember to backup your original excel file. You canÂ´t undo a macro.**

## 8. Merge two columns - Excel 365

The new VSTACK function, available for Excel 365 subscribers, handles this task easily. In fact, it is built solely to combine cell ranges or arrays.

This example shows how to merge two nonadjacent cell ranges with different sizes located in B3:B7 and D3:D5, the result is returned to cell F3 and cells below as far as needed.

This is a new behavior to dynamic array formulas in Excel 365, called spilling meaning values from a dynamic array formula are all returned if adjacent cells are empty.

Excel 365 dynamic array formula in cell F3:

### Explaining formula

#### Step 1 - Populate arguments

The VSTACK function combines cell ranges or arrays. Joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)

Function syntax: VSTACK(array1,[array2],...)

VSTACK(array1,[array2],...)

becomes

VSTACK(B3:B7, D3:D4)

#### Step 2 - Evaluate VSTACK function

VSTACK(B3:B7, D3:D4)

becomes

VSTACK({"AA";"DD";"CC";"GG";"HH"}, {"MM";"WW"})

and returns

{"AA"; "DD"; "CC"; "GG"; "HH"; "MM"; "WW"}.

## 9. Merge two columns - earlier Excel versions

This example demonstrates a formula that only works in Excel 2007 and later versions, it utilizes the IFERROR function to move between cell ranges. However, the IFERROR function handles all formula errors and this may make it hard for you to spot other formula errors.

If you are looking for a formula to merge columns based on a condition read this article: Merge tables based on a condition

Formula in F3:

Copy cell C2 and paste it down as far as needed.

Earlier versions of excel, formula in C2:

Copy cell C2 and paste it down as far as needed.

### How the formula in F3 works

#### Step 1 - Extracting List 1

=IFERROR(INDEX($A$2:$A$6, ROWS(C1:$C$1)), IFERROR(INDEX($B$2:$B$3, ROWS(C1:$C$1)-ROWS($A$2:$A$6)), ""))

The ROWS function calculate the number of rows in a cell range.

Function syntax: ROWS(array)

In cell C2: INDEX($A$2:$A$6, ROWS(C1:$C$1))

becomes

INDEX($A$2:$A$6, 1)

The INDEX function returns a value or reference from a cell range or array, you specify which value based on a row and column number.

Function syntax: INDEX(array, [row_num], [column_num])

INDEX($A$2:$A$6, 1)

equals "AA"

In cell C3: INDEX($A$2:$A$6, ROWS(C1:$C$1))

becomes

INDEX($A$2:$A$6, ROWS(C2:$C$1))

becomes

INDEX($A$2:$A$6, 2) equals "DD"

and so on...

#### Step 2 - Error when all values in List 1 are processed

IFERROR(INDEX($A$2:$A$6, ROWS(C1:$C$1)), IFERROR(INDEX($B$2:$B$3, ROWS(C1:$C$1)-ROWS($A$2:$A$6)), ""))

In cell C7 something unexpected happens:

In cell C7: INDEX($A$2:$A$6, ROWS(C1:$C$1))

becomes

INDEX($A$2:$A$6, ROWS(C6:$C$1))

becomes

INDEX($A$2:$A$6, 6) equals "#REF!" error

There are no more values in List 1 so we need to continue on List 2.

IFERROR() takes care of this:

The IFERROR function if the value argument returns an error, the value_if_error argumentÂ is used.Â If theÂ valueÂ argument does NOT return an error, the IFERROR function returns the value argument.

Function syntax: IFERROR(value, value_if_error)

#### Step 3 - Continue with List 2

In cell C7:

=IFERROR(INDEX($A$2:$A$6, ROWS(C6:$C$1)), IFERROR(INDEX($B$2:$B$3, ROWS(C6:$C$1)-ROWS($A$2:$A$6)), ""))

becomes

=IFERROR(#REF!, IFERROR(INDEX($B$2:$B$3, ROWS(C6:$C$1)-ROWS($A$2:$A$6)), ""))

and becomes

IFERROR(INDEX($B$2:$B$3, ROWS(C6:$C$1)-ROWS($A$2:$A$6)), "")

INDEX($B$2:$B$3, ROWS(C6:$C$1)-ROWS($A$2:$A$6))

becomes

INDEX($B$2:$B$3, 6-ROWS($A$2:$A$6))

becomes

INDEX($B$2:$B$3, 6-5)

becomes

INDEX($B$2:$B$3, 1)

equals "MM" in List 2.

and so on...

#### Step 4 - Error when all values in List 2 and List 1 are evaluated

In cell C9:

=IFERROR(INDEX($A$2:$A$6, ROWS(C8:$C$1)), IFERROR(INDEX($B$2:$B$3, ROWS(C8:$C$1)-ROWS($A$2:$A$6)), ""))

becomes

=IFERROR(REF!, IFERROR(INDEX($B$2:$B$3, ROWS(C8:$C$1)-ROWS($A$2:$A$6)), ""))

becomes

=IFERROR(REF!, IFERROR(INDEX($B$2:$B$3, 8-ROWS($A$2:$A$6)), ""))

becomes

=IFERROR(REF!, IFERROR(INDEX($B$2:$B$3, 8-5), ""))

becomes

=IFERROR(REF!, IFERROR(REF!, ""))

equals "" (nothing)

### Get Excel *.xlsx file

### Useful links

Combine data from multiple sheets - Microsoft

Consolidate data in Excel and merge multiple sheets into one worksheet

Consolidate Data From Multiple Worksheets in a Single Worksheet in Excel

### Combine merge category

The picture above shows how to merge two columns into one list using a formula. Table of Contents Merge two […]

This article demonstrates two formulas, they both accomplish the same thing. The Excel 365 formula is much smaller and is […]

Merge Ranges is an add-in for Excel that lets you easily merge multiple ranges into one master sheet. The Master […]

### Excel categories

### 48 Responses to “Merge cell ranges into one list”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

I have down loaded the consolidated file and it does not appear to work the way I expected.

I am looking to combine cashflow worksheets for multiple projects. The row headings are the same for each project but the column heading varies (they are set as end of month dates)

The issue with normal consolidation is that each sheet has to be identical. But because each project starts and stops at different months the consolidation is messy

Can I use your example and modify it.

You can use it and modify it.

It is an interesting question and IÂ´ll post an answer as soon as possible.

the code has it that all the headers must be in row 1 of each sheet. How would you modify if the headers all on the 13th row on each sheet?

Neville Ash,

Read https://www.get-digital-help.com/2010/09/06/consolidate-sheets-in-excel-part-2/

Thanks A LOT!!! I really mean it. Lifesavers you guys are!

Can you translate this into an earlier version of Excel please....I REALLY wish I had a clue how to use VBA (or how to write these formulas myself!)

Shawna,

Yes, the formula is large but it is not an array formula. I have included the excel 2003 solution in this blog post. Read above and you can also get an Excel 2003 file.

Thanks, now we're cooking!! Anyway to remove the blanks from the resulting "merged" list?

nevermind...sorry I bothered you, I can't get the new formula to work at all, so it's pointless. Thanks for the time and effort anyway.

Hmmm, tried again and I got it to work, but I still come up with zeros if one of my Lists has a blank. This is SO frustrating!!

Shawna,

I hope this user defined function works in excel 2003:

Where to copy the code?Press Alt-F11 to open visual basic editor

Press with left mouse button on Module on the Insert menu

Copy and paste the user defined function into module

Exit visual basic editor

How to use user defined function in excelSelect a cell

Type MergeRanges(A1:C10, Sheet2!A3:B10) in formula bar, you can have as many range arguments you like.

Press Ctrl + SHIFT + ENTER

Add more cells to your selection.

Press with left mouse button in Formula bar.

Press Ctrl + SHIFT + ENTER

I wish I could try it, but I don't know how to create VBA. I'm a novice, though I did try a few times from random directions online, but it didn't work. :(

LOL, sorry...you gave me directions to follow, DUH! It's early, I'm not awake yet, so the attention hasn't kicked in. I'll let you know how I make out when I get to work. Thanks!

Nope...I set up the following example:

Heading1 Heading2 Heading3 Merge aa aa bb hh bb

cc ii 1 cc

2 dd

dd jj 3 dd

ee ee

kk 4 ff

ff ff

gg gg

#VALUE!

#VALUE!

#VALUE!

I pasted the code in the VBA module and typed the statement:

=MergeRanges(A2:A11, B2:B11, C2:C11) into the Merge cell. Then copied it down. However, notice only the A range merged. Also, the merged column repeated dd and ff (the only ones preceeded by a space). Any idea what I messed up?

Shawna,

Get the Excel file

udf-merge-ranges.xls

Udf is in cell range B3:B24, sheet1.

Can we please discuss via email so I can attach an example of what I am doing? My typed example above was obviously worthless! LOL

Thanks! :)

Shawna,

Yes, you can use the contact form on this page: Contact me

Hello,

I gotr your Excel workbook and wanted to know how to combine lists that are 2000 items in length?

This is great! Almost exactly what I was looking for.

I got the consolidate sheet and I am able to get all of my data to consolidate fine. If I update something and press consolidate again, it displays a second set of data instead of replacing what showed up the upon the first press with left mouse button. Is there a way to replace the existing data on the consolidate sheet when ever I press with left mouse button on "consolidate"?

also, after the 540th row, only half of the columns show up. any ideas? all told, I have 14 columns and 921 rows.

Stephen,

This line deletes all values in range B2:E65536:

Worksheets("Consolidate").Range("B2:E65536") = ""

Copy and paste after this line:

Dim i As Long

Get the Excel filestephen.xls

hi

i have two sheets in my workbook

sheet1 :

A14:A200 = item code

E14:E200 = quantity

sheet2 :

column "C" = item code

column "I" = Quantity

i want if

sheet1 :

A14=101, A15=102 & E14=4&E15=5

sheet 2 :

C15=101, c22=102 & C15=20, C22=25

then,,,

vba search for 101 nd 102 in sheet 2, column c

and add quantity

ans could be

C15=24 & E15=30

can any1 help me for this

Deepak,

Can you provide som sample data?

Hi Oscar,

Here I found one interesting solution to merge table to single row. It could be useful for you :)

https://www.cpearson.com/excel/TableToColumn.aspx

I modified formula with INDEX() function (array formula) and like result :)

=INDEX(Table;1+MOD(ROW()-ROW(MergedRange);ROWS(Table));1+TRUNC((ROW()-ROW(MergedRange))/ROWS(Table);0))

Best regards

The solution is to merge table to single column, not row...

BatTodor,

Thanks for sharing!

Redim Preserve does not execute all that quickly, so it is usually a good idea to avoid using it too often. Here is an alternate function to the one you posted which avoids them altogether...

Rick Rothstein (MVP - Excel),

I didnÂ´t know! I am curious, I have to do some speed tests.

Thank you for your valuable contribution!

This works well, but doesn't work if you have strings over 255 letters long! Any idea how to work around that? Thanks, Tom

Hello Oscar,

thanks for updating this code...

can you do a favour,,,, if i am using this code to consolidate the data,it is working fine for me but can i get the sheet(project) name as well against the data, so that i can track, from which project i pick the number,

Brilliant work, I converted List1, List2,List3 into dynamic range.

ALIST = =OFFSET($A$1,0,0,COUNTA($A:$A),1)

BLIST = =OFFSET($B$1,0,0,COUNTA($B:$B),1)

CLIST = =OFFSET($C$1,0,0,COUNTA($C:$C),1)

DLIST = =OFFSET($D$1,0,0,COUNTA($D:$D),1)

Here link to solution with screenshot.

https://stackoverflow.com/questions/14774806/how-to-combine-4-column-into-1-column

Zuberr,

thank you!

This code does exactly what I was looking for, except in one case: if the fields being consolidated have formulas that refer to columns I'm not consolidating (ie it returns #ref or #value errors). Is there any way to amend the code to "paste values"?

I've combined several cell ranges across several sheets, how would I eliminate the duplicate cells using the vba provided?

Trying to generate a list based on several cell ranges on Sector A-P sheets and combine totals on a Totals sheet.

This is a very useful formula. But, here the list names have fixed ranges and if the ranges expand or reduces, then the formula does not hold good. For example, list1 range A1:A20 with data upto A7, list2 range B1:B20 with data upto row b17 and list3 range C1:C20 with data upto C10. In simple words, the ranges need to be dynamic. Can anybody help with a formula (not Vba). Regards.

R Vijayakumar,

Try this:

https://www.get-digital-help.com/2011/05/17/excel-charts-use-dynamic-ranges-to-add-new-values-to-both-chart-and-drop-down-list/

i have tried in different ways and found this array formula works....

=IFERROR(IFERROR(IFERROR(INDEX(MultiPlyYarnPRCount, MATCH(0, COUNTIF(L$7:$L7, MultiPlyYarnPRCount), 0)), INDEX(MultiPlyYarnDHPRCount, MATCH(0, COUNTIF(L$7:$L7, MultiPlyYarnDHPRCount), 0))),INDEX(MultiPlyYarnDHCRCount, MATCH(0, COUNTIF(L$7:$L7, MultiPlyYarnDHCRCount),0))),"")

MultiPlyYarnPRCount (I8:I100),MultiPlyYarnDHPRCount (J8:J100) and MultiPlyYarnDHCRCount (K8:K100) are three ranges in three columns, each having varying lengths of data i.e. first range has data in only one row, second range 23 rows and third one has 18 rows of data. This formula works fine.

I request your suggestion for fine-tuning the formula if possible.

Regards,

I really need help!

Have similar but bigger issue

Have three columns in excel:

Column A: Category

Column B: Value

Column C: Value

I want to consolidate the list such that if I have a row like:

A1=Car | B1=Ford | C1=Toyota

A2=Scooter | B1=Honda | C1=(blank)

Gives me a result:

A1=Car | B1=Ford

A2=Car | B2=Toyota

A3=Scooter | A3=Honda

Sorry, correction:

I want to consolidate the list such that if I have a row like:

A1=Car | B1=Ford | C1=Toyota

A2=Scooter | B2=Honda | C2=(blank)

Gives me a result:

A1=Car | B1=Ford

A2=Car | B2=Toyota

A3=Scooter | B3=Honda

How can I merge 5 columns?

thanks

anthony,

The easiest way to go is to use a simple user defined function:

Where to copy the code?

Press Alt-F11 to open visual basic editor

Press with left mouse button on Module on the Insert menu

Copy and paste the user defined function into module

Exit visual basic editor

How to use user defined function in excel

Select a cell

Type =MergeRanges(A1:C10, Sheet2!A3:B10) in formula bar, you can have as many range arguments you like.

Press Ctrl + SHIFT + ENTER

Add more cells to your selection.

Press with left mouse button in Formula bar.

Press Ctrl + SHIFT + ENTER

Hi people,

Here's a solution i'm trying to solve using the functionality/formula provided at the top of this post .

Let's substitute List1, List 2 and List3 ranges with dynamic ranges that are using offsets when referring to data filter results elsewhere in my workbook. these dynamic ranges sometimes return no data as there are no results at times in my filters . my problem is, when one of the dynamic ranges has no data in it, that then corrupts the merge.

My mind is thinking incorporating ISBLANK or something similar to the above IFERROR INDEX ROWS formula where an empty dynamic range doesn't then corrupt the merge.

Hope that makes sense. Am drowning in development deadlines, any help would be much appreciated.

Hi Geoff

Have you read this article?

https://www.get-digital-help.com/2010/05/18/merge-two-columns-with-possible-blank-cells-in-excel-formula/

I've been looking at this article as well as the article on "Merge two columns with possible blank cells", but I'm trying to find out how to do a union of the two:

How do I merge multiple (3+) columns into one, where there are blank or #N/A cells involved?

I can use the Two-Into-One article just fine for handling blanks (or changing isblank to isna to handle #N/A values), but I haven't been able to get it to handle 3+ columns even after trying to combine the parsing of both articles together.

Hi

I am finding an error on this line of code while compiling your vba of combining multiple cell ranges. Any inputs?

If cell <> "" Then

I have a sort of similar issue as Bossi but mine is multiple 2-column data. I wanted to have a dynamic way of merging them without having to change the formula every time I add more columns. URLs (header) are in odd columns and the corresponding titles (header) are in even columns. It also has to have only unique values and no blanks after merging. I found a Google Sheets implementation with blog title "Select Every nth Column in Query in Google Sheets (Dynamic Formula)" but I couldn't make it work. Perhaps an Excel function will work. Any ideas?

Chris,

This formula lets you add new Excel Tables dynamically without changing the formula at all. All data sets are Excel Tables making them dynamic by default.

Excel 365 dynamic array formula in cell B10:

I don't have 365 but it's good to know it can be done. Hopefully drop gets implemented in Excel/Google Sheets in the future. Thanks a bunch!