Charting Data With Formula Links In Excel By David Hager

You might have a need to chart data that is formula linked from its original source. For example, the data is generated in Columns A (Date) & B (Data), but you want to chart the data from Columns U & V. So, you would put this formula in cell U1 (=A1) copy it to Column V and fill down to whatever # of rows you needed. As the data is generated in A & B, you would then want to chart the data from U & V. In the following Figure is an example what U & V might look like.

Notice what the columns look like where no information has been added at the data source. The column containing the dates return at the bottom 01/00/1900, which is 0 with a date format. The column containing the data return at the bottom 0’s. An attempt to chart this data as is shown in the following Figure. = SERIES(Control!$V$1,Control!$U$2:$U$39,Control!$V$2:$V$39,1)

Clearly, the chart does not produce the desired effect. A method of charting just the “good” data is needed. In order to identify the end of the good data, this defined name formula looks at column V and finds the 1^st position of a 0.

EndOfData= MATCH(0,Control!$V:$V,0)-2

Actually, the formula for doing that would exclude the -2, but it is needed for sizing the subsequent OFFSET formulas.

DateRange= OFFSET(Control!$U:$U,1,,EndOfData,)

Data Range= OFFSET(Control!$V:$V,1,,EndOfData,)

So, the defined names formulas can now be used in the chart series formula.

=SERIES(Control!$V$1,ChartDraw.xlsm!DateRange,ChartDraw.xlsm!DataRange,1)

Note that the defined names are at the workbook level. The desired chart is shown below.

Hope this helps!

Creating a Unique Delimited String from an Excel Filtered List by Using the TEXTJOIN Function By David Hager

Some time ago (16 years – which is 100 in Excel years) I developed a formula solution for counting the number of unique items in a filtered list. I realized that this methodology could be used with the new TEXTJOIN function in Excel (Office 365 version). See the original publication in EEE# 20:

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

The following defined names are needed to construct the formula.

Rge=Sheet1!$A$2:$A$20

unRge=IF(SUBTOTAL(3,OFFSET(Rge,ROW(Rge)-MIN(ROW(Rge)),,1)),Rge,””)

unRge returns an array that contains only filtered items.

In the figure, rows 2,3,4,6,8,10,11,12,15,17,18,19,and 20 are visible. To return a unique delimited string for only those visible rows, use the following formula (in E3):

=TEXTJOIN(“,”,TRUE,IF(N(IF(ISNA(MATCH(“”,unRge,0)),MATCH(Rge,Rge,0),IF(MATCH(unRge,unRge,0)=MATCH(“”,unRge,0),0,MATCH(unRge,unRge,0)))=ROW(Rge)-MIN(ROW(Rge))+1),unRge,””))

which affords

a,c,h,l,v,m,d,w,g,o,t

You can download the file here.

textjoin_subtotal1

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

Archive of Excel Experts E-Letter (by David Hager)

Previously hosted on John Walkenbach’s web site, the material included here is unabridged. Therefore, many of the links are no longer good. Also, some of this information is dated and obsolete. Nonetheless, I am posting it here because it does contain a number of VBA procedures and Excel formulas that are still of use today, and to preserve a record of what was new and exciting in the Excel world many years ago.

Here is the link.

eee-1-20

Creating an Excel Table of Components By Product From a List Using the TEXTJOIN Function By David Hager

The listing of recipes for process blending is difficult, since each recipe can contain a different number of components. Therefore, the usual listing of this recipe information is as shown in columns A and B. From the sample table, defined names are given where arange is the list of blended compounds and brange is the list of components. In this case:

arange=Sheet1!$A$2:$A$25

brange=Sheet1!$A$2:$A$25

The following formula was used to make the unique list of compounds in column E.

=INDEX(arange,MATCH(0,INDEX(COUNTIF($E$2:E2,arange),0,0),0)) in E3.

Then the formula is copied down until #N/A appears in a cell. Cells containing #N/A are then deleted.

I found this formula at Oscar’s great Excel site. See:

http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/

Now that the unique list is in place, a comma delimited list of components can be created in column F.

Place the following formula in cell F3 and fill down.

=TEXTJOIN(“,”,,IF(arange=E3,brange,””))

This array formula compares the value in E3 to each value in arrange and if there is a match, the corresponding value in brange is returned to the array. The TEXTJOIN function converts the array to a string using comma as the delimiter.

You can download the file here.

 textjoin1

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

Creating A Unique Delimited String From a Delimited String – Excel Formula Method With TEXTJOIN( David Hager)

Before I started my blog, I wrote an article about calculating the number of unique items in a delimited string. Dick Kusleika was kind enough to publish it on his web site.  Here is the link:

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

You know, It’s amazing what you can learn when you reread something you previously had written :). I was thinking of how to convert a delimited string into an array, and, lo and behold, I had already done this as part of that article. Here is that formula shown below:

DelStrArry=TRIM(MID(SUBSTITUTE(Sheet1!$A$1,”,”,REPT(“”,999)),ROW(INDIRECT(“1:”&LEN(Sheet1!$A$1)-LEN(SUBSTITUTE(Sheet1!$A$1,”,”,””))+1))*999-998,999))

Armed with this formula (which creates an array) that can be used as the main argument in the TEXJOIN formula demonstrated in my previous blog post, I was ready to complete the desired formula. See:

https://dhexcel1.wordpress.com/2017/01/02/using-the-excel-textjoin-function-to-return-unique-items-in-a-one-cell-delimited-string-from-a-2d-and-3d-range-by-david-hager/

It was also necessary to correctly scale the row array the same size as the primary array. This was accomplished with the following defined name formulas.

CntDelStrArry=COUNTA(DelStrArry)

RowArry=ROW(INDIRECT(“1:”&CntDelStrArry))

The final result is:

=TEXTJOIN(“,”,,IF(MATCH(DelStrArry,DelStrArry,0)=RowArry,DelStrArry,””))

which will convert a delimited string like, for example

a,b,c,c,d,a,e,r,h,h,t,o,x,a,b,c  (in A1)

to

a,b,c,d,e,r,h,t,o,x

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

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