1

I would like to have a formula that scans through one column of data, and should this column be non-zero, it registers the contents of a cell in another column, but the same row. I would also like it to trim out the blank cells. Is this possible in Excel 2007?

for example: I have, 

column A    
A
B
C
G
H

column B
1
2
blank
blank
8

If the criteria is a non-zero column B, we have the following output:

column N

A
B
H
Improve this question
2
  • 1
    Wouldn't it be easier if you use filters? (Add filter to column A and B, filter out blanks and 0s, copy the visible contents of column A and paste it in column N, clear the filters).
    – Jerry
    Commented May 7, 2014 at 8:33
  • The end-game of this is to create a system whereby I only need to edit columns A or B for the information to appear in other columns. There will be several versions of "Column N", each with their own columnar criteria. Also, the less manual the work, the better as there will be plenty of information on the spreadsheet!
    – Gokotai
    Commented May 7, 2014 at 8:35

1 Answer 1

Reset to default
2

The usual way to do something like this would be to use INDEX and SMALL in an array entered formula:

=IFERROR(INDEX(A:A,SMALL(IF(B:B<>0,ROW(B:B)),ROWS($B$1:B1))),"")

Put that in the first cell in column N and after typing the formula, don't press Enter. Instead, press Ctrl+Shift+Enter

And then you can drag it down.

Improve this answer
3
  • This works to some degree, but all the outputs are "0" instead of the contents of the corresponding cell in column A. I made sure to press ctrl+shift+enter. Did I miss something?
    – Gokotai
    Commented May 7, 2014 at 8:59
  • 1
    @Gokotai I'm not sure how you are getting '0'. Here's a google spreadsheet sample. There, you cannot Ctrl+Shift+Enter, so that's why I used arrayformula to give the same result (but this doesn't exist in Excel).
    – Jerry
    Commented May 7, 2014 at 9:37
  • I'm not sure why, but shutting down Excel and restarting it seems to have done the trick! Thank you very much!
    – Gokotai
    Commented May 7, 2014 at 11:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.