# #Excel Short and Sweep Tip #13 (KeyBoard DFAROT Unique Technique) by David Hager

Using Data Filter Advanced Filter to obtain a list of unique items from another list is quick and relatively easy to do. However, when Rob van Gelder came up with a keystroke way to do this rather by clicking, it made this a lot easier to accomplish.

http://dailydoseofexcel.com/archives/2012/05/25/copy-unique-values/

as per his instructions:

Select the range to extract from

Hold down the Alt key

Press these keys in sequence: d, f, a, r, o, t

Release the Alt key

Select the range to paste the unique values to.

The only caveat to this approach that I found is that when Excel does not recognize that the selection has headers, it will stop at an intermediary stage. Then, you can click the appropriate options to finish the unique items copy.

HTH!

# Using TEXTJOIN: Generating a Sorted Unique Array in #Excel using Only Formulas by David Hager

I like to review articles from various Excel sites to get ideas for new creations and to use the “formula

technology” displayed there. I was reading an article from Oscar’s great Excel site at

http://www.get-digital-help.com/2009/05/25/create-a-drop-down-list-containing-only-unique-distinct-alphabetically-sorted-text-values-using-excel-array-formula/

and I decided to try and extend his work by creating a sorted unique array derived from a list. I wanted this to use with one of my favorite Excel functions (TEXTJOIN). The final result is shown in the following figure. Now, to how this was made. The following formula was created by Oscar (and slightly modified by me). Is uses COUNTIF to determine the number of array items that are “greater” than the rest of the items (i.e. A>B).

UniqueArr1=IF(MATCH(COUNTIF(List,”>”&List)+1,COUNTIF(List,”>”&List)+1,0)=ROW(INDIRECT(“1:”&COUNTA(List))),COUNTIF(List,”>”&List)+1,””)

{1;21;26;16;12;””;14;19;7;””;17;25;””;10;3;29;13;””;9;8;6;18;23;24;””;””;””;””;22;””}

Now, the challenge was to create an array of positions from UniqueArr1 corresponding to the largest to smallest numbers in UniqueArr1. If you locate the largest number in UniqueArr1(29), it is in position 16 in the array. Likewise, the 2nd largest number in UniqueArr1(26), is in position 3. This was accomplished by using the following formula (although I admit that it took a while to figure this out).

=MATCH(LARGE(UniqueArr1,ROW(INDIRECT(“1:”&COUNT(UniqueArr1)))),UniqueArr1,0)

It is important to note that the ROW array is dimensioned with COUNT(UniqueArr1), since the elements of UniqueArr1 greater than COUNT are null values. As you can see, the resulting array is correctly dimensioned.

{16;3;12;24;23;29;2;8;22;11;4;7;17;5;14;19;20;9;21;15;1}

Now, we have an array with the sorted positions in the correct order and that array can be used return itemd from the List array. A few years ago, it was thought that the INDEX function could not return an array of items, but then a great solution to this appeared at the excelxor.com site. See:

https://excelxor.com/2014/09/05/index-returning-an-array-of-values/

The resulting formula is shown below.

uSortedArr=INDEX(List,N(IF(1,MATCH(LARGE(UniqueArr1,ROW(INDIRECT(“1:”&COUNT(UniqueArr1)))),UniqueArr1,0))))

The resultant array is:

{“Bovey”;”Bullen”;”Cronquist”;”Dalgleish”;”Devenshire”;”Duggirala”;”Green”;”Hager”;”Hodge”;”Jelen”;”Kusleika”;”Manville”;”McRitchie”;”Mehta”;”Ogilvy”;”Pearson”;”Peltier”;”Pieterse”;”Puls”;”Rech”;”Umlas”}

This array can now be converted back to a string by using the TEXTJOIN function. The use of the delimiter CHAR(10) allows the result to be displayed as shown in the figure earlier if the cell is formatted with word wrap as true and is merged with lower cells. The formula (in cell K1 in the example) is:

=TEXTJOIN(CHAR(10),,uSortedArr)

You can download the file from this link. Remember, you must have a correct version of Excel 2016 for the TEXTJOIN function to work.

UniqueSorted

# Using Conditional Formatting to Highlight Unique Items in an Excel Filtered List By David Hager

Quite a while back I created a formula to count the number of unique items in a filtered list. For examples, see:

http://blog.contextures.com/archives/2010/10/04/count-unique-items-in-excel-filtered-list/

and

https://dhexcel1.wordpress.com/2017/01/08/creating-a-unique-delimited-string-from-an-excel-filtered-list-by-using-the-textjoin-function-by-david-hager/

I decided to extend this methodology to the conditional formatting of a filtered list. The following defined name formulas are required.

Rge=\$A\$5:\$A\$29

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

cfUnRge=INDEX((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)),ROW()-4)

(The cursor must be on A5 when the cf function is defined and applied to A5:A29)

So, before filtering, the list shows the 1st unique items highlighted in yellow. After filtering (removing the letters b,e,f,g), the resulting filtered list looks like this. unFiltered

# 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

textjoin_subtotal1

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:

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.

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 Irregular Banding on a Filtered List by David Hager

Yesterday I was looking up some information about Excel banding on the Net, and I came across this article on Dick Kusleika’s web site.

http://dailydoseofexcel.com/archives/2006/06/30/irregular-color-banding/

Although he had cited me when he used a formula I created about 20 years ago as part of his banding solution, I never saw the article because he had misspelled my name (Hagar instead of Hager) so it never came up in a search.

=ROUND(MOD(SUM(1/COUNTIF(\$A\$3:A3,\$A\$3:A3)),2),2)=1

Anyway, I thought that his formula was very cool and it got me to thinking about how the same feature might be applied to a filtered list. I quickly realized that another formula for counting unique items would have to be used, since the “range” argument in the COUNTIF function has to be an actual range and cannot be an array. Luckily, I was able to remember that I had created another formula long ago to return unique items and this formula could accept arrays. Furthermore, I had already adapted this formula to work with a filtered list by using the SUBTOTAL formula invented by Laurent Longre shown at the link below (my old EEE publication on John Walkenbach’s site).

So, I modified the SUBTOTAL formula shown below to include expanding ranges as per Dick’s formula.

define filtRange as:

=IF(SUBTOTAL(3,OFFSET(\$B\$2:\$B2,ROW(\$B\$2:\$B2)-MIN(ROW(\$B\$2:\$B2)),,1)),\$B\$2:\$B2,””)

(where column B contains sorted information organized in blocks)

and replaced the “unique formula” used in his formula with one from the EEE article to come up with a truly powerful formula.

=ROUND(MOD(SUM(N(IF(ISNA(MATCH(“”,filtRange,0)),MATCH(\$B\$2:\$B2,\$B\$2:\$B2,0),IF(MATCH(filtRange,filtRange,0)=MATCH(“”,filtRange,0),0,MATCH(filtRange,filtRange,0)))=ROW(\$B\$2:\$B2)-MIN(ROW(\$B\$2:\$B2))+1)),2),2)=1

Caveat: In fact, it is so powerful that it takes up an enormous amount of computing power when used on a large list, so use it with care.

It can be used directly as a conditional formatting formula or added as an additional column in the list, which would then be used with the conditional format (i.e. – =\$D2).

As an example, the figure below would be the result before filtering: and the next figure would be the result after filtering (5 & 7 filtered from result column): I hope that this provides another useful bullet to your Excel formatting arsenal.