Excel Spillable Ranges are Great!

Quote of the Day

I begin to speak only when I’m certain what I’ll say isn’t better left unsaid.

Cato the Younger. I try to keep this quote in mind during meetings.


Figure 1: Toy Data Set With Same Format as My Work Example.

Figure 1: Toy Data Set With Same Format as My Work Example.

I use Python or R for my large-scale data work, but I do find Excel a very powerful ad hoc data analysis tool, particularly with some of the new functions that use spillable ranges. Today, I was given a large table of Engineering Change Orders (ECOs) and a comma-separated list of the documents each ECO affected (very abbreviated form shown in Figure 1). I needed to generate a count of the number of times each document was modified by an ECO and I needed that count for a meeting coming up in a few minutes. My approach can be described as follows:

    • Use TEXTJOIN to combine all the comma-separated lists into one long comma-separated list.
    • Use FILTERXLM to break up the combined comma-separated list into a column with one document per row
    • Use UNIQUE to generate a list of the unique documents that were changed
    • Use COUNTIF to count the number of times each individual document was changed

Figure 2 shows a screenshot of my Excel workbook. For those who like to work along, I include my workbook here.

Figure 2: Functions Applied to Generate Document Change Counts.

Figure 2: Functions Applied to Generate Document Change Counts.

 
This entry was posted in Excel. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *