{"id":2619,"date":"2023-11-30T17:19:00","date_gmt":"2023-11-30T23:19:00","guid":{"rendered":"https:\/\/www.iqaccountingsolutions.com\/blog\/?p=2619"},"modified":"2023-12-14T17:30:59","modified_gmt":"2023-12-14T23:30:59","slug":"combine-excel-data-into-one-column-using-tocol","status":"publish","type":"post","link":"https:\/\/www.iqaccountingsolutions.com\/blog\/combine-excel-data-into-one-column-using-tocol\/","title":{"rendered":"Combine Excel Data into One Column using ToCol()"},"content":{"rendered":"\n<p>Last month I talked about how to use Transpose to copy data from a row and paste it into a column or vice versa. This month we&#8217;ll look at a fairly new Excel function <em>ToCol()<\/em> that can pull data from several adjacent rows and columns and combine it into one column. (There&#8217;s also a ToRow() function to combine multiple rows into one.)<br><br>Here is how you use this function<br><em>=TOCOL(array, [ignore], [scan_by_column])<\/em><\/p>\n\n\n\n<ul>\n<li><em>Array<\/em> is simply the range of cells you want to take information from.<br>&nbsp;<\/li>\n\n\n\n<li><em>Ignore<\/em> determines what information, if any, will be ignored. There are four options<br>0 (or omitted) = Keep all values<br>1 = Ignore blanks<br>2 = Ignore errors<br>3 = Ignore blanks and errors<br>&nbsp;<\/li>\n\n\n\n<li><em>Scan_by_column<\/em> determines whether it reads the data in your array by row or by column.<br>False (or omitted) = scan by rows<br>True = scan by columns<\/li>\n<\/ul>\n\n\n\n<p>Here&#8217;s a simple example. You can see that there are names in columns A and B. In cell D1 is an example of ToCol in its simplest form. Since only an array is specified (A1:B5) Excel assumes the default values for <em>Ignore<\/em> and <em>Scan_by_column<\/em>). Then, in F1 is another example using the same array, but setting <em>Ignore<\/em> to &#8220;1&#8221; (ignore blanks) and setting <em>Scan_by_column<\/em> to &#8220;True&#8221;. In the second picture you can see the results.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-content\/uploads\/2023\/12\/ToCol-formula.png\"><img decoding=\"async\" loading=\"lazy\" width=\"1024\" height=\"234\" src=\"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-content\/uploads\/2023\/12\/ToCol-formula-1024x234.png\" alt=\"\" class=\"wp-image-2620\" srcset=\"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-content\/uploads\/2023\/12\/ToCol-formula-1024x234.png 1024w, https:\/\/www.iqaccountingsolutions.com\/blog\/wp-content\/uploads\/2023\/12\/ToCol-formula-300x69.png 300w, https:\/\/www.iqaccountingsolutions.com\/blog\/wp-content\/uploads\/2023\/12\/ToCol-formula-768x176.png 768w, https:\/\/www.iqaccountingsolutions.com\/blog\/wp-content\/uploads\/2023\/12\/ToCol-formula-50x11.png 50w, https:\/\/www.iqaccountingsolutions.com\/blog\/wp-content\/uploads\/2023\/12\/ToCol-formula.png 1044w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<p>Probably the first thing you&#8217;ll notice is that we only entered a formula in row 1 of columns D and F, but we have many rows of results. Unlike most Excel functions, ToCol can &#8220;spill&#8221; results into as many rows as needed.<br><br><\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-content\/uploads\/2023\/12\/ToCol-Results.png\"><img decoding=\"async\" loading=\"lazy\" width=\"591\" height=\"225\" src=\"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-content\/uploads\/2023\/12\/ToCol-Results.png\" alt=\"\" class=\"wp-image-2621\" srcset=\"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-content\/uploads\/2023\/12\/ToCol-Results.png 591w, https:\/\/www.iqaccountingsolutions.com\/blog\/wp-content\/uploads\/2023\/12\/ToCol-Results-300x114.png 300w, https:\/\/www.iqaccountingsolutions.com\/blog\/wp-content\/uploads\/2023\/12\/ToCol-Results-50x19.png 50w\" sizes=\"(max-width: 591px) 100vw, 591px\" \/><\/a><\/figure>\n\n\n\n<p>Both versions of the formula combined the names from the range A1 to B5 (the Array) into one column. Let&#8217;s look at the differences in the results.<br><br>In column D, we didn&#8217;t specify anything for <em>Ignore<\/em>. The result is that the empty cell from B5, gets displayed as &#8220;0&#8221; in our list. But in column F, where we set <em>Ignore<\/em> to 1 (ignore blanks) we only have 9 rows of results instead of the 10 items we have in column D.<br><br>In column F, we also set <em>Scan_by_column<\/em> to True. That is why the names appear in a different order in column D compared to column F. In column D, where we didn&#8217;t specify an order, it used the default of reading the array by rows. But in column F, it is reading the array by columns.<br><br>Keep in mind that what you see in columns D and F are just the results of the formulas in cells D1 and F1. So they doesn&#8217;t behave the same as if those names were there as text. First of all, if the data in the array changes, the results will automatically update. But you can&#8217;t highlight the names and click the sort button to sort them. And if you try to copy any of the names other than the top one, and paste it somewhere else, nothing will happen. If you try to copy the first name in either column, you&#8217;ll end up copying the formula, not the name.<br><br>So how do you turn the results into a list you can use for other purposes? That&#8217;s where &#8220;Paste Special &#8211; Values&#8221; comes in. Highlight the cells you want to copy, then click the <em>Copy<\/em> button on the ribbon (or right-click and choose <em>Copy<\/em>). Next, right-click where you want your new list, choose <em>Paste Special<\/em>, then choose <em>Values<\/em>. The icon will look like this <img decoding=\"async\" loading=\"lazy\" width=\"23\" height=\"24\" src=\"https:\/\/mcusercontent.com\/0b92990bba1267be26b8ca6db\/images\/97f7feac-c918-b6e8-d8de-6b5bff50c12f.jpg\">. Now you&#8217;ll have static text instead of a formula and you can work with it like you would any data in Excel.<br><br>Do you have trouble remembering how to enter functions like ToCol? Remember that you don&#8217;t have to type them directly. The <a href=\"https:\/\/iqaccountingsolutions.us2.list-manage.com\/track\/click?u=0b92990bba1267be26b8ca6db&amp;id=20d1ac6e70&amp;e=b0476d6293\" target=\"_blank\" rel=\"noreferrer noopener\">Function Wizard<\/a> can help you enter your formula correctly with a simple fill-in-the-blank interface<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Last month I talked about how to use Transpose to copy data from a row and paste it into a column or vice versa. This month we&#8217;ll look at a fairly new Excel function ToCol() that can pull data from several adjacent rows and columns and combine it into one column. (There&#8217;s also a ToRow() [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[9,5],"tags":[],"_links":{"self":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/2619"}],"collection":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/comments?post=2619"}],"version-history":[{"count":1,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/2619\/revisions"}],"predecessor-version":[{"id":2622,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/2619\/revisions\/2622"}],"wp:attachment":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/media?parent=2619"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/categories?post=2619"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/tags?post=2619"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}