© The Author(s), under exclusive license to APress Media, LLC, part of Springer Nature 2023
A. I. KatzUp Up and Array!https://doi.org/10.1007/978-1-4842-8966-2_11

11. TOCOL and TOROW: Straightening Out the Data

Abbott Ira Katz1  
(1)
Edgware, UK
 

To reiterate an introductory point we made a short while ago: Excel’s new, second-generation dynamic array functions don’t open many new windows on number crunching; rather, they’re primarily about revamping and streamlining the way in which data can be organized. And that’s a point that surely calls for some explanation, beginning with a description of the TOROW and TOCOL functions.

How They’re Written – and What They’re About

TOCOL and TOROW were crafted to allow users simpler means for analyzing data that’s currently committed to a rectangular form. So what does that mean?

Try this example on for size. Suppose you’re faced with a collection of numbers occupying a range of 12 rows and 5 columns in G6:J17 (practice file: TOCOL – number matrix), its integers ranging in value from one through ten, per Figure 11-1.

A matrix with 4 columns and 12 rows filled with integers from 1 to 10. The row entries are as follows. Row 1. 1, 3, 9, and 7. Row 2. 2, 5, 6, and 3. Row 3. 3, 4, 8, and 7. Row 4. 9, 2, 3, and 4. So on.

Figure 11-1

The matrix: the sequel

We want to count the frequency with which each value appears in the range, but the dispersion of those values across five columns makes the exercise something of a pause giver – because before we can count each value with the standard COUNTIF function we need to list each value uniquely as criteria, so that COUNTIF knows what it’s counting; and assembling a unique list of values across columns is no straightforward thing.

Enter TOCOL, which is happy to throw itself at multi-column data and distill them all into a single column, after which all kinds of data-analytic tasks become much easier. After all, once you’ve somehow squeezed all the above values into one column – even temporarily – the UNIQUE function can fearlessly spring into action.

TOCOL (note that its name is phrased in the singular) exhibits three arguments, only one of which is absolutely required:

  • =TOCOL(range/array,ignore,scan by column)

The first argument, range, or what Excel again terms “array,” simply requests the coordinates of the data with which TOCOL is to work – that is, which data will be reshaped into a single column.

The second argument, “ignore,” presents itself as an option but can be surprisingly useful, as we’ll see. Ignore accepts four possible arguments expressed as values 1 through 4 in Figure 11-2.

A set of 4 options. 0, keep all values default. 1, ignore blanks. 2, ignore errors. 3, ignore blanks and errors. 0, keep all values default is selected.

Figure 11-2

TOCOL ignore options; don’t ignore them

The first as conveyed by the 0, Keep all values, serves as the default option and can be omitted; it simply preserves all existing values in the range/array and will return them all to the single column TOCOL will construct. Ignore blanks does just that, that is, it forsakes any empty cells in the source range/array and bars them from the TOCOL result, and ignore errors does the same for error-message-bearing cells. The fourth option, ignore blanks and errors, combines the operations of options 2 and 3.

The optional “scan by column” argument is also handier than you may initially suspect, and to demonstrate what it does let’s first write

  • =TOCOL(G6:J27)

in its option-free default mode, in cell L6. You should see (in excerpt) as in Figure 11-3.

A matrix of 4 columns and 12 rows is highlighted in an excel sheet. A list of numbers is under an excel formula. The formula reads, equals T O C O L left parenthesis G 6 colon J 17 right parenthesis.

Figure 11-3

All four one – the matrix narrowed to a single column

You’ll note the first four values populating the spill range – 1, 3, 9, and 7 – reference the entries in the matrix’ first row, with 2, 5, 6, 3, reflecting the values in row two, etc. If, however, we edit our formula to read

  • =TOCOL(G6:J17,,1)

where the 1 embodies what’s called the “scan by column” option, the results get flipped, per Figure 11-4.

A table of 4 columns and 12 rows and a list of numbers with an Excel formula. The formula reads equals T O C O L left parenthesis G 6 colon J 17 comma comma 1 right parenthesis. 1 in the first cell under the formula is highlighted.

Figure 11-4

Heading in a different direction: the results dive down the columns for their results

Here, the first four TOCOL results capture the 1, 2, 3, and 9 from the matrix’ first column, because the values are searched downwards.

But let’s not lose sight of our wider aim: we want to count the number of times each value appears in the matrix – and so we can remake our formula per that intent, and realize these outcomes, presented in Figure 11-5.

A table of 4 columns and 12 rows and a list of numbers with an Excel formula. The formula reads equals SORT left parenthesis unique left parenthesis T O C O L left parenthesis G 6 colon J 17 comma comma 1 close all the parentheses.

Figure 11-5

Each matrix value – once each, in numerical order

Now that we’ve uniquely enumerated the matrix’ values, we can add this expression to M6:

  • =COUNTIF(G6:J17,L6#)

yielding in turn the following, in Figure 11-6.

A table of 4 columns and 12 rows and two lists of numbers with two Excel formulas. One of the formulas read equals SORT left parenthesis unique left parenthesis T O C O L left parenthesis G 6 colon J 17,,1 close all parentheses.

Figure 11-6

Matrix values, ac-counted for

Mission accomplished. We’ve brought the TOCOL function to bear on multi-column data, thus enabling us to easily collect unique instances of each value.

Now what if we wanted to sort the matrix values – within the multi-columned matrix? That’s also something we can do, but that assignment awaits the next chapter, in which we debut the WRAPCOLS and WRAPROWS functions and partner them with TOCOL

Drawing a Bead on the Blanks

In view of the kinds of work that TOCOL and/or TOROW can perform, a face-to-face showdown with some nasty blank cells just might be in the offing. In that light consider this practice worksheet (file: TOCOL – attendance), in which the presence of ten hypothetical students has been recorded in A2:E11 (row 1 has been reserved for header data) in the order in which they’ve appeared each day as recorded by Figure 11-7.

A table of 5 columns and 11 rows. The column headers are Monday, Tuesday, Wednesday, Thursday, and Friday. Columns are recorded with names.

Figure 11-7

Classifying daily attendance data

We want to tabulate the attendance totals for each student for the week, and if that plan sounds like another call for COUNTIF you’re right. Again, our first order of business is to thresh a unique student listing from the five columns’ worth of data. What’s different here is the messy reality that the columns are unevenly occupied with names. If we thus write in I4:

  • =SORT(UNIQUE(TOCOL(A2:E11)))

The formula will unfurl this roster, per Figure 11-8.

A list of names with an Excel formula. The formula reads equals SORT left parenthesis unique left parenthesis T O C O L left parenthesis A 2 colon E 11 close all parentheses. Bennie's name was highlighted.

Figure 11-8

Ten students, eleven outcomes

We see what’s happened. Because attendance fell short of 100% on some days, the TOCOL expression picks up on the blanks pock-marking some of the columns and reports them in our single column as a zero, a decision that doesn’t serve the outcome very well. But rewriting the formula to read in Figure 11-9

A list of names with an Excel formula. The formula reads equals SORT left parenthesis unique left parenthesis T O C O L left parenthesis A 2 colon E 11, 1 close all right parentheses. Bennie was highlighted.

Figure 11-9

All present and accounted for – except the blanks

evicts any blanks from the spill range, a happy consequence of the 1 we’ve added to the formula (the “ignore blanks” argument), and that looks much better.

And once you’ve gotten this far you can break out another COUNTIF, dropping it into J4 and brought to our attention by Figure 11-10.

A list of names and a list of numbers with formulas equal SORT left parenthesis unique left parenthesis T O C O L left parenthesis A 2 colon E 11, 14 hash right parenthesis. Bennie and number 4 are highlighted.

Figure 11-10

Gold stars for Conor, Danny, and Nancy

Now what’s additionally cool about all this is if I redefine the attendance data as a table (making sure to tick the My table has headers box as you proceed), I can add more names to the attendance data in spite of the jagged table-column heights, and those new entries will be processed by our existing formulas, for example, in Figure 11-11.

A table of 5 columns and 11 rows. The column headers are Monday, Tuesday, Wednesday, Thursday, and Friday. A list of names and numbers with formulas is mentioned with Bennie and 4 highlighted.

Figure 11-11

Zelda is new, reported, and sorted

TOROW Is Slightly Different

And as you’ve probably inferred, TOROW realigns multi-column data into a single row’s worth of output, relying on the same arguments as TOCOL but nevertheless sometimes requiring a few more user decisions.

To explain: the above screen shot delineates a sorting of the unique instances of student names with TOCOL; but to sort student names uniquely with TOROW you must write as follows, as we see in Figure 11-12.

A row of names with an Excel formula. Names are Bennie, Brett, Chrissy, Conor, Danny, Nancy, Nydia, Rob, Robin, and Rosa. The formula reads equals SORT left parenthesis UNIQUE left parenthesis and so on.

Figure 11-12

Sideways student sort

What’s different here is that the UNIQUE function must be told to spill the unique names across columns, evidenced by the 1 in its final argument (the one preceding the three commas); and SORT too must be asked to order the data horizontally – stipulated by the last 1 in the entire formula.

Note

By default, both TOCOL and TOROW scan the ranges which they work by column; that is, they scan the data horizontally, realigning them into a single column or row one column at a time.

Lining Up the Conclusions

By constricting multi-column data into a single column or row the new TOCOL and TOROW dynamic array functions greatly ease the task of analyzing those data – even if, as we’ll see, you want to return them back to multiple columns whence they came. On the other hand, of course, you need to decide if TOCOL and TOROW are in fact the tools you need. For example, you almost surely won’t want to apply them to multi-field, as opposed to simple multi-column data, for example, these names and test scores in Figure 11-13.

A table of 2 columns and 3 rows. The row entries are as follows. Row 1. Ted, 56. Row 2. Jane, 67. Row 3. Mary, 87.

Figure 11-13

Don’t try this at home: jamming two fields into one column

Put TOCOL to that task and you’ll wind up with the following, as in Figure 11-14.

A table of 1 column and 6 rows. The column entry is as follows. Ted, 56, Jane, 67, Mary, and 87.

Figure 11-14

Apples and oranges: Alphas and numerics don’t mix

Try that and you’ll be reaching for the undo command pronto.

Coming Attractions

The next chapter will introduce a pair of new functions that can be made to usefully partner with TOCOL and TOROW, WRAPCOLS and WRAPROWS, both of which take the analysis in the opposite direction – by spreading one column’s or row’s worth of data into multiple columns or rows.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
52.15.210.12