How can I sort through a sheet and copy the qualifying rows to another sheet?

I would like to sort through one sheet and create a list with the rows that have a quantity of >0 onto another sheet.

I would like to go through my material list and create a material order on another sheet of all the materials that I need (anything with a quantity greater than o).

My question is how to create this list. Is there a way to conditionally hide the non-qualifying rows. Can I just copy the qualifying rows onto another sheet? I want my finalized list to be without gaps.

I am trying to do this without using macros, although if there is an easy way with macros I am open to the idea. I have an extremely limited knowledge of macros.

There’s a fully automatic method that relies on matching and indexing. To use the “match” function without enabling regular expressions, you would add a condition column on the first sheet that has one particular value when the row is to be transferred to the second sheet. For example, if “qty” is the fourth column then the condition column could use: IF($D4>0;“1”;""), which is filled down the condition column to cover every item—the row value uses relative addressing.

On the destination sheet the first column, which can be hidden later, contains the row numbers matching the desired condition. The range for the “match” function is changed for each row such that you’re searching for the next instance of the desired condition. Start by putting number “1” in $A$1. In $A$2 put:

=MATCH(1,INDIRECT("$Sheet1.$e$"&$A1+1&":$e$5000"),0)+$A1

assuming column “E” is the condition column. The contents of $A$2 are then filled down the column as far as necessary to match the complete parts list. Subsequent columns are filled with formulas in this form to transfer the rows from the first sheet:

=IF(ISNUMBER($A1),INDEX($Sheet1.$B$1:$B$32000,$A1,1),"")

Sample attached…
RowSelector.ods (11.1 KB)

The condition column on the first sheet can be eliminated if you enable regular expression matching for the “match” function. Then you can craft a regular expression to match non-zero numbers.

1 Like

Thanks! This does exactly what I wanted to do.

@Ed_one,

Two semi-manual options:

A.

  • Unmerge Item name and qty cells and put the data in the row 2;
  • Select the range of cells from row 2 to row 36;
  • In menu Data select AutoFilter;
  • Select the arrow near qty and uncheck the box near the “0”;
  • Pulse OK
  • Copy and paste wherever needed.

B.

  • Select the range of cells from row 3 to row 36;
  • Open menu Data - More Filters - Standard Filter, in Field name chose Column x (where x is the letter name of the fourth column), in Condition chose >, and in Value choose 0;
  • In Options choose Copy results to: and using the Shrink button chose a cell to be the upper left corner of the result range.

You could edit your question, or comment an answer. Do not use Answer to comment.

Mark the circle to the left of the answers that solved your question.