Monthly Archives: August 2017

#Excel Super Links #135 – shared by David Hager


Power BI – Using Change tables to compare data

Extracting images and themes from Power BI Desktop files

Calculating the Number of Unique Items in a Delimited String

Excel Games

Creating A Unique Delimited String From a Delimited String – Excel Formula Method With TEXTJOIN


#Excel Super Links #134 – shared by David Hager


Excel Magic Trick 1456: PivotTable & Slicer to Create 8 Year Sales Report by Product & Month


New Customers per Day – DAX Technique

Dynamic Conditional Formatting



#Excel Super Links #133 – shared by David Hager


Extract duplicate values with exceptions

Top Excel Formulas & Function Examples To Get Better At Microsoft Excel

Tom’s Tutorials For Excel: Returning a Formula’s Arguments

Generating a series of numbers in DAX

#Excel Impossibly Easy #1: Return a 1D Array from Non-Contiguous Native 3D Ranges


#Excel Impossibly Easy #1: Return a 1D Array from Non-Contiguous Native 3D Ranges


What if I told you that I had 2 non-contiguous 3D ranges in an Excel workbook and I wanted to return a single 1D array from those ranges. Impossible, right? No, it turns out that it is “easy”.

Prior to the introduction of the TEXTJOIN function, this would likely have been impossible. But, this function accepts native 3D ranges as range arguments. See:

It would have been nice if the technique was only a VBA solution, but although Textjoin is a VBA worksheet function in Excel, VBA will not accept a native 3D range as an argument. Likewise, a pure Excel formula solution would have been nice, and a method dows exist to do this

but it has severe limitations which prevents its use with a relatively large number of cells (maybe 150). The total number of characters that a cell can contain is 32,767 characters. This solution assumes that an average of 6 characters per cell plus a comma for each gives an approximate number of 4500 cells allowed.

Here is the solution.


It consists of the TEXTJOIN worksheet function with 2 non-contiguous 3D ranges arguments


And a simple VBA function which converts a comma delimited string into a 1D array.

Function ArrayFromCDS(MyString As String)

ArrayFromCDS = Split(MyString, “,”)

End Function

In the example file, the array produced contains all of the elements of the two 3D ranges (shown below).


The figure shows this formula in cell I2.


I hope that you will find this useful.

The example file can be downloaded here.


#Excel Super Links #132 – shared by David Hager


Excel CONVERT Function With Drop Down Lists

How to import from Excel with cell coordinates in Power Query and Power BI

Excel Range Modifiers

How to refer to the next row in Power Query

Using the SUBTOTAL Function in #Excel To Aggregate Values From Multiple Filtered Lists

#Excel Super Links #131 – shared by David Hager


Excel Formulas Based Sudoku Solver

Awesome chart to visualize Salary Increases for 3,500+ people [Tutorial]

Converting a Formula to Values

Changing the culture of a Power BI Desktop file

Documenting Formulas in an #Excel Range Using the FORMULATEXT, ISFORMULA and TEXTJOIN Functions by David Hager

#Excel Super Links #130 – shared by David Hager


Tom’s Tutorials For Excel: Counting All Data Types in a Range

What is Solver in Excel?

Working With Range.FormulaArray In VBA

15 Quick & Powerful ways to analyze Business Data

Extracting Ingredients from an #Excel List by Cost Category by David Hager