I have a range of data in multiple rows, for example B:G; A is the Header. Some cells in the range are blank:
| A | B | C | D | E | F | G | 1 | HEADER | | Green | | Blue | | | I need a formula to copy all the values into another place, consequently, starting from B, ignoring the blank cells:
| A | B | C | D | E | F | G | 2 | HEADER | Green | Blue | | | | | Can anyone help me please?
3 Answers
In Excel 2013, the following array formula should work:
I1: =INDEX($A1:$G1,1,AGGREGATE(15,6,1/(LEN($A1:$G1)>0)*COLUMN($A1:H$1),COLUMN(INDEX($1:$1,1):INDEX($1:$1,COUNTA($A1:$G1))))) The AGGREGATE function returns an array of the column numbers that contain data (and the function has an argument to exclude errors, so we don't return the empty columns), and the INDEX function returns the value in those columns.
In Excel 2013, you may need to "confirm" this array-formula (after selecting I1:O1) by holding down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula as observed in the formula bar, and you should see all the results
You can then fill down as far as needed.
NOTE If you don't want to use a CSE formula, you can use the INDEX function to return each element of the array. eg:
I1: =IFERROR(INDEX(INDEX($A1:$G1,1,AGGREGATE(15,6,1/(LEN($A1:$G1)>0)*COLUMN($A1:H$1),COLUMN(INDEX($1:$1,1):INDEX($1:$1,COUNTA($A1:$G1))))),COLUMNS($A:A)),"") Fill right to O1. Then select I1:O1 and fill down as far as needed.
If you use latest version of Excel, you can use FILTER function:
=FILTER(A1:G1,A1:G1<>"")
You may use "Go To" to select non-empty cells.
Select the cells from B1 to G1 > Press Ctrl+G > Specilal > Check the box of Constants > OK. Then copy non-emptu cells to Row 2.
