Clearing out spaces

S

Simon

I have a excel sheet that has a list of products in a column to which a user
can put a y in the next column to the right if they require that item i.e:
A B
1 Product1 y
2 Product2
3 Product3 y
4 Product4
On a neighbouring sheet I have a column in which each cell contains an IF
statement that says if the cell in the first sheet displays anything give
the Product name to the left. I end up with:
A
1 Product1
2
3 Product3


The problem I have is that that I need to convert this column with gaps in
into a new column, listing only the selected products without the gaps. It
needs to be automatically on document save or close. There is a good way of
doing this out there somewhere but I have no idea what it is. Please Help!
 
T

Teethless mama

=IF(ISERR(SMALL(IF(rngB="y",ROW(INDIRECT("1:"&ROWS(rngA)))),ROWS($1:1))),"",INDEX(rngA,SMALL(IF(rngB="y",ROW(INDIRECT("1:"&ROWS(rngA)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top