Tag Archives: 3D

#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 Native 3D Ranges with the TEXTJOIN Function Plus Bonus by David Hager

In a conversation with Bill Jelen about the TEXTJOIN function, he mentioned whether I had tried using 3D references as arguments. I said that I had not tried native 3D ranges, and I did not believe it would work. Well, I was wrong (again). It does work, in spite of there being no documentation from Microsoft about that ability. The figure below shows the result in cell D2 of using this formula:

=TEXTJOIN(“,”,,Sheet1:Sheet2!A1:A2)

TJ3D2.PNG

I continue to be amazed by the myriad of formula solutions made possible by the TEXTJOIN function. In a recent article where I demonstrated the use of non-contiguous ranges

https://dhexcel1.wordpress.com/2017/05/21/excel-short-and-sweet-tip-15-using-textjoin-with-non-contiguous-ranges-by-david-hager/

I now realize that the same concept can be used with 3D ranges. (Here is the bonus!) In fact, that 3D range can be in ANOTHER workbook, as shown below.

=TEXTJOIN(“,”,,Sheet1:Sheet2!A1:A2,[TEXTJOIN_Native3DB.xls]Sheet1:Sheet2!A1:A2)

TJ3D1.PNG

The example file can be downloaded here (Note: I did not include the external file, and I put a tilde in front of that formula)

TEXTJOIN_Native3D

Remember again, that you have to have the Excel version included in Office 365 in order for the TEXTJOIN formula to work.

#Excel: Using Conditional Formatting to Highlight 3D Formulas with Defined Names by David Hager

There was a comment on LinkedIn about my post about using CF to highlight 3D formulas

“Since I never use a direct reference (or, come to that, enter a formula without naming the range to which it applies) any 3D reference I might use would pass under the radar. Unless, of course, you have an array UDF which will parse the formula to yield a set of references; in which case can I put in an order?”

Initially, I replied that it was not possible. But, the challenge was irresistable. I started working on the problem and, after a number of dead-ends, I was able to come up with a solution. It required a VBA function to return an array of defined names.

Function DefinedNameArray() As Variant

Application.Volatile

Dim Arr As Variant

nCount = ActiveWorkbook.Names.Count

ReDim Arr(1 To nCount)

For N = 1 To nCount

cPos = InStr(1, ActiveWorkbook.Names(N).RefersTo, “:”)

ePos = InStr(1, ActiveWorkbook.Names(N).RefersTo, “!”)

If cPos < ePos Then

Arr(N) = ActiveWorkbook.Names(N).Name

Else

Arr(N) = “”

End If

Next

 

DefinedNameArray = Arr

End Function

What the VBA function does is return an array of defined names, but only places the items meeting the correct criteria for a 3D formula in the final array (which is the same concept using in the initial article).

https://dhexcel1.wordpress.com/2017/04/24/excel-using-conditional-formatting-to-highlight-cells-containing-native-3d-formulas-by-david-hager/

In this case, the InStr function was used to locate the positions of the first colon and exclamation point in the RefersTo string and the values are compared. If cPos<ePos, then the name is added to the array and a null string added otherwise. This array is used in the following formula to find if a 3D defined name is part of the string returned by the FORMULATEXT function. It was defined for use as a CF formatting formula, as shown below (F5 was the active cell when defined).

Is3DDefinedName=MATCH(TRUE,IFERROR(FIND(IFERROR(DefinedNameArray(),””),FORMULATEXT(F5))>1,FALSE),0)

Is3ddn1

Both F5 and F7 contain formulas using 3D defined ranges.

Peter, thanks for the challenge!

You can download the example file here.

CFDefinedNames

#Excel: Using Conditional Formatting to Highlight Cells Containing Native 3D Formulas by David Hager

Conditional formatting (CF) in Excel can be used to hightlight cells that meet certain criteria. In this case, I wanted to create a CF that would highlight cells containing formulas that use Excel’s native 3D references. So, this would be like the following example.

=SUM(Sheet1:Sheet2!B2:B5)

So, I tried to determine what was unique this type of formula string compared to others. What I noticed was that the first colon in this formula always comes before the exclamation point. Thus, I started working on a solution on that basis.

Note, though, that there are ways to write a formula containing a 3D reference that will not meet this criteria, such as:

=SUM(C2:C5,Sheet2!C2:C5)

So, don’t use those kinds of formulas. 😊

To lookup the position of the colon in the formula string, the following formula is needed.

=MATCH(“:”,MID(FORMULATEXT(F6),ROW(INDIRECT(“1:”&LEN(FORMULATEXT(F6)))),1),0)

where F6 contains the formula.

The corresponding formula for looking up the position of the exclamation point is:

=MATCH(“!”,MID(FORMULATEXT(F6),ROW(INDIRECT(“1:”&LEN(FORMULATEXT(F6)))),1),0)

By comparing the two formulas, the following Boolean expression wrapped in an IFERROR function is defined as Is3D:

=IFERROR(MATCH(“:”,MID(FORMULATEXT(F6),ROW(INDIRECT(“1:”&LEN(FORMULATEXT(F6)))),1),0)<=MATCH(“!”,MID(FORMULATEXT(F6),ROW(INDIRECT(“1:”&LEN(FORMULATEXT(F6)))),1),0),FALSE)

Applying this formula as a CF on cell F6, you can see that F6 is highlighted as expected.

Is3D1

You can download the example file here.

IsNative3DFormula

Using the Excel TEXTJOIN Function To Return Unique Items In A One-Cell Delimited String From A 2D and 3D Range By David Hager

The TEXTJOIN function was recently introduced in Excel 2016, and a number of fascinating formula solutions have been created with this function. One of these formulas used TEXTJOIN to create a one-cell delimited string of unique items from a list. To see how that was done, go to:

https://www.youtube.com/watch?v=QJ2O07EB80Q&feature=youtu.be

The methodology for making a 1D array from a 2D Excel range was created and described in great detail at:

https://excelxor.com/2014/11/08/unique-alphabetical-list-from-several-columns/

It was subsequently used to make a unique list from that range. The following formula uses a slightly modified version of that methodology.

 

=TEXTJOIN(“,”,,IF(MATCH(Arry4,Arry4,0)=Arry1,Arry4,””))

 

where the required defined named formulas are:

 

Range1 =Sheet1!$H$2:$L4)

Arry1=ROW(INDIRECT(“1:”&COLUMNS(Range1)*ROWS(Range1)))

Arry2=1+INT((Arry1-1)/COLUMNS(Range1))

Arry3=1+MOD(Arry1-1,COLUMNS(Range1))

Arry4=INDEX(Range1,N(IF(1,Arry2)),N(IF(1,Arry3)))

 

Again, these formulas were not created by me, but the TEXTJOIN shown above does create a one-cell comma-delimited string from the 2D range Sheet1:!$H$2:$L4).

For a VBA solution to this same problem, see:

http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-unique-list-from-multi-column-table/

I needed a way to transform a 3D range in Excel to a 2D array. Luckily, this had already been done recently. Here is the formula:

Range_3D=CELL(“contents”,IF(1,+INDIRECT(Sheets&TEXT(MODE.MULT(ROW(Range1)*10^5+COLUMN(Range1),ROW(Range1)*10^5+COLUMN(Range1)),”!R0C00000″),)))

This formula was created by MichaelCH as part of the collaboration at the amazing excelxor.com site.

See: https://excelxor.com/2016/04/08/advanced-formula-challenge-13-single-array-containing-all-entries-from-a-given-range-in-multiple-worksheets

Since this formula creates a 2D array from a 3D array, I was hopeful that it could be used in the same way that a “real” Excel 2D range is transformed to a 1D array. In particular, I was concerned that the COLUMNS function would not work on a 2D array, but it did! I just had never tried it before.

In order to modify Arry1 for correct scaling for the 3D range, the following defined formulas were needed:

 

Sheets={“Sheet1″,”Sheet2″,”Sheet3”,”Sheet4”}  ‘in this example

ShCnt=COUNTA(Sheets)

 

The modification of Arry1 to 3D scaling is shown below:

 

Arry1_3D=ROW(INDIRECT(“1:”&COLUMNS(Range1)*ROWS(Range1)*ShCnt))

along with the other transform array formulas.

Arry2_3D=1+INT((Arry1_3D-1)/COLUMNS(Range_3D))

Arry3_3D=1+MOD(Arry1_3D-1,COLUMNS(Range_3D))

Arry4_3D=INDEX(Range_3D,N(IF(1,Arry2_3D)),N(IF(1,Arry3_3D)))

 

The resulting formula using TEXTJOIN affords the desired one cell delimited string on unique items from a 3D range.

 

=TEXTJOIN(“,”,,IF(MATCH(Arry4_3D,Arry4_3D,0)=Arry1_3D,Arry4_3D,””))

 

Apart from using the created array with TEXJOIN, the transforming of a 3D range in Excel to a 1D array is also noteworthy.

Here is the link to download the file. Remember though, that you have to have the Excel version in Office 365 in order for the TEXTJOIN formulas to work.

uniquetextjoin

EXCEL 3D – Easy as 1-2-3 and A-B-C and Others

EXCEL 3D – Easy as 1-2-3 and A-B-C and Others

By David Hager

A number of years ago (~16) Laurent Longre discovered that Excel formulas that calculated over 3D ranges could be made by using the INDIRECT function(see Issue #3).

https://dhexcel1.wordpress.com/2017/01/07/archive-of-excel-experts-e-letter-by-david-hager/

This formula was developed out of necessity, since many of the functions in Excel do not work with Excel’s built-in 3D cell range notation.

This methodology was refined to formulas of the general form:

=SUMPRODUCT(XXXIF(INDIRECT(“‘”&SOMETHING&”‘!cell_range”),criteria))

In its basic form, it was first developed sometime in the 2004-2005 time period, but it is unclear who was actually the first person to derive the “final” form.

A specific example of this

=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&SOMETHING&”‘!B2:F3”),”<>”&””))

This article is not actually about how to use these 3D formulas. Rather, it is about the formula techniques that can be used to create multi-sheet range in the SOMETHING part of the formula.

In each example shown here, an actual formula/range can be used, or a defined name representing the formula/range can be used.  In the following example, an array created by the ROW function can be used to return sheet tabs named as single digit numbers.

=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&ROW($1:$4)&”‘!B2:F3”),”<>”&””))

Or in the defined name form:

MyNumberTab1 =ROW($1:$4)

=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&MyNumberTab1&”‘!B2:F3”),”<>”&””))

It is important to note that this type of 3D formula has another inherent advantage over Excel’s normal 3D range formulas (i.e. – =SUM($1:$4!B2:F2). The tabs do not have to be contiguous for the formulas to function as intended/desired. If, for example, the sheet containing the 3D formulas was “between” sheet “1” and “3” and had entries in the cell range of that sheet, they would be included in the sum for an Excel-inherent 3D cell reference, but they are not for the INDIRECT 3D reference form.

Another formula returns an array of single letters from a-z.

=CHAR(ROW($97:$122))

So, using this in the INDIRECT formula will return the expected values for the specified formula from all sheets named a-z (26 sheets). Note that all 26 sheets have to exist for the formula to not return an error. Of course, this formula can be modified for an alphabet subset if necessary.

Another formula returns an array of months of the year, which is another popular choice for naming worksheet tabs.

=TEXT(ROW($1:$12)&”-1″,”MMM”)

All of the formula arrays mentioned in this article can be modified by concatenating a text string to the array. In this case, the following array returns 12 strings, starting with “Jan 2011”.

Months2011 =TEXT(ROW($1:$12)&”-1″,”MMM”)&” 2011″

The final example illustrates the simplest form of an array to be used: a simple cell range.

=$A$1:$A$4

Two modifications of this can be useful as well. The OFFSET function could be used to create a dynamic cell range that would expand or contract based on user input.

For cases where the name list is anticipated to be static, the cell range can be evaluated in the formula bar and the resulting array of tab names can be stored as a defined name.

MyTabs1={“John”;”Paul”;”George”;”Ringo”}

I hope that you found this discussion useful.