Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

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.

share|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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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