Daily Dose of Excel

I need help. I have this:

I can tell my how many unique fruits are in column A with this array formula

E4: =SUM(1/COUNTIF(A2:A13,A2:A13))

And I can tell how many rows have both Apple in column A and 1 in column B with this array formula

E5: =SUM((A2:A13=”Apple”)*(B2:B13=1))

But I can’t seem to figure out the formula to tell me the count of unique fruits that have a 1 in column B. The answer is two, apple and peach. What’s the formula that gets me there?

Created by David Hager, Bob Umlas and Laurent Longre

The problem - to create an array containing only the unique items from an expanding column list. In other words, if items are typed down column A, what is the formula that will return the unique items? The following example further illustrates the problem.

In this case, the array should be {”a”;”b”;1;3}. Then, if additional values
are added:

the array should be {”a”;”b”;1;3;”c”;”d”}. The answer to this problem has eluded me for years, but with recent input from Bob and Laurent, I have successfully constructed a solution to this problem. The formula is somewhat long, so it is necessary to define parts of the formula to simplify the final form.

Create a defined name with a Name of TheList and a Refers to of:

=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),)

This formula creates the expanding range for the items as they are entered
into column A.

Define sArray as:

=SMALL(IF(MATCH(TheList,TheList,0)=ROW(TheList),ROW(TheList),""),
ROW(INDIRECT(”1:”&SUM(N(MATCH(TheList,TheList,0)=ROW(TheList))))))-1

This formula contains several important elements that require explanation. The formula

IF(MATCH(TheList,TheList,0)=ROW(TheList),ROW(TheList),"")

returns an array of positions for the unique items that is the same size as the TheList array, where the duplicates items are now represented by empty strings. The formula

ROW(INDIRECT("1:"&SUM(N(MATCH(TheList,TheList,0)=ROW(TheList)))))

returns an array of numbers from 1 to n, where n is the number of unique items in the list, as calculated by the formula

SUM(N(MATCH(TheList,TheList,0)=ROW(TheList))).

What is desired is an array that contains the unique positions with no empty strings. This is accomplished by the use of the SMALL function which, along with the LARGE function, is unique among Excel functions in its ability to create different sized arrays than the array used in the 1st argument if the 2nd argument is also an array. The -1 is used to adjust the item positions for use in the formula shown below.

Define TheUniqueArray as:

=IF(T(OFFSET(TheList,sArray,,1))="",N(OFFSET(TheList,sArray,,1)),T(OFFSET(TheList,sArray,,1)))

The formula

OFFSET(TheList,sArray,,1)

is an array of single element arrays, as explained in detail in the 1st issue of EEE. It can be converted into a normal array by using the N or T functions. Both N and T are used here since TheList can contain either text or numeric items.

WARNING: This formula can take a while to calculate if TheList is long. One thousand items took a couple of seconds on my Latitude D810.

Here's what my (partially obfuscated) desktop looks like:

I put information I need on my desktop so I'm a Windows+M key combination away from what I need. The Excel file sits in my XLStart directory so the calendar gets updated every day.

VBA from Excel to Desktop

In the Workbook_Open event, I have

It only updates it once per day, although it doesn't really take that long to execute. In a standard module, I have:

The PastePicture call uses code from Stephen Bullen's PastePicture.zip file.

Almost nothing in the file is my own - all stolen. In addition to Stephen's PastePicture, the following code is borrowed:

The Calendar

The calendar for this month and next month is John Walkenbach's Array Calendar (see also). For 'next month' I merely added 1 to the month in the DATE function throughout that formula.

Week Numbers

The week numbers along the right come from Ron de Bruin's site. The formula in H27 is

=IF(LEN(B27)=0,"",INT((B27-DATE(YEAR(B27-WEEKDAY(B27-1)+4),1,3)+WEEKDAY(DATE(YEAR(B27-WEEKDAY(B27-1)+4),1,3))+5)/7))

It only shows the week number for weeks with a Monday (B27 in this case).

Highlighting Today and Holidays

The conditional formatting for the current month looks like this:

The first format inverts the colors for the current day. The second format checks the range HolVac to see if the date is a holiday - meaning a day I don't work. In that range, I have the holidays my company offers plus I enter any scheduled vacation days. I show the holiday formulas below. The second format is repeated for 'next month'.

Special Characters

Because I deal with German companies on a somewhat regular basis, I like to keep the ASCII codes for some German characters handy, and I do that across the bottom. To enter the special characters, hold down the Alt key and type the four digit number on the numeric keypad.

Holiday Formulas

The formulas for the holidays come from Chip Pearson's Holiday Page and Chip's Date and Time Page. In the following formulas, cell S1 contains the current year and S2 contains the next year.

New Year's Day: =IF(WEEKDAY(DATE(S1,1,1))=7,DATE(S1,1,1)-1,IF(WEEKDAY(DATE(S1,1,1))=1,DATE(S1,1,1)+1,DATE(S1,1,1)))

Next New Year's Day: =IF(WEEKDAY(DATE(S2,1,1))=7,DATE(S2,1,1)-1,IF(WEEKDAY(DATE(S2,1,1))=1,DATE(S2,1,1)+1,DATE(S2,1,1)))

Memorial Day: (array) =IF(SUM(IF(WEEKDAY(DATE($S$1,5,1)-1+ROW(INDIRECT("1:"&TRUNC(DATE($S$1,5,31)-DATE($S$1,5,1))+1)))=2,1,0))=5,
    DATE(YEAR(NOW()),5,1+((5-(2>=WEEKDAY(DATE(YEAR(NOW()),5,1))))*7)+(2-WEEKDAY(DATE(YEAR(NOW()),5,1)))),
    DATE(YEAR(NOW()),5,1+((4-(2>=WEEKDAY(DATE(YEAR(NOW()),5,1))))*7)+(2-WEEKDAY(DATE(YEAR(NOW()),5,1)))))

US Indpendence Day: =IF(WEEKDAY(DATE(S1,7,4))=7,DATE(S1,7,4)-1,IF(WEEKDAY(DATE(S1,7,4))=1,DATE(S1,7,4)+1,DATE(S1,7,4)))

Labor Day: =DATE(S1,9,1+((1-(2>=WEEKDAY(DATE(S1,9,1))))*7)+(2-WEEKDAY(DATE(S1,9,1))))

Thanksgiving: =DATE(S1,11,1+((4-(5>=WEEKDAY(DATE(S1,11,1))))*7)+(5-WEEKDAY(DATE(S1,11,1))))

Xmas Eve: =IF(WEEKDAY(DATE(S1,12,24))=7,DATE(S1,12,24)-1,IF(WEEKDAY(DATE(S1,12,24))=1,DATE(S1,12,24)-2,DATE(S1,12,24)))

Xmas: =IF(WEEKDAY(DATE(S1,12,25))=7,DATE(S1,12,25)-1,IF(WEEKDAY(DATE(S1,12,25))=1,DATE(S1,12,25)+1,DATE(S1,12,25)))

Update: Download DesktopPic.zip