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.
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.