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.

It's my table in Sheet1:

A         B          C            D
------------------------------------------
1        220        John         Good
2        210        Jack         Bad
3        300        Mike         Good
4        473        David        Very Good

I want to create a list of Good and Very Good records in Sheet2. For example:

A         B          C            D
------------------------------------------
1        220        John         Good
2        300        Mike         Good
3        473        David        Very Good

Which formula should I use to fill each row? (autofill)

share|improve this question

migrated from stackoverflow.com Mar 19 '13 at 1:55

This question came from our site for professional and enthusiast programmers.

    
what's you basis for the decision. What have you tried? –  Brian Vanderbusch Mar 18 '13 at 18:19
    
@BrianVanderbusch: I tried this on B1: =IF(OR(D1="Good"; D1="Very Good"); B1; "") but it puts empty string if D1 is bad. And won't go to next row (and check again) –  kikio Mar 18 '13 at 18:22

3 Answers 3

You could use the advanced filter functionality in Excel, and set Sheet2 as the filter location. Data > Sort & Filter > Advanced > Check the "Copy to another location" radio button.

You will need to create criteria for this. In this case, create an additional three rows above your table and set the corresponding columns to:

="=Good" and ="=Very Good"

It will look like this (note that you need the blank row in between the criteria and the data, as well as the column labels for the criteria):

A         B          C            D
------------------------------------------
                                 =Good
                                 =Very Good

A         B          C            D
------------------------------------------
1        220        John         Good
2        210        Jack         Bad
3        300        Mike         Good
4        473        David        Very Good
share|improve this answer

Enable Autofilter with Alt - D - F - F or select the Filter button on the Data ribbon while the cursor is in the list. Then use a text filter on Column D to filter with "Contains"

enter image description here

share|improve this answer

Very similar to first two answers (though perhaps a little simpler, if consequently less versatile) but ensuring original data is preserved and with renumbering as indicated in question:

  • Copy Sheet1 into Sheet2.
  • Select Sheet2 ColumnD.
  • Data > Sort & Filter – Filter.
  • Click on the down arrow, click on Select All and “Bad”, OK.
  • Delete all visible rows except first (ie those numbered in blue).
  • Click on Filter.
  • Select ‘A1’ and with Ctrl depressed, drag down as far as required.
share|improve this answer

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.