Author Archives: David Hager

About David Hager

Retired. Now very active in Excel community. Contact me with job opportunities.

#Excel Super Links #135 – shared by David Hager

 

Power BI – Using Change tables to compare data

https://www.fourmoo.com/2017/08/22/power-bi-using-change-tables-to-compare-data/

Extracting images and themes from Power BI Desktop files

https://xxlbi.com/blog/extracting-images-and-themes-from-power-bi-desktop-files/

Calculating the Number of Unique Items in a Delimited String

http://dailydoseofexcel.com/archives/2013/08/07/calculating-the-number-of-unique-items-in-a-delimited-string/

Excel Games

http://www.cpearson.com/Excel/games.htm?2

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

https://dhexcel1.wordpress.com/2017/01/03/creating-a-unique-delimited-string-from-a-delimited-string-excel-formula-method-by-david-hager/

 

#Excel Super Links #134 – shared by David Hager

 

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

https://www.youtube.com/watch?v=U4iJyHr6tWI

New Functions – GENERATESERIES, SELECTEDVALUE

https://exceleratorbi.com.au/new-functions-generateseries-selectedvalue/

New Customers per Day – DAX Technique

https://powerpivotpro.com/2013/01/new-customers-per-day-technique-by-david-hager/

Dynamic Conditional Formatting

https://powerpivotpro.com/2012/11/david-hager-on-dynamic-conditional-formatting/

CREATING AN Nth OCCURRENCE DAX FORMULA

https://powerpivotpro.com/2011/03/guest-post-nth-occurrence-dax-formula/

 

#Excel Super Links #133 – shared by David Hager

 

Extract duplicate values with exceptions

https://www.get-digital-help.com/2017/08/19/extract-duplicate-values-with-exceptions/

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

https://www.myexcelonline.com/blog/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel/

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

https://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-returning-a-formulas-arguments/

Generating a series of numbers in DAX

http://www.sqlbi.com/articles/generating-a-series-of-numbers-in-dax/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+sqlbi_blog+%28SQLBI%29

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

https://dhexcel1.wordpress.com/2017/08/20/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:

https://dhexcel1.wordpress.com/2017/05/23/excel-native-3d-ranges-with-the-textjoin-function-plus-bonus-by-david-hager/

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

https://dhexcel1.wordpress.com/2017/02/07/calculating-aggregation-for-internal-numbers-from-strings-in-a-range-by-david-hager/

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.

=ArrayFromCDS(TEXTJOIN(“,”,TRUE,Sheet1:Sheet3!$B2:$D$6,Sheet1:Sheet3!$G$2:$G$6))

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

TEXTJOIN(“,”,TRUE,Sheet1:Sheet3!$B2:$D$6,Sheet1:Sheet3!$G$2:$G$6)

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

{“Name1″,”Name2″,”Name3″,”Name4″,”Name2″,”Name3″,”Name7″,”Name2″,”Name3″,”Name10″,”Name2″,”Name3″,”Name13″,”Name2″,”Name3″,”Name1″,”Name2″,”Name3″,”Name4″,”Name2″,”Name3″,”Name7″,”Name2″,”Name3″,”Name10″,”Name2″,”Name3″,”Name13″,”Name2″,”Name3″,”Name1″,”Name2″,”Name3″,”Name4″,”Name2″,”Name7″,”Name7″,”Name2″,”Name11″,”Name10″,”Name2″,”Name15″,”Name13″,”Name2″,”Name19″,”a”,”b”,”c”,”d”,”a”,”b”,”d”,”e”,”b”,”d”,”e”,”f”,”m”,”e”,”f”}

The figure shows this formula in cell I2.

TJ_3dTo1dArray1

I hope that you will find this useful.

The example file can be downloaded here.

TJ_3dTo1dArray

#Excel Super Links #132 – shared by David Hager

 

Excel CONVERT Function With Drop Down Lists

http://blog.contextures.com/archives/2017/08/17/excel-convert-function-with-drop-down-lists/

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

http://www.thebiccountant.com/2017/08/18/how-to-import-from-excel-with-cell-coordinates-in-power-query-and-power-bi/

Excel Range Modifiers

https://www.sumproduct.com/blog/article/vba-blog-series-range-modifiers

How to refer to the next row in Power Query

https://www.linkedin.com/pulse/get-transform-rescue-wyn-hopkins

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

https://dhexcel1.wordpress.com/2017/04/07/using-the-subtotal-function-in-excel-to-aggregate-values-from-multiple-filtered-lists-by-david-hager/

#Excel Super Links #131 – shared by David Hager

 

Excel Formulas Based Sudoku Solver

http://www.excelhero.com/blog/2010/07/excel-formula-based-sudoku-solver.html

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

http://chandoo.org/wp/2017/08/17/visualize-salary-increases-jitter-plot/

Converting a Formula to Values

https://newtonexcelbach.wordpress.com/2017/08/17/converting-a-formula-to-values/

Changing the culture of a Power BI Desktop file

http://www.sqlbi.com/articles/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

https://dhexcel1.wordpress.com/2017/08/17/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

https://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-counting-all-data-types-in-a-range/

What is Solver in Excel?

http://theexcelclub.com/use-solver-excel/

Working With Range.FormulaArray In VBA

https://colinlegg.wordpress.com/2012/05/23/working-with-range-formulaarray-in-vba/

15 Quick & Powerful ways to analyze Business Data

http://chandoo.org/wp/2015/07/01/how-to-analyze-business-data/

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

https://dhexcel1.wordpress.com/2017/08/15/extracting-ingredients-from-an-excel-list-by-cost-category-by-david-hager/