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

English
, , ,
#1

image description
image description463Ă—686 14.9 KB

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.

1 Like
#3

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
#4

Thanks! This does exactly what I wanted to do.

#5

I love the way this has been working!

I now have another twist. I would like to transfer my order list to the destination sheet in a specific order that is is different from the order in which they appear in the source sheet. In other words I would like to order all the lumber pieces, then all the frame pieces, then the hardware, etc.

I have already created a column which orders the way they should appear but I have been wracking my brain trying to figure out how to use this column.

#6

It’s not very elegant, but I think this modified example does what you want.
RowSelector_Plus_Category.ods (12.9 KB)

I put all the extra calculations in the third sheet, and the second sheet just gives the result. It works similarly to the first example, except this time a sort by category follows the sort by non-zero quantity. Thus the columns on the worksheet are:
A Non-zero quantity ordered
B Sorted indexes to non-zero items
C Category transferred in sorted order
D Local indexes to sorted categories
E Running category count, incremented when no more matches
F Starting index for category search
G Inclusion fields after category sort
H Sorted local inclusion indexes
I Double index to map sorted results back to first sheet indexes

#7

I dfidn’t any sorting!

@catlinger:
A few days ago there was a related request in the OpenOffice forum for which I suggested a solution derived from older approaches simplifying the formula apparatus. It only uses two helper columns.
Assuming you might also be interested in it I reworked the example sheet using your data. I attach the reworked version now here.
disask53852adaptd_from_aoo110131GroupingByOneDataField.ods (17.7 KB)

#8

@Lupp :
My approach uses only two helper columns, or a single one if regular expression matching is enabled. My method is more efficient because it involves only one or two single passes through the rows, O(N), whereas your use of the array and match functions involves O(N-squared) operations.

#9

For a sparse group you will get time complexity near O(N^2) for columns searching selectable entries with a MATCH().
You are right, however, that the COUNTIF() approach I tried the first time for filtering in my above example isn’t very efficient.
I thought it was rather simply to understand anyway.
We can makle it more efficient if we fill the counting column by different means, and reduce the ranges needing to be searched for matches using OFFSET()
See
twoSimilarWaysOfFilteringByFormulas.ods (324.7 KB)

#10

In any case, it would be interesting to see a more efficient solution than mine to the second question, which requires also sorting by category. It would be great if LibreOffice had a sort function similar to what’s found in Google Sheets. I assume there must be some implementation difficulties.

#11
  1. Could you, please, show me the point where “the second question” was asked? I read the “sort through” from the subject line as a sloppy colloquial equivalent of “go through”.
  2. I don’t know “what’s found in GoogleSheets”. I’m approaching 80, and hope I can end my life decently without GoogleSheets and Clouds.
  3. Supposing a sort function would need to output to an array and to lock the range, I see good reasons to NOT implement such a function. If functions should be allowed to output to unlocked ranges may be a difficult question. Its about possible overwriting and about “marking dirty”, I suppose. At least it doesn’t fit into Calc.
  4. Sorting is an extremely carefully investigated task field of the “Art of Computer Programming”. The difficulties concerning implementation are about the need of choosing the most appropriate way under given circumstances if the relevant aspects cannot be cleared generally in advance.
  5. The means integrated with LibreOffice, and available via the API are very well implemented, though. An example with 20000 rows of data (and 10 columns used to create the sort fields) which I made about a year ago returned its three results (a stable ranking, the respective sorting-access permutation, and the sorted output to a dedicated range of columns (no array-formula needed) within about 4 s though the macros I had written for it needed to create, use, and close a hidden Calc model. (If you are interested, you can get the macros -including some helpers needed to get parameter values from a kind of query string- and a reduced example, of course.)
  6. The general assumption sorting should be done with the data we need in sorted order is wrong. Effective sorting may better be done in many cases by creating index permutations based on sort fields. (This is one of my reasons to not study the sorting capabilities coming with Python or whatever.)
#12

This extension adds the functions:
XLOOKUP, FILTER, SORT, UNIQUE, and more. Some of them with partial implementation
https://extensions.libreoffice.org/en/extensions/show/27434

#13

You (@mariosv) are a very experienced user. How would you estimate the value of that extension? What about the “partial implementation”?
The new “modern” functions …
Are they useful or just modern? Are they just toys pobably? Are they clearly specified?

#14

I know about the partial implementation, I have reported some bug, sort takes the numbers like strings.
But for me, the question is if it serves to solve the problem presented here

#15
  1. How to tell a comlpete implementation from a partial one and from a faulty one if there is no specification?
  2. I still feel sure that the questioner used the word “sort” without any respect to actual sorting. Colloquial (US) English uses “get sorted” or similar often as synonymous to “figure out”.
    The only point where he was precise: “I want my finalized list to be without gaps.” That’s about filtering.
  3. How to specify a function like UNIQUE()? I can tell you a hundred reasons for what I would refuse the task. (Types of values, representations of values? “12” = “XII”:melting_face:?). Excelists will give an idiotic example not clearing anything.
#16

The second question is in post #5 above.

I avoid Google as much as possible, but regardless of theoretical concerns, the SORT and SORTN functions in Sheets are extremely useful and I would hope to eventually see the equivalent in LibreOffice. The current work-arounds in LibreOffice are ugly and inefficient.

With the SORTN function the second question can be answered trivially. There’s a single helper column to identify non-zero quantities:
Title
=if(Sheet1!C2>0,1,0)
=if(Sheet1!C3>0,1,0)
…
If that helper column is on Sheet3 then on Sheet2 you duplicate the first row of Sheet1 and in A2 you put:
=sortn(Sheet1!A2:C200,countif(Sheet3!A2:A200,">0"),0,Sheet3!A2:A200,FALSE,Sheet1!B2:B200,TRUE)

Note that Sheets uses “!” instead of “.” to delimit the sheet identifier.

#17

On a 666th attempt even the blind ones see. (Do you also dislike delayed questions?)
Concerning the “extreme usefulness” we may have a private discussion if you come to München. I owe you a beer now.

#2

@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.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.