The copy and paste functions in Excel are perhaps the most
widely used functions, used to replicate data and formulae across ranges in the
same worksheet, worksheets across the same workbook and across workbooks.
However, many a time, due to paucity of data, data has to be entered in bursts,
leaving rows blank. If these data gaps do not get filled up at a later
stage, one may want to copy the non-blank cells to a different range in a manner
that the data appears together. This could actually be useful in reducing
the range, which one has to scroll through in the worksheet. This article
explains the procedure to copy non-blank cells to a different range both
programmatically and non- programmatically.
|
Static pasting
The range A1:A9 contains the original list of numbers and C1:C9 is the result of
static pasting. To copy the non-blank cells in the range (A1:A9) to column
C. Select the range A1:A9 and go to Edit>Go To>Special. Now select
Constants or Formulae (as the case may be) to select the non-blank cells in the
region. Next go to Edit>Copy, select C1, go to Edit>Paste Special, select
Values and click on OK. The problem with this method is that if a new value is
inserted in cell A3, we do not see the change in column C.
Original range is A1:A9. Cells have been left blank as information isn't available | Go to Edit>Go To>Special. Now select the Constants radio button |
For the 'Paste Special' menu, click on the Values radio button and click on OK | The values get pasted in range C1:C6 without the blank rows. It's non-dynamic |
Filtering the list
There is yet another procedure for displaying the non-blank cells only.
However, this procedure is not entirely dynamic
because to revert to the original list, one has to 'Select All' in the
drop-down box in cell A1. For this, cut and paste the range to A2:A10, type in
'Numbers' in A1, select the range A1:A10, go to Data>Filter>Auto
Filter. A drop down arrow will appear in cell A1. Now click on the
drop-down box in cell A1 and select 'Non Blanks'.
To Filter data, we should give a heading to the list. It is always a good practice to clearly identify data columns | Use the Autofilter feature available in the Filter options. This will insert a drop down box in cell A1 |
Select the 'Non Blanks' option under in the drop-down menu available in cell A1 | The list automatically displays the non-blank cells in the region. The blue row number indicates hidden rows there |
Dynamic pasting
The first task is to determine the row number of non-blank cells in the
range A1:A9. This is done by using the following formula in cell B1:
“=IF(A1="","", ROW())”. Copy this down till cell
B9. For blank cells in range A1:A9, a blank appears in range B1:B9. Now to
get rid of these blanks cells, we employ the SMALL() function. In cell C1,
enter the formula: “=SMALL($B$1:$B9,1)”. Copy it down to C9 but
increment the second argument of the small function by 1 each time-to avoid
this, you may type numbers 1—9 in a different range (say range F1:F9) and the
second argument of the small function may draw a reference to that. To
avoid error values from appearing in range C1:C9, use the ISERROR() and
the revised formula becomes: “=IF(ISERROR(SMALL ($B$1:$B9,1)),””,
SMALL($B$1:$B9,1))
C1 to C9 will now have the row numbers of the non-blank cells arranged
consecutively in range A1:A9. To return the non-blank cells in range D1:D9, use
the INDEX function. The formula in cells D1 to D9 after including the
ISERROR function will be: =IF(ISERROR(INDEX($A$1:$A$9,C1,1)),””,
INDEX($A$1:$A$9,C1,1)).
Now changes in A3 are reflected in D1:D9. The
completed formula in column
D is as follows: “=IF(ISERROR(INDEX ($A$1:$A$9,SMALL($B$1:$B9,F1),1)),
””,INDEX($A$1:$A$9,SMALL($B$1:$B9,F1),1))
D1:D6 shows non-blank cells got programmatically. Any data, changed or entered in the range A1:A9, will reflect range | The formula looks difficult to use initially, but with some practice and knowledge of functions, one can get comfortable |
Ashish Mathur, Business Analyst, Pulsar Knowledge Center