Search This Blog

Friday 20 July 2012

PASTE NON BLANK CELLS ONLY USING EXCEL



Array Formula in B1:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(A:A,SMALL(IF($A$1:$A$10<>"",ROW($A$1:$A$10)),ROWS($B$1:B1)))))      

Press Ctrl+Shift+Enter and drag it down to B10.

Tip:   This is also useful when you create a data validation list from a range with blank cells in it. In that case you can create a new range with non-blank cells using above formula and then use this new range for data validation.


No comments:

Post a Comment