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
- Merge two columns with possible blank cells - Excel 365
- Merge two columns with possible blank cells - earlier versions
- Group rows based on a condition
- Merge matching rows
- Merge and then sort text from two columns combined (array formula)
- Sort text from multiple cell ranges combined (user defined function)
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)) 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)) 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) 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)) returns "AA"
In cell C3: INDEX($A$2:$A$6, ROWS(C1:$C$1)) returns "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)) returns "#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)), "")) returns "MM" in List 2.
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)), ""))
returns "" (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
10. Merge two columns with possible blank cells - Excel 365
This example works only in Excel 365, it contains three functions only available for Excel 365: LET, VSTACK and FILTER. A dynamic array formula is entered as a regular formula, however, it spills values to adjacent cells automatically as far as needed.
Dynamic array formula in cell F3:
Explaining formula
Step 1 - Stack cell ranges vertically
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).
VSTACK(array1, [array2], ...)
VSTACK(B3:B8,D3:D6)
returns {"AA"; "DD"; ... ; "TT"}.
Step 2 - Remove blanks
The FILTER function filter values/rows based on a condition or criteria.
FILTER(array, include, [if_empty])
FILTER(VSTACK(B3:B8,D3:D6),VSTACK(B3:B8,D3:D6)<>"")
returns {"AA"; "DD"; "GG"; "HH"; "TT"; "MM"; "WW"; "TT"}.
Step 3 - Shorten formula
The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.
LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])
FILTER(VSTACK(B3:B8,D3:D6),VSTACK(B3:B8,D3:D6)<>"")
VSTACK(B3:B8,D3:D6) is repeated twice in the formula above. I will name this intermediate calculation x.
LET(x,VSTACK(B3:B8,D3:D6),FILTER(x,x<>""))
The result is a smaller formula.
11. Merge two columns with possible blank cells - earlier versions
Question:
This article is terrific. Thanks so much for posting this solution!
I do have one question:
Let's say my "List 1" is auto updated and the number of entries in this list will fluctuate. Since the number of entries fluctuates, I would like to select a larger range than I actually have data in currently. The issue is when I make my "List 1" larger than the number of entries, the rows that don't currently have data in them, show up on my combined list as zeros.
So my question is, is there a way to adjust the formula so that when it looks at "List 1" for example, it skips over blank cells and continues to combine the list with "List 2".
(You can find the question here: Merge two columns into one list in excel)
Answer:
The array formula below removes blank cells. Another method is to use dynamic named ranges.
Array formula in C2:
Recommended post:
Recommended articles
The image above demonstrates a user defined function that merges up to 255 cell ranges and removes blanks. I will also […]
This is an array formula, here is how to enter it. Type the formula in cell C2, press and hold CTRL + SHIFT simultaneously. Press Enter once. Release all keys. If you did it correctly, you now have curly brackets before and after the formula.
Copy cell C2 and paste it to cells below, as far as needed.
This example merges two columns into one column using an array formula. If you are looking for merging two data lists with criteria, check this post: Merge lists with criteria
Named ranges
Recommended article:
Recommended articles
The above image demonstrates a formula that adds values in three different columns into one column. Table of Contents Merge […]
Explaining array formula in cell C8
Step 1 - Understand relative and absolute cell references
In cell C2 the formula is:
=IFERROR(INDEX(List1, SMALL(IF(ISBLANK(List1), "", ROW(List1)-MIN(ROW(List1))+1), ROW(A1))), IFERROR(INDEX(List2, SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A1)-SUMPRODUCT(--NOT((ISBLANK(List1)))))), ""))
In cell C8 the formula is:
=IFERROR(INDEX(List1, SMALL(IF(ISBLANK(List1), "", ROW(List1)-MIN(ROW(List1))+1), ROW(A7))), IFERROR(INDEX(List2, SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A7)-SUMPRODUCT(--NOT((ISBLANK(List1)))))), ""))
Recommended articles
What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD […]
Step 1 - Find cells containing a value in List 1 and return row numbers in an array
IF(ISBLANK(List1), "", ROW(List1)-MIN(ROW(List1))+1)
returns
{1;2;"";4;5;6}
Recommended articles
Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.
Step 2 - Return the k-th smallest row number
SMALL(array,k) Returns the k-th smallest number in this data set.
SMALL(IF(ISBLANK(List1), "", ROW(List1)-MIN(ROW(List1))+1), ROW(A7))
returns #NUM
Step 3 - Return a value of the cell at the intersection of a particular row and column
INDEX(array,row_num,[column_num]) returns a value or reference of the cell at the intersection of a particular row and column, in a given range
INDEX(List1, SMALL(IF(ISBLANK(List1), "", ROW(List1)-MIN(ROW(List1))+1), ROW(A7)))
returns #NUM
Recommended articles
Gets a value in a specific cell range based on a row and column number.
Step 4 - Check if formula returns an error
=IFERROR(INDEX(List1, SMALL(IF(ISBLANK(List1), "", ROW(List1)-MIN(ROW(List1))+1), ROW(A7))), IFERROR(INDEX(List2, SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A7)-SUMPRODUCT(--NOT((ISBLANK(List1)))))), ""))
becomes
IFERROR(INDEX(List2, SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A7)-SUMPRODUCT(--NOT((ISBLANK(List1)))))), "")
Recommended articles
The IFERROR function lets you catch most errors in Excel formulas. It was introduced in Excel 2007. In previous Excel […]
Step 5 - Find cells containing a value in List 2
IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1)
returns {1;"";3;4}
Step 6 - Return the k-th smallest row number
SMALL(array,k) Returns the k-th smallest number in this data set.
SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A8)-SUMPRODUCT(--NOT((ISBLANK(List1)))))
becomes
SMALL({1;"";3;4}, 2)
and returns 3.
Step 6 - Return a value of the cell at the intersection of a particular row and column
IFERROR(INDEX(List2, SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A8)-SUMPRODUCT(--NOT((ISBLANK(List1)))))), "")
becomes
IFERROR("WW", "")
and returns WW.
Get Excel sample file for this tutorial.
merge-two-columns with blanks.xlsx
(Excel 2007 Workbook *.xlsx)
11. Group rows based on a condition
This section explains how to merge values row by row based on a condition in column A using an array formula.
Mike asks:
Oscar,
I'm hoping you can help. I am trying to group a number of rows together by the first column, providing a union of the column values. In the example below, we are looking to Vendors V1-V3 to sell us some subset of products P1-P5.
; P1 ; P2 ; P3 ; P4 ; P5
V1 ; 1 ; ; ; 1 ;
V2 ; ; 1 ; ; 1 ;
V1 ; ; ; 1 ; ;
V3 ; ; ; 1 ; ; 1
Once transformed, I would like to see the following:
; P1 ; P2 ; P3 ; P4 ; P5
V1 ; 1 ; ; 1 ; 1 ;
V2 ; ; 1 ; ; 1 ;
V3 ; ; ; 1 ; ; 1
Thank you for your help!
Extract unique distinct values
Array formula in cell A9:
Copy and paste array formula in A9 down as far as needed.
Read this post for more details: How to extract a unique distinct list from a column
Group values
Formula in cell B9:
You are not required to enter this as an array formula.
Explaining formula in cell B9
The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication. This may sound complicated but it is not, you can build powerful calculations across columns and rows once you understand how arrays work.
Step 1 - Find matching vendor
The equal sign compares the value in cell A9 to each value in cell range A2:A5. It returns TRUE if equal and FALSE if not. It is not, however, a case sensitive comparison.
This means that V1 is equal to v1. TRUE and FALSE are boolean values.
$A9=$A$2:$A$5
becomes "V1 "={"V1 ";"V2 ";"V1 ";"V3 "}
and returns {TRUE; FALSE; TRUE; FALSE}
The first value in the array is TRUE which means that cell A2 is equal to cell value in A9. The second value in the array is FALSE which means that the value in cell A3 is not equal to the value in cell A9.
Step 2 - Find matching product
The following logical expression compares the value in cell B8 to all values in cell range B1:F1.
B$8=$B$1:$F$1
becomes " P1 "={" P1 "," P2 "," P3 "," P4 "," P5"}
and returns {TRUE, FALSE, FALSE, FALSE, FALSE}
Step 3 - Find values equal to 1
This steps identifies cells in cell range B2:F5 that contains 1.
$B$2:$F$5=1
returns {TRUE, FALSE, ... , TRUE}
Step 4 - Multiply arrays
When we multiply boolean values their numerical equivalents are returned. TRUE = 1 and FALSE = 0 (zero).
Multiplying values also means that we apply AND logic meaning the boolean values we multiply must all be 1 to return 1. Example, 1*1 = 1 but 1*0 = 0. The parenthes determines the order of operation.
($A9=$A$2:$A$5)*(B$8=$B$1:$F$1)*($B$2:$F$5=1)
returns {1, 0, ... , 0}
Step 5 - Sum numbers in array
This step adds the numbers in the array and returns a total.
SUMPRODUCT({1, 0, 0, 0, 0; 0, 0, 0, 0, 0; 0, 0, 0, 0, 0; 0, 0, 0, 0, 0})
returns 1 in cell B9.
12. Merge matching rows
I'm using excel 2003. This is my problem.Sheet 1 COL A contains fruits, col B to H contains there prizes daily (1 week). take note that in col. A fruits name may randomly repeated in col A. What I need is put in Sheet 2 col A all fruit name but not repeated and put to column B to H, I to N, O to U there prizes .see sample below. hope u understand.
A B C .... I
1 apple 10 11 .... 8
2 orange 9 9 ..... 10
3 apple 11 11 ..... 12
4 apple 14 10 ..... 10
5 grapes 15 15 ..... 14In sheet 2 answer should be like this.
A B C ..... H I J.....N O P.... U
1 apple 10 11 8 11 11 12 14 10 10
2 orange 9 9 10
3 grapes 15 15 14
Answer:
Array formula in cell A2:
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Copy cell A2 and paste down as far as needed. See this blog post for an explanation: How to extract a unique distinct list from a column
Array formula in cell B2:
Copy cell B2 and paste B2:K4. Read more about relative and absolute cell references.
The formula above in cell B2 works only with numerical values, if cell range B2:D6 contains text values you need the following formula:
This Excel 365 dynamic array formula is explained here: REDUCE function It creates a unique list and groups adjacent values based on the values in the unique list.
Explaining array formula in cell B2
Step 1 - Filter values in matching rows
The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).
IF($A2=Sheet1!$A$2:$A$6, Sheet1!$B$2:$D$6, "")
returns
{10; 11; 8, ""; ""; "", 11; 11; 12, 14; 10; 10, ""; ""; ""}
Step 2 - Return the k-th smallest value
To be able to return a new value in a cell each I use the SMALL function to filter column numbers from smallest to largest.
SMALL({10; 11; 8, ""; ""; "", 11; 11; 12, 14; 10; 10, ""; ""; ""}, COLUMN(A1))
returns 8.
This merging partially works for me, but instead of ordering the values on sheet 2 from smallest to largest, I need to keep the original order for sheet 1, for example instead of:
apple: 8 10 10 10 11 11 11 12 14
keep the original order from sheet 1 like this:
apple: 10 11 8 11 11 12 14 10 10
how can I change the formula(s) to do it?
Thanks!!
Answer
Function MergeMatchingRows(SearchValue As Range, SearchRange As Range)
Dim r, c, ic As Single
Dim temp() As Variant
ReDim temp(0)
For r = 1 To SearchRange.Rows.Count
If SearchRange.Cells(r, 1) = SearchValue Then
For c = 2 To SearchRange.Columns.Count
If SearchRange.Cells(r, c) <> "" Then
temp(UBound(temp)) = SearchRange.Cells(r, c)
ReDim Preserve temp(UBound(temp) + 1)
End If
Next c
End If
Next r
ReDim Preserve temp(UBound(temp) - 1)
ic = Range(Application.Caller.Address).Columns.Count
For c = UBound(temp) To ic
ReDim Preserve temp(UBound(temp) + 1)
temp(UBound(temp)) = ""
Next c
MergeMatchingRows = temp
End Function
14. Sort text from two columns combined (array formula)
Array formula in cell F3:
This array formula handles duplicates but not blank cells.
14.1 How to create an array formula
- Select cell F3
- Copy/Paste above formula
- Press and hold Ctrl + Shift
- Press enter once
- Release all keys
14.2 How to copy array formula
- Select cell F3.
- Copy cell (Ctrl + c).
- Paste to cells below (Ctrl + v).
14.3 Explaining formula in cell F3
The IFERROR function makes the formula alternate between two lists depending on the sort order of each value.
IFERROR(part1, part2)
Step 1 - Build expanding number
Every value in these two lists will have number which corresponds to the sort order. To match each value we need to build a formula that returns a number from 0 (zero) to whatever.
The ROWS function returns a number based on an expanding cell reference, it grows automatically as the cell is copied to cells below.
ROWS(F$3:$F3)-1
becomes
1-1 and returns 0 (zero).
Step 2 - Create an array that shows the sort order of each value as if they were sorted alphabetically.
The COUNTIF function counts values based on a condition or criteria. If we add a "<" the function returns a value that shows the rank if the list were sorted. The ampersand concatenates the "<" with the values in the second argument.
COUNTIF($D$3:$D$7, "<"&$D$3:$D$7)
becomes
COUNTIF({"Gooseberry";"Apple";"Mangosteen";"Redcurrant";"Coconut"}, "<"&{"Gooseberry";"Apple";"Mangosteen";"Redcurrant";"Coconut"})
becomes
COUNTIF({"Gooseberry";"Apple";"Mangosteen";"Redcurrant";"Coconut"}, {"<Gooseberry";"<Apple";"<Mangosteen";"<Redcurrant";"<Coconut"})
and returns
{2;0;3;4;1}.
For example, how many values are sorted above "Apple" in array {"Gooseberry";"Apple";"Mangosteen";"Redcurrant";"Coconut"}? None, so Apple gets number 0 (zero).
Step 3 - Compare values to the other list
COUNTIF($B$3:$B$21, "<"&$D$3:$D$7)
becomes
COUNTIF({"Chico fruit";"Boysenberry";"Honeyberry";"Peach";"Guava";"Plum";"Raspberry";"Persimmon";"Papaya";"Jabuticaba";"Nance";"Kiwano";"Orange";"Cloudberry";"Kiwifruit";"Crab apples";"Tamarind";"Plumcot";"Plantain"}, "<"&$D$3:$D$7)
and returns
{4;0;9;18;3}.
Apple is clearly the top value if these values are combined and sorted from A to Z since this array also says that "Apple" is 0 (zero).
Step 4 - Take duplicates into account
This step adds 1 to the correct value in the array so also duplicates in both lists are returned. The IF function makes sure that previously counted values are taken into account.
IF((COUNTIF($D$3:$D$7, $D$3:$D$7)+COUNTIF($B$3:$B$21, $D$3:$D$7))<=COUNTIF($F$2:F2, $D$3:$D$7), 0, COUNTIF($F$2:F2, $D$3:$D$7))
becomes
IF(({1;1;1;1;1}+{0;0;0;0;0})<=COUNTIF($F$2:F2, $D$3:$D$7), 0, COUNTIF($F$2:F2, $D$3:$D$7))
becomes
IF(({1;1;1;1;1}+{0;0;0;0;0})<={0;0;0;0;0}, 0, {0;0;0;0;0})
becomes
IF({1;1;1;1;1}<={0;0;0;0;0}, 0, {0;0;0;0;0})
becomes
IF({FALSE;FALSE;FALSE;FALSE;FALSE}, 0, {0;0;0;0;0})
and returns
{0;0;0;0;0}
Step 5 - Add arrays
COUNTIF($D$3:$D$7, "<"&$D$3:$D$7)+COUNTIF($B$3:$B$21, "<"&$D$3:$D$7)+IF((COUNTIF($D$3:$D$7, $D$3:$D$7)+COUNTIF($B$3:$B$21, $D$3:$D$7))<=COUNTIF($F$2:F2, $D$3:$D$7), 0, COUNTIF($F$2:F2, $D$3:$D$7))
becomes
{2;0;3;4;1} + {4;0;9;18;3} + {0;0;0;0;0}
and returns
{6;0;12;22;4}
Step 6 - Match number to array
The MATCH function returns the relative position of a given value in a cell range or array.
MATCH(ROWS(F$3:$F3)-1, COUNTIF($B$3:$B$21, "<"&$B$3:$B$21)+COUNTIF($D$3:$D$7, "<"&$B$3:$B$21)+IF((COUNTIF($B$3:$B$21, $B$3:$B$21)+COUNTIF($D$3:$D$7, $B$3:$B$21))<=COUNTIF($F$2:F2, $B$3:$B$21), 0, COUNTIF($F$2:F2, $B$3:$B$21)), 0)
becomes
MATCH(0, COUNTIF($B$3:$B$21, "<"&$B$3:$B$21)+COUNTIF($D$3:$D$7, "<"&$B$3:$B$21)+IF((COUNTIF($B$3:$B$21, $B$3:$B$21)+COUNTIF($D$3:$D$7, $B$3:$B$21))<=COUNTIF($F$2:F2, $B$3:$B$21), 0, COUNTIF($F$2:F2, $B$3:$B$21)), 0)
becomes
MATCH(0, {6;0;12;22;4}, 0)
and returns 2.
Step 7 - Return value based on position
INDEX($D$3:$D$7, MATCH(ROWS(F$3:$F3)-1, COUNTIF($D$3:$D$7, "<"&$D$3:$D$7)+COUNTIF($B$3:$B$21, "<"&$D$3:$D$7)+IF((COUNTIF($D$3:$D$7, $D$3:$D$7)+COUNTIF($B$3:$B$21, $D$3:$D$7))<=COUNTIF($F$2:F2, $D$3:$D$7), 0, COUNTIF($F$2:F2, $D$3:$D$7)), 0))
becomes
INDEX($D$3:$D$7, 2)
and returns
"Apple" in cell F3.
If an error had been returned the IFERROR function then continues with the second part of the formula. It works just the same as the first part, however, it returns values based on List1.
The following image shows what the two parts return, the first part of the formula in column H and the second part in column J.
14.4 Get Excel *.xlsx file
sort-from-a-to-z-from-two-columns.xlsx
15. Sort text from multiple cell ranges combined (user defined function)
This user defined function allows you to enter up to 255 arguments or cell ranges. The udf combines all values from all cell ranges and then sorts them from A to Z. It uses a bubblesort algorithm and I don't recommend using large data sets.
Array formula in cell F2:F12:
=SortMultipleRanges(A2:B3,C4:C6,D8:D9)
15.1 VBA Code
'Name function and specify parameters Function SortMultipleRanges(ParamArray rng() As Variant) 'Dimension variables and declare data types Dim temp() As Variant 'Iterate through each cell range in parameter rng For Each cellrange In rng 'Count cells in cell range and add number to variable i i = i + cellrange.Cells.CountLarge 'Continue with next cell range Next cellrange 'Redimension array variable temp using variable i ReDim temp(1 To i, 1 To 1) 'Save 0 (zero) to variable i i = 0 'Iterate through each cell range in parameter rng For Each cellrange In rng 'Iterate through each cell in cell range For Each cell In cellrange 'Add 1 to variable i i = i + 1 'Save value in cell to array variable temp if value is not empty If cell <> "" Then temp(i, 1) = cell 'Continue with next cell Next cell 'Continue with next cell range Next cellrange 'Sort values in array variable temp using the sort function below SortMultipleRanges = BubbleSort(temp) End Function
'Name function and specify parameters Function BubbleSort(str As Variant) 'Dimension variables and declare data types Dim tmp As String, c As Integer, temp As String Dim a As Long, b As Long 'Create a for ... next loop based on the number of values in parameter str For a = LBound(str, 1) To UBound(str, 1) 'Create a for ... next loop from number in variable a plus 1 to the number of values in parameter str For b = a + 1 To UBound(str, 1) 'Check if array value is larger than another array value based on container variable a and b If str(a, 1) > str(b, 1) Then 'Save value str(a, 1) to variable tmp tmp = str(a, 1) 'Save value str(b, 1) to value str(a, 1) str(a, 1) = str(b, 1) 'Save value tmp to str(a, 1) str(b, 1) = tmp End If 'Continue with next number Next b 'Continue with next number Next a 'Return sorted values BubbleSort = str End Function
15.3 Get excel *.xlsm file
Combine merge category
This article demonstrates techniques on how to merge or combine two data sets using a condition. The top left data […]
Excel categories
158 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.
This article is terrific. Thanks so much for posting this solution!
I do have one question:
Let's say my "List 1" is auto updated and the number of entries in this list will fluctuate. Since the number of entries fluctuates, I would like to select a larger range than I actually have data in currently. The issue is when I make my "List 1" larger than the number of entries, the rows that don't currently have data in them, show up on my combined list as zeros.
So my question is, is there a way to adjust the formula so that when it looks at "List 1" for example, it skips over blank cells and continues to combine the list with "List 2".
Dan,
See this post: https://www.get-digital-help.com/2010/05/18/merge-two-columns-with-possible-blank-cells-in-excel-formula/
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/
Have I missed something? I found the formula listed above worked just as well *without* entering it as an array formula.
Excel solution either way.
typo... *excellent solution either way
Thanks!!
=IFERROR(INDEX(List1, SMALL(IF(ISBLANK(List1), "", ROW(List1)-MIN(ROW(List1))+1), ROW(A1))), IFERROR(INDEX(List2, SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A1)-SUMPRODUCT(--NOT((ISBLANK(List1)))))), ""))
please same for office 2003
Prash,
Excel 2003, array formula in C2:
=IF(ISERROR(INDEX(List1, SMALL(IF(ISBLANK(List1), "", ROW(List1)-MIN(ROW(List1))+1), ROW(A1)))), IF(ISERROR(INDEX(List2, SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A1)-SUMPRODUCT(--NOT((ISBLANK(List1))))))), "", INDEX(List2, SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A1)-SUMPRODUCT(--NOT((ISBLANK(List1))))))), INDEX(List1, SMALL(IF(ISBLANK(List1), "", ROW(List1)-MIN(ROW(List1))+1), ROW(A1)))) + CTRL + SHIFT + ENTER
Copy cell C2 and paste it down as far as needed.
Thanks A LOT!!! I really mean it. Lifesavers you guys are!
Could you please tell me what I need to do to add additional Lists? I'm trying to get 3 lists merged into a single list with no blanks for earlier versions of Excel. Thanks
Shawna,
The array formula would be ridiculously large, too large I think.
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!)
I was hoping to be able to add additional lists as well with the ability to remove the spaces. It's not to bad if you leave the spaces between the end of one list and the start of the next in.
=IFERROR(INDEX(List1,ROWS(V$2:V2)),IFERROR(INDEX(List2,ROWS(V$2:V2)-ROWS(List1)),IFERROR(INDEX(List3,ROWS(V$2:V2)-ROWS(List1)-ROWS(Lis2)),"")))
Instead I think I will just do a simply =cell in a new worksheet and than simply sort it.
Denis,
Thanks for a reply...if my data are in columns, do I simply change the formula to read "columns"?
Here is my dilemma: I work in a forensic lab and we typically have samples that undergo different procedures, culminating in a final procedure they all undergo. I am trying to compile a Total Sample List (which combines all of the samples from other lists). The data from the 'Total Sample List' will be drawn into MS Word to populate the forms we use according to sample type (that much I've already figured out). I'm a lab geek, not a computer geek UNFORTUNATELY!
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!!
Well, honestly I would have to say just give it a try. You could look at it as an experiment :)
I'm by no means an expert in excel. I spent a couple hours just modifying the above formula from one that was posted on this website prior.
I've been trying to create something close to this for a long time now for budgeting and cashflow of my personal finances.
-Good Luck!
Shawna,
I hope this user defined function works in excel 2003:
Function MergeRanges(ParamArray arguments() As Variant) As Variant() Dim cell As Range, temp() As Variant ReDim temp(0) For Each argument In arguments For Each cell In argument If cell <> "" Then temp(UBound(temp)) = cell ReDim Preserve temp(UBound(temp) + 1) End If Next cell Next argument ReDim Preserve temp(UBound(temp) - 1) MergeRanges = Application.Transpose(temp) End FunctionWhere 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
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?
Hi,I have little experience with excel.Could someone point out how to run this array formular? I entered the formular into the cell but it didn't give me the list.
Btw,I am using a mac.And the formular doesn't seem to work with openoffice on my linux anyway.
Jane,
It looks like IFERROR function and ISERROR function don´t exist in OpenOffice.
Oscar,
Thanks!If I want to merge multiple,say 5,columns in the same manner,how should I change the parameters then?Thanks and I am using excel now.
Try this udf:Combine cell ranges into a single range while eliminating blanks
is there a way to merge two columns of dates in this similar way but only combine the dates that appear on both columns? ie. without duplicating them in the newly created column?
macutan,
Array formula in cell D3:
Excel file
merge-two-columns-return unique distinct values.xlsx
(Excel 2007 Workbook *.xlsx)
Thank you Oscar for your reply,... I am using Excel 2003, which is probably why I am getting #NAME? in the cells where the merged list is supposed to appear... Any ideas as to how can i get this working in my excel version?
Also please note that as per list1 and list2 shown above, i would be looking for the merged list to have ONLY duplicate dates in it (as in dates that appear in both list1 and list 2) so in this case the merged list would display (also if possible in descending or ascending order) 2011-01-01 as that is the date that is in both lists.
Any guidance will be greatly appreaciated
Rgds
macutan
macutan
Array formula:
Read this post: How to find common values from two lists
Thank you Oscar, I have just tried to run your formula in an array but i am getting 2011-01-01 repeated until the end of the array instead of what you are seeing on the Merged list.
also, do you know how to modify the final array formula so that the dates come out descending.
macutan
Filter common dates from two columns and return unique distinct dates sorted smallest to largest
Array formula in cell D3:
How to create an array formula
Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
Press and hold Ctrl + Shift.
Press Enter once.
Release all keys.
Get the Excel file
sort-common-dates-from-two-columns.xls
Excel 97-2003 *.xls
Thanks a lot Oscar!!
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 file
stephen.xls
I've never used the INDEX function before and this is a great use for it. My last puzzle is to automate the length of the merge column. Been trying to do with with a dynamic table height but no luck. anyone got ideas?
Cheers
Windfery,
I am not sure I understand, automate the length of the merge column?
You want the named ranges to expand whenever new values are added, right?
Windfery,
I updated this blog post.
Hi oscar,
Is possible to make a dropdown list as below?
[IMG]https://i65.photobucket.com/albums/h232/alien3660/dropdown.gif[/IMG]
condition:
1,two column of data
2,remove blank cells
3,only show one time of duplicated data
thank & best rgds,
senen
Senen,
Open attached file:
drop-down-list-from-two-columns-and-no-blanks.xlsx
Hi Oscar,
Thanks for ur reply,can u make it in .xls format b'cos i can't open .xlsx file in my pc...
sry for the inconvenience.
Thank & best rgds,
senen
Senen,
Try this file:
how-to-extract-a-unique-distinct-list-from-two-columns-in-excel-2003.xls
Can this work, with the same dataset specified above, if the lists are defined :
List1 (A2:A10)
List2 (B2:B10)
instead of
List1 (A2:A7)
List2 (B2:B5)
that is, with trailing blank cells in the list. In my particular example, my lists will be filled top down, with any blank cells at the end, and i do know the maximum number of cells i could possibly have, if that helps.
Cause when i try this, i only get details from the first list.
Thanks
Graeme
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?
Hey Oscar!!
Many thanks for sharing your excel knowledge...Ive been trying to figure this out for a while!...
I copied the formula into my worksheet (Im using excel 2003), but (and I cant see why), but the formula has only copied the first named range column (GN_LIST in column H) into the merged column (column M).
If I change the very last range name to the second list name (SH_LIST in column L), then the second column only is entered into the merged column.
My formula is as follows:
=IF(ISERROR(INDEX(GN_LIST, ROWS($M$1:M2))), IF(ISERROR(INDEX(SH_LIST, ROWS($M$1:M2)-ROWS(GN_LIST))), "", INDEX(SH_LIST, ROWS($M$1:M2)-ROWS(GN_LIST))), INDEX(GN_LIST, ROWS($M$1:M2)))
Can you PLEASE help?!? I am baffled...
Regards
I have resolved this now! Originally I wanted to define two separate columns as the two ranges as the data being impoted into them varies month by month i.e the data is dynamic. Through experimentation and testing, I discovered that your formula only works for statically defined ranges. It would be great to be able to adapt your formula to be used with dynamic ranges, but that is, sadly, beyond my excel scope and knowledge. If you are able to provide this, I would be deeply grateful, but if you are not, then I will use my work-around. Many thanks.
Dan,
Get the Excel 2003 *.xls file
merge-two-columns-dynamic-ranges1.xls
Received with many thanks!!!
I noticed that you have limited the height definition for the range to 10,000 cells. Is there any particular reason for this (ie. slower processing when more cells are included in the range), or can the height theoretically be increased down to cell 65,536?
Dan,
There is no partcular reason, it can be increased down to cell 65,536.
Thanks for commenting!
here is a version i used that auto counts the columns so you don't have to have your "list1" range = the range. I used this to combine multiple columns. Please mind that i didn't name my first indexranges which wouuld have simplified alot
=IFERROR(INDEX($AL$2:$AL$26, ROWS(BW$1:$BW15)), IFERROR(INDEX($AU$2:$AU$26, ROWS(BW$1:$BW15)-COUNT($AL$2:$AL$26)), IFERROR(INDEX($BA$2:$BA$26, ROWS(BW$1:$BW15)-SUM(COUNT($AL$2:$AL$26), COUNT($AU$2:$AU$26))), IFERROR(INDEX($BH$2:$BH$26, ROWS(BW$1:$BW15)-SUM(COUNT($AL$2:$AL$26), COUNT($AU$2:$AU$26), COUNT($BA$2:$BA$26))), IFERROR(INDEX($BO$2:$BO$26, ROWS(BW$1:$BW15)-SUM(COUNT($AL$2:$AL$26), COUNT($AU$2:$AU$26), COUNT($BA$2:$BA$26), COUNT($BH$2:$BH$26))), "")))))
Hey Oscar,
i am trying to merge 4 columns into one list but i think i have run into an issue. Can you help? I have already substitued my named ranges. One thing i have to note is that these named ranges are created using the following formula =OFFSET(References!$N$1,1,0,COUNTA(References!$N:$N)-1,1) so that i can use my named ranges in a dropdown box with data validation. These named ranges are always being updated so i dont want to have to continually change the range of the named reference. Let me know if you can help.
thanks JOEY
=IFERROR(INDEX(Phatec_Local_DIDs, ROWS(AM2:$AM$2)), IFERROR(INDEX(Phatec_Toll_Frees, ROWS(AM2:$AM$2)-ROWS(Phatec_Local_DIDs)), IFERROR(INDEX(Comcast_Local, ROWS(AM2:$AM$2)-ROWS(Phatec_Local_DIDs)-ROWS(Phatec_Toll_Frees)), IFERROR(INDEX(Comcast_Toll_Free, ROWS(AM2:$AM$2)-ROWS(Phatec_Local_DIDs)-ROWS(Phatec_Toll_Frees)-ROWS(ComCast_Local)),"")))) + CTRL + SHIFT + ENTER
one other thing that maybe you can help me with? i was able to use your two column method for combining two named ranges. Works great. The only issue now is when i go to sort the list it only sorts list one then sorts list two in the same column. Not sure if i explained that right but below is what i am experiencing.
List one:
216
220
221
223
305
311
314
315
360
361
501
505
511
List two
200
202
244
250
320
350
399
400
400
401
402
403
449
499
514
550
557
590
598
599
600
601
607
666
After using the sort feature it shows like this? each of the cells are in a table. Its like it sorts list one first then after sorting list one it sorts list two?
216
220
221
223
305
311
314
315
360
361
501
505
511
200
202
244
250
320
350
399
400
400
401
402
403
449
499
514
550
557
590
598
599
600
601
607
666
Joey,
Question 1:
You can see the logic behind this formula:
Merge three columns into one list in excel
and then adapt it to your formula.
Joey,
Question 2:
I recommend converting the formulas to values before sorting.
1. Select range
2. Copy (Ctrl + c)
3. Press with right mouse button on the same range.
4. Press with left mouse button on "Paste Special.."
5. Press with left mouse button on "Values"
6. Press with left mouse button on OK
This article is great.
Exactly what I needed.
Thanks so much for posting.
;-)
Thanks for commenting!
This merging partially works for me, but instead of ordering the values on sheet 2 from smallest to largest, I need to keep the original order for sheet 1, for example instead of:
apple: 8 10 10 10 11 11 11 12 14
keep the original order from sheet 1 like this:
apple: 10 11 8 11 11 12 14 10 10
how can I change the formula(s) to do it?
Thanks!!
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...
shomyx,
Great question! I can´t do it with array formulas, really complicated.
Instead I created a user defined function. See new content above!
BatTodor,
Thanks for sharing!
Great article. I have to combine 200 columns into one list. I know. I tried steps from 'Combine cell ranges into a single range while eliminating blanks' UDF, but looks like typing the formula itself is going to be a big deal. Any advice? (To give a bit of a background, I am trying to compare 200 columns to one column of data and figured it would be easier if I combine all 200 into one column and then compare, it would be easy).
Jinesh,
You want to know how to simplify/automate typing 200 column ranges in a udf?
Good question! I don´t know but I believe a macro should be able to return addresses of cell ranges populated with values.
Read this post:
Extract cell references from all cell ranges populated with values in a sheet
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...
Function MergeRanges(ParamArray Arguments() As Variant) As Variant() Dim X As Long, Index As Long, Cell As Variant, TempArray As Variant, Done As Boolean ReDim TempArray(1 To Application.Caller.Count) Index = 1 For X = LBound(Arguments) To UBound(Arguments) For Each Cell In Arguments(X) If Len(Cell.Value) Then TempArray(Index) = Cell.Value Index = Index + 1 If Index > UBound(TempArray) - LBound(TempArray) + 1 Then Done = True Exit For End If End If Next If Done Then Exit For Next For X = Index To UBound(TempArray) TempArray(X) = "" Next MergeRanges = Application.Transpose(TempArray) End FunctionRick 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,
[...] [...]
Hi All,
I hope you are able to help me, must admit I don't do a whole lot with excel in my day to day role so my skills are really limited. The above formula functions OK in my workbook and combines two named ranges together into a single list with no 0's :) so all good.
My question is how would I add a third list, fourth list etc. etc to the formula ?
I'm attempting to set up one list that combines data from several other lists.
Thanks very much
Nathan
I have all 13 lists combined now using the below formula, now just struggling to add in the "Remove Balnks" piece, My application only allows imports from xlsx and not xlsm so I cannot do it the easy way (with a vb macro)
also the application will not recognize the file if it's been renamed :(
Formula
=IFERROR(INDEX(List1,ROWS(V$2:V2)),
IFERROR(INDEX(List2,ROWS(V$2:V2)-ROWS(List1)),
IFERROR(INDEX(List3,ROWS(V$2:V2)-ROWS(List1)-ROWS(List2)),
IFERROR(INDEX(List4,ROWS(V$2:V2)-ROWS(List1)-ROWS(List2)-ROWS(List3)),
IFERROR(INDEX(List5,ROWS(V$2:V2)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)),
IFERROR(INDEX(List6,ROWS(V$2:V2)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)-ROWS(List5)),
IFERROR(INDEX(List7,ROWS(V$2:V2)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)-ROWS(List5)-ROWS(List6)),
IFERROR(INDEX(List8,ROWS(V$2:V2)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)-ROWS(List5)-ROWS(List6)-ROWS(List7)),
IFERROR(INDEX(List9,ROWS(V$2:V2)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)-ROWS(List5)-ROWS(List6)-ROWS(List7)-ROWS(List8)),
IFERROR(INDEX(List10,ROWS(V$2:V2)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)-ROWS(List5)-ROWS(List6)-ROWS(List7)-ROWS(List8)-ROWS(List9)),
IFERROR(INDEX(List11,ROWS(V$2:V2)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)-ROWS(List5)-ROWS(List6)-ROWS(List7)-ROWS(List8)-ROWS(List9)-ROWS(List10)),
IFERROR(INDEX(List12,ROWS(V$2:V2)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)-ROWS(List5)-ROWS(List6)-ROWS(List7)-ROWS(List8)-ROWS(List9)-ROWS(List10)-ROWS
(List11)),
IFERROR(INDEX(List13,ROWS(V$2:V2)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)-ROWS(List5)-ROWS(List6)-ROWS(List7)-ROWS(List8)-ROWS(List9)-ROWS(List10)-ROWS
(List11)-ROWS(List12)),""))
Thanks
Nathan
I think I have this sorted now, I have used dynamic named ranges for List1 --> List13, in the refers to formula is:-
=OFFSET(Sheet1!$A$2,0,0,MATCH("*",Sheet1!$A:$A,-1),1)
this formula seems to work OK in as much that it does not include the column heading "Cell A1" but for some reason it is adding one row to the end?
so In my drop down I see:-
Value - sheet 1
Value - sheet 1
0
Value - sheet 2
0
Value - sheet 3
Value - sheet 3
0
looking at the named range I can see it's selecting one row more than there are values - can anyone explain why it's doing this ? I am sure it's a simple fix
Thanks
Nathan
Nathan,
try this named range:
=OFFSET(Sheet1!$A$2,0,0,MATCH("*",Sheet1!$A:$A,-1)-1,1)
Hi Oscar,
Thanks for getting back to me, I got this working using :-
=IFERROR(INDEX(Servers!A:A,AGGREGATE(15,6,(ROW(Servers!A:A)-ROW(Servers!A3)+1)/(Servers!A:A""),ROWS(C$1:C2))),"")
for each of the 13 lists and then used these ranges to make the drop down
Thanks
Nate
Hi Nathan,
I've been looking all over the internet for your solution to combining multiple columns that remove blanks into 1 cell. Can you please post an example file?
Thanks,
Andy
It works perfectly - Thanks Oscar!
Oscar,
To modify the plan above and use non-binary values (e.g. use a list that includes quantities or prices for the table values (e.g. 100, 45, 12), would we have to move from SUMPRODUCT back to the INDEX model?
Nevermind - got it.
Setting up the B9 construct in the following manner would add up all of the values that correspond to the "V1" (etc) column value and place them in a given spot.
=IF(SUMPRODUCT(($A9=$A$2:$A$5)*(B$8=$B$1:$F$1)*($B$2:$F$5>0))>0,sum(($A9=$A$2:$A$5)*(B$8=$B$1:$F$1)*($B$2:$F$5=1)),"")
Mike,
This array formula seems also to work:
=SUMPRODUCT(($A9=$A$2:$A$5)*(B$8=$B$1:$F$1)*$B$2:$F$5)
Thanks Oscar - that's what I ultimately used to avoid a linearity issue. I just forgot to repost.
Thanks for your help!
I’m building an S-Curve Chart from disaster Condition Excel Spread sheet, that should reflect in the end: Plan and forecast weekly bars info and plan and forecast cumulative progress curves.
Take Plan Date Column & Using Pivot Table Fields: Row Labels and Values (count) I get an output such as:
Columns: a – Plan Date, b – linked value
Repeat above for Forecast Date using same Pivot Table Fields:
Columns: c – Forecast Date, d – linked value
Now I have 4 Columns: (a) Date + (b) linked value / (c) Date + (d) linked value.
Challenge, as I see it:
Compare dates columns a and c, c and a, find unique dates, somehow combine them into one column, but somehow keep linked value associates with each date:
So in the end I would get:
Column A – Date (for both Plan and Forecast)
Column B – Linked to Plan Date Value (if date is related to forecast value, then put 0 in plan column B for this row)
Column C – Linked to Forecast Date Value (if date is related to Plan value, then put 0 in forecast column C for this row)
So Ultimate goal is to have this database ready for a chart.
I’m sure there are various ways of manually doing it, but I also predict there must be a way of doing it all through Macro.
I have zero marco experience, so came to this forum with desperate need for a help. Please.
Oscar
thank you so much, it is just wonderful tricks you show here, now i can get rid of that macro in my excel dashboard.
thank you again and happy new year
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!
[...] all, Please first have a look at this link so that you may follow me: Merge two columns into one list in excel | Get Digital Help - Microsoft Excel resource I would like to combine List1 and List2 into a 3rd named range called List3. I was wondering if [...]
Dear Oscar,
I need to have both functions of columns combine (Skip banks + Distinct Values).
Please help.
Regards,
Alaa Abdullnabi,
See this file:
how-to-extract-a-unique-list-from-two-columns-in-excel-2007-no-blanks.xlsx
GREATTTTTT, REALLY MANY MANY THANKS... YOU ARE ONE OF THE BEST.
Hi Oscar,
I'm trying to combine Column "A" values from multiple sheets (within same workbook) in condition that column "C" contain specific word/value e.g. "Good" within the same sheets. It's like filtering multiple sheets in one where specific value/ word is there.
Your help is appreciated.
Thanks
Alaa
Alaa Abdullnabi,
Get the Excel *.xlsx file
merge-two-columns-with-blanks-with-condition.xlsx
An alternative , and probably simpler approach has been presented by Sajan Thomas in the Chandoo.org forum , here :
https://chandoo.org/forums/topic/how-to-transpose-multiple-rows-into-one-column-without-0-in-excel
NARAYAN,
Yes, it is simpler but the solution presented here can transpose two non-contiguous columns.
Thanks for commenting!
From the code listed, how does sending the sum of multiple COUNTIFs as 2nd parameter to MATCH() supposed to accomplish anything? MATCH() expects an array, not a value, for its 2nd parameter.
Anonymous,
The COUNTIFS functions return arrays in this formula.
Hi Oscar
Hope you are well!...
I have not asked for your assistance since January of last year because, after your invaluable help, I was able to complete the task I was working on.
I am now re-visiting the work as amendments need to be made and I find myself stuck again, as I have not Excel so indepth since.
Maybe you can help...
I asked you about merging to columns of dynamic data with possible blanks into one sorted column and you were able to help me do this.
An example of one such equation is provided below (I'm using 2003):
=IF(ISERROR(INDEX(ListGN, ROWS($O$1:O2))), IF(ISERROR(INDEX(ListSH, ROWS($O$1:O2)-ROWS(ListGN))), "", INDEX(ListSH, ROWS($O$1:O2)-ROWS(ListGN))), INDEX(ListGN, ROWS($O$1:O2)))
I now need to add a third column to this equation, and although I have a rough idea of what to do, I was hoping you could help me out as I'm rusty.
Many thanks!!
Dan,
Merge three columns into one list
Hi Oscar!
Many thanks for getting back to me. I had already seen this page but its not giving me what I want in the way as the previous expression posted above.
I have definied the third dynamic range, ListARGN
=OFFSET(core_calculations!$N$2,0,0,COUNTA(core_calculations!$N$2:$N$65536),1)
but am finding it quite difficult to logically workout how to expand the existing equation to take into account the new column of numbers.
I suppose an alternatiove would be to add another column to then merge and sort the first merged/sorted column with the new column. It should produce the same result but is not as tidy...
Can you assist at all? It would be a massive help!
Regards
Hi Oscar again!
I have just worked out what I was doing before and must admit that I got myself into a pickle! (I said I was rusty - haha)!
So, I have taken another look at your merge three columns into one list example and got things working correctly!
So, I must thank you again for this amazing website and your wonderful assistance!
Kindest regardss
Dan, can you explain your logic for the additional column? I have a total of 5 columns I need to convert into one list. Each column increases by time so they have to be individual columns. I am able to get the 3 columns to merge into one. Thank you very much!!!!!!!!!!!!!!!!!
Hi Oscar,
This is a great thread! I am having some trouble. I want to combine 2 lists into one list. I want only one record of duplicates and I want the unique items from both lists to be returned as well. The formula above is only returning me the first value from List 1 all the way down the column.
Thanks!
Jamie
Jamie,
The formula above is only returning me the first value from List 1 all the way down the column.
Make sure you entered the formula as an array formula. Also check your cell references, they might be wrong.
Did you provide formula to sort andmerged between 2 or 3 list?
Thanks Oscar, great website, much learn from you....
How about 3 Columns? Merged and Sort them?
Sami
Sami,
I recommend using a custom function:
Merge,sort and remove blanks from multiple cell ranges
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 have a feeder list that creates 2 list, I have implemented your forumula and it works well, however the blanks in my list are created as spaces because a formula is returning a blank, is there any way I can tailor your formula to accept this condition and eliminate it from the final list, at the moment it is putting the "blanks into the combined list".
Cheers
JD
Hi John,
I have aexactly the same problem, did you find a way around it i.e. to ignore blanks that contain formulas? If so, do you have the formula?
Thanks
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.
Using formula above to combine 2 data tables on separate sheets into a new sheet in one work book. I made sure the formula is correct and entered as array.
{=IFERROR(INDEX(Table_Query_from_QuickBooks_Data[[#Headers],[Name]], ROWS(A1:$A$1)), IFERROR(INDEX(Table_Query_from_QuickBooks_Data9[Name], ROWS(A1:$A$1)-ROWS(Table_Query_from_QuickBooks_Data[[#Headers],[Name]])), ""))}
It is only returning the data from table 1.
Does it have to be one table?
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,
Need help :(
I have 10 sheets and all are the same format for each user to work on. column A list the order# which left alot of blank row at the bottom as we need to add more data day by days, all order# are difference for all sheets. and I have a master sheet. I want to combine all the order# which short from the 10 sheets ignore blank. please, avoiding VBA code because I know very little about it.
Thanks and best regard,
Rath
Rath,
Sorry, I don´t know using formulas but my add-in might be helpful:
https://www.get-digital-help.com/2010/02/28/combine-data-from-multiple-sheets-in-excel/#mergeranges
Unsure why this formula isn't removing blanks..
=ArrayFormula(IFERROR(INDEX(A$3:A$20, SMALL(IF(ISBLANK(A$3:A$20), "", ROW(A$3:A$20)-MIN(ROW(A$3:A$20))+1), ROW(A1))), IFERROR(INDEX(C$3:C$20, SMALL(IF(ISBLANK(C$3:C$20), "", ROW(C$3:C$20)-MIN(ROW(C$3:C$20))+1), ROW(A1)-SUMPRODUCT(--NOT((ISBLANK(A$3:A$20)))))), "")))
Anyone able to shed some light?
Hi, this formula worked for me. It is consolidating comments made on other worksheets. Now I would like to add the name of the person who made the comment. How can I do that? for example, The name is located on A2 on each corresponding sheet.
IFERROR(IFERROR(INDEX(Additives,MATCH(0,IF(ISBLANK(Additives),1,COUNTIF($B$1:B100,Additives)),0)),INDEX(AdminBuilding,MATCH(0,IF(ISBLANK(AdminBuilding),1,COUNTIF($B$1:B100,AdminBuilding)),0))),"")
Please help me with this problem. I am trying to make a sheet of premier leauge points and try to get the result of winners for each week. And I came across with a problem that two people have equal points and excell won't work for two values. Can anybody help me? Thank you for your kindness.
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
Does the original formula have a version using IF(ISERROR), using it with OO Calc?
I have a problem how to combine and sort 2 columns with blanks? Is that possible with formula? No UDF or VBA?
thanks in advance
Sami
I made a udf:
https://www.get-digital-help.com/sort-text-cells-alphabetically-from-two-columns-using-excel-array-formula/#sortudf
This is great!! Thanks for sharing :) Is there a way to account for "Blank" cells that contain a formula? The cells do not contain data, so I'd like to skip them, but since we use the ISBLANK function, they are not registering as Blank...?
Thank you!!!
Dana,
Yes, it is possible:
How can I merge 5 columns?
thanks
anthony,
The easiest way to go is to use a simple user defined function:
Function MergeRanges(ParamArray arguments() As Variant) As Variant() Dim cell As Range, All As String For Each argument In arguments For Each cell In argument All = All & cell & "," Next cell Next argument MergeRanges = Application.Transpose(Split(All, ",")) End FunctionWhere 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
Oscar, your formula for merging two columns into one, works great! However, I am dealing with dates. I want to sort range to oldest to newest. I tried re-formatting and it seems to not work. Your comments or suggestions would be greatly appreciated. Thank you in advance -Carl
Carl Carter,
I don't know how to do that with a formula or array formula. It is possible with a User Defined Function.
Carl Carter
The UDF found on this webpage lets you merge and sort values from multiple cell ranges:
https://www.get-digital-help.com/sort-text-cells-alphabetically-from-two-columns-using-excel-array-formula/#sortudf
sir, How to Merge four columns with possible blank cells. please give me example. Its very urgent. My email id is [email protected].
Thanks for your article, it inspired me to come up with the following two formulas. The first combines two columns of numbers into one without having to first define the length of both Array ranges or use the IFERROR. Assuming that the two list of numbers are in columns A and B,
=IF(ROWS($1:1)>COUNT($A:$A,$B:$B),"",IF(ISNUMBER($A2),$A2,INDEX($B:$B,ROW()-COUNT($A:$A))))
The crux of the formula is "IF(ISNUMBER($A2),$A2,INDEX($B:$B,ROW()-COUNT($A:$A))))". The beginning "IF(ROWS($1:1)>COUNT($A:$A,$B:$B)" returns a blank cell if the row number is greater than the combined count of both arrays, so that the formula can be copied down as far as one likes.
A simpler solution, if one wants to have the combined list sorted is the following:
=IF(ROWS($1:1)>COUNT($A:$A,$B:$B),"",SMALL(($A:$A,$B:$B),ROWS($1:1)))
Using nested brackets within the SMALL function, more than one array can be combined together. The added advantage of this formula is that it can handle blank cells or even cells with text within the desired list of numbers. Thanks again, Sam
Sam,
Thank you for your comment.
SAM the ****** genius
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.
Problem if instead of blank cell will be formula or =""
what solution for this ?
How do I use this method on 6 columns/lists?
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
my table
roll SUBJECT Th Pr Tot
1 BIOS 65 30 95
1 CHEM 65 30 95
1 PHYS 65 30 95
1 MATH 74 20 94
2 BIOS 63 30 93
2 CHEM 63 30 93
2 PHYS 63 30 93
3 BIOS 59 30 89
3 CHEM 59 30 89
3 NUTN 59 30 89
3 GEGR 59 30 89
I want result as like below to make student marksheet
roll s1 s1 th s1 pr s1 tot s2 s2 th s2 pr s2 tot s3 s3 th s3 pr s3 tot s4 s4 th s4 pr s4 tot
1 BIOS 65 30 95 CHEM 65 30 95 PHYS 65 30 95 MATH 74 20 94
2 BIOS 63 30 93 CHEM 63 30 93 PHYS 63 30 93
3 BIOS 59 30 89 CHEM 59 30 89 NUTN 59 30 89 GEGR 59 30 89
What would have to be added to the end of your formula to add 3rd dynamic range list into the merged column?
=IFERROR(INDEX(List1, SMALL(IF(ISBLANK(List1), "", ROW(List1)-MIN(ROW(List1))+1), ROW(A1))), IFERROR(INDEX(List2, SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A1)-SUMPRODUCT(--NOT((ISBLANK(List1)))))), ""))
Nevermind, figured it out for myself.
=IFERROR(INDEX(B$1:B$22, SMALL(IF(ISBLANK(B$1:B$22), "", ROW(B$1:B$22)-MIN(ROW(B$1:B$22))+1), ROW(A1))), IFERROR(INDEX(C$1:C$22, SMALL(IF(ISBLANK(C$1:C$22), "", ROW(C$1:C$22)-MIN(ROW(C$1:C$22))+1), ROW(A1)-SUMPRODUCT(--NOT((ISBLANK(B$1:B$22)))))), IFERROR(INDEX(D$1:D$22, SMALL(IF(ISBLANK(D$1:D$22), "", ROW(D$1:D$22)-MIN(ROW(D$1:D$22))+1),ROW(A1)-SUMPRODUCT(--NOT((ISBLANK(B$1:C$22)))))),"")))
Michael,
thank you for posting the solution. I have added an Excel 365 formula to this article, it is much easier to adjust the formula to include more cell ranges.
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!
https://postimg.cc/5j5gFpJS
Hello,
Im a newbie, and I have problem when combining date into single column from two different columns. Each of these columns contain blanks & duplicate dates, which occurs on both columns. I've tried to paste a closest formula for this problem, but it only prioritize a duplicate instead of sorted numbers (date). Is there any suggestion for this? Thank you.