up vote 0 down vote favorite
share [g+] share [fb]

Say source data is like

col1   col2
a1     2
b1     
b2     2
c1     1
d1     
d2     2
d3

I want to make another list that looks like 

col1   col2
a1     2
b2     2
c1     1
d2     2

How to do this? Thanks.

link|improve this question
81% accept rate
feedback

1 Answer

up vote 2 down vote accepted

You can do this in situ by converting your range to a list and then filtering on col2 to not show blanks.

link|improve this answer
Good idea, thanks. What if I need the original list remain as it's and have a copy (reference to the list) in another sheet and use the filtering function. How to do that? – Stan Jul 5 '10 at 3:55
Moreover, I want to use this filtered list as a source for a pivot table. How to do that? – Stan Jul 5 '10 at 4:02
2  
@Stan: To address your second comment, you can go directly from the original source data straight to pivot table. Hide the 'empty' values by setting 'filter on values' on the pivot table's row labels with the rule 'sum of col2 is greater than 0'. – Mike Fitzpatrick Jul 5 '10 at 4:29
feedback

Your Answer

 
or
required, but never shown
discard

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