[Solved] [Tutorial] Sorting and Filtering data with formulas

[jd-oo-user]

Hello, I'm struggling here. Any assistance greatly appreciated.

Full disclosure, I'm not an expert in spreadsheets, but I'm able to muddle through some basic formulas.

I read a good tutorial in "Getting Started" > "Tutorials" > "Calc", thread: [Tutorial] Sorting and Filtering data with formulas.

In the tutorial; final post in that thread, I used the updated example spreadsheet: Sorting and Filtering data with formulas1.ods

Worksheets of focus: "Automatic subset and sort" and "MultiKey SubRank"

The "Automatic subset and sort" worksheet is almost what I'm looking for, which is to automatically populate rows, based on criteria. The issue is, I cannot for the life of me figure out how to include additional corresponding row data. For example, in the other worksheet: "MultiKey SubRank" (which I've modified in my attachment), the data that matches has it's corresponding data in the same row (cols: SortA, SortB, ...).

Is it possible for the "Automatic subset and sort" worksheet to be expanded to include more cols of corresponding data? For example, in the modified sheet, I added a "Data2" column, with various strings. For example, in the "Ascending odd" column, I2 (val: 1), would have the corresponding value "Yellow" from col: Data2 placed in "Ascending odd (Data2)", and so on. I've added the values in manually in "Ascending odd (Data2)", so that it'd be clearer what I mean.

Note, you can probably see where I'm going with this based on the "MultiKey SubRank" modified worksheet, and where I have some cells already modified to calculate only within a certain Date range based on the date values in U2, V2. Eventually, if the automatic sheet can be expanded, I'd like to replace the "ISODD" function with a Date range IF condition or something to only select dates within a specific range, and then all the corresponding data in other cols populate accordingly.

Any help greatly appreciated.

Thank you for your time,
-Jd-oo-user

[Lupp]

(Using named expressions may shorten formulae. It requires to study the definitions if someone tries to understand the functionality.)

I am a bit tired to study examples of the kind again and again. Concerning my suggestions I would prefer to turn the tables, and to invite you to study another example of mine. It is derived from a "reusable" variant by adding a sheet demonstrating the selection of additional columns to be displayed for a selected and sorted subset of the original data.

There are many ways to add even more functionality, of course. One enhancement actually needed sometimes is the provision of more than one selector making the selector column selectable itself. This can be done in a similar way. The formulae will get more complicated and the files larger, however.

[jd-oo-user]

Thank you very much Lupp for your prompt reply and example spreadsheet.

Your example file looks to solve my issue. However I'm doing this for a client who is using Excel. I cannot afford Excel, so I'm using OpenOffice. I basically need a way to source a date range (in this instance anyway, if compatibility can be resolved, apply similar methods for other sheets), and then only show relevant data (with additional related row data, there's 8-9 cols of raw data in this situation).

The issues:

In the worksheet: SortedSelectionByFormulaeAndSel

When I saved this as XLS using OpenOffice (I even tried in LibreOffce and saved it as XLSX, XLSM, but same results), and tried it in MS Office Starter (2010), columns W, X, Y were missing rows. It appears to be an issue with T3 & T14 (showing #VALUE), "A value used in the formula is of the wrong data type", error message (!).

Cols L, M do however show the correct number of rows matched based on the set criteria (J5).

Is there any possible way to resolve this to be compatible with Excel?

Also, I haven't looked closely yet, but is it difficult to add additional DATA columns? The raw data I'm using has 8-9 or so columns, and if I can get this to scale properly, I'd like to utilize the method with other data sets.

Edit: Added a Data3 column and copy/pasted the formula and modified the offset by adding a +1 accordingly (modified file attached). Kinda excited that it worked, /grin. Just waiting for Lupp (/duck, no pressure), or some other OO guru to help with the Excel compatibility issue.

Edit2: Data3 col I added works except it's showing a 0 (zero) when there's no corresponding value in the source data3 cell. I tried to nest an IF condition to account for it, which kinda worked, except all the remaining cols in the output which are not matches displayed as #N/A, which is visually not appealing. A work in progress here.

Thank you again for your time,
-jd-oo-user

[jd-oo-user]

Update:

It appears the results in Excel with regards to the sorting are always having issues with the FIRST and LAST rows of the T3 and T14 (using original Lupp spreadsheet), or using my modified attached spreadsheet it would be V3 and V7 ("To" col). I think it has something to do with SUMPRODUCT or maybe the OFFSET putting the cell in a location that contains no value (or a string?) and then I get the #VALUE errors. It clearly works for the output rows aside from first and last though. Perhaps there's a way to account for this with an IF condition or something. I've been Googling for solutions, but it's difficult to narrow this down. From my search so far, it may be something to do with SUMPRODUCT, but I don't understand enough yet to fix it.

Any help appreciated, thanks.
-jd-oo-user

[jd-oo-user]

Update:

Re: The FIRST output row in the sorted area #VALUE issue. I "think" it has something to do with the second SUMPRODUCT returning something other than 0 (non-int maybe a string). I can't tell what's going on because in Excel Starter (2010), there's no formula wizard to step through and see what the outcome value is.

Anyway, totally frustrated, spent hours on this without much progress. Breaking for lunch.

Any help appreciated, thanks.
-jd-oo-user

[jd-oo-user]

Update:

Attached is another modified version. In Column S, of worksheet: "SortedSelectionByFormulaeAndSel", I've broken down the formula in the last row result in V8 where #VALUE appears (when run from Excel). Basically took each function, pasted it in another cell to see the result, for comparisons of EXCEL vs CALC. This may be straight forward for someone knowledgeable with these functions and can perhaps figure out what the hell is going on. Clearly it's behaving differently between the two programs. What's the solution, nfc. Beyond fucking annoyed at this point.

Any help appreciated, in a bind here.
-jd-oo-user

[Lupp]

I assume your notes in S13 and below concernt the formulae to calculete the TargetIndex 'To' for the sorting in column V. (For pointing me to the source of something addresses are useful.)
For spreadsheets complying with OpenDocument specifications part 2 ("OpenFormula") defines the parameters of SUMPRODUCT to be 'ForceArray'. This means they are to be evaluated in array mode under any circumstances. Did you consider / test whether or not Excal 2010 does behave compliant insofar, too.

I didn't use Excel for decades now, and I have no access to it. But: You may try to enter the formula in V3 with Ctrl+Shift+Enter expressly for evaluation in array mode. Having done so please note that thze filling down will work correctly if done via the menu. When dragging with the mouse you need to keep Ctrl pressed in addition.

(The date format i set in my demos is the internationally standardised format, and actually preferrable. See ISO 8601. It is the only date format specified for odf complying spreadsheets to be recognised inependent of the locale.)

[Villeroy]

Excel's SUMPRODUCT does not evaluate boolean results. SUMPRODUCT(condition1 ; condition2 ; condition3) needs to be written as SUMPRODUCT(condition1*condition2*condition3). The arithmetic operator * enforces arithmetic evaluation. Using the right tool, sorting and filtering are trivial. Formulas are not the right tool.

[MrProgrammer]

Hi, and welcome to the forum.

The issue is, I cannot for the life of me figure out how to include additional corresponding row data. … Is it possible for the "Automatic subset and sort" worksheet to be expanded to include more cols of corresponding data?

Sure. Open the attachment below and select the Calc sheet. I removed the defined names and used the specific cell ranges.

F has the odd numbers:           =IF(ISERROR(En);"";INDEX($A$2:$A$21;En))
G has the corresponding colors:  =IF(ISERROR(En);"";INDEX($B$2:$B$21;En))
J has the sorted odd numbers:    =IF(ROWS(F$2:Fn)>$H$2;"";INDEX($F$2:$F$21;MATCH(COUNT(I$2:In);$I$2:$I$21;0)))
K has the corresponding colors:  =IF(ROWS(F$2:Fn)>$H$2;"";INDEX($G$2:$G$21;MATCH(COUNT(I$2:In);$I$2:$I$21;0)))

However I'm doing this for a client who is using Excel.

The tutorial is written for Calc, of course. I don't have Microsnot Excel and can't test a solution for it but give the second sheet (Excel) a try. I've made some modifications in it to the SUMPRODUCT functions in column I which may allow it to work with that software.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

I'm able to muddle through some basic formulas.

[Tutorial] Ten concepts that every Calc user should know

[Lupp]

In the example under discussion SUMPRODUCT is only used with one parameter. The intention is to take advantage of the ForceArray specification. Explicit array formulas are a bit unhandy under some respects.
I would expect Excel to also correctly evaluate SUMPRODUCT(1*condition) then. Otherwise SUMPRODUCT(IF(condition;1;0)) will do.

The interactive tools for filtering and sorting are often useful. However, they raise breaches of the original paradigm of spreadsheets which is the equilibrium maintained by recalculation of formulas after changes to cell contents. Strict observance requires the usage of formulas for every task. At least the use of interactive means is a risky extension of the concept of changing contents. Yes. Tools for data import are of the same kind.

[jd-oo-user]

Excel's SUMPRODUCT does not evaluate boolean results. SUMPRODUCT(condition1 ; condition2 ; condition3) needs to be written as SUMPRODUCT(condition1*condition2*condition3). The arithmetic operator * enforces arithmetic evaluation. Using the right tool, sorting and filtering are trivial. Formulas are not the right tool.

Thank you Villeroy for replying and the explanation. From my searchers, it seemed the culprit was as you described. I ended up going with a double unary minus operation to coerce the the boolean to int. From my reading, it seems like the most efficient way is to use a double unary. Personally, I think it's less confusing, then using some other random arithmetic operator. Especially if someone looks it over and wonders why it's there. The double unary stands out.

All the best,
jd-oo-perl

[jd-oo-user]

Hi Lupp,

I assume your notes in S13 and below concernt the formulae to calculete the TargetIndex 'To' for the sorting in column V. (For pointing me to the source of something addresses are useful.)
For spreadsheets complying with OpenDocument specifications part 2 ("OpenFormula") defines the parameters of SUMPRODUCT to be 'ForceArray'. This means they are to be evaluated in array mode under any circumstances. Did you consider / test whether or not Excal 2010 does behave compliant insofar, too.

I didn't use Excel for decades now, and I have no access to it. But: You may try to enter the formula in V3 with Ctrl+Shift+Enter expressly for evaluation in array mode. Having done so please note that thze filling down will work correctly if done via the menu. When dragging with the mouse you need to keep Ctrl pressed in addition.

(The date format i set in my demos is the internationally standardised format, and actually preferrable. See ISO 8601. It is the only date format specified for odf complying spreadsheets to be recognised inependent of the locale.)

Thanks for the suggestions, I didn't know about expressly evaluating with CTRL+SHIFT+ENTER. I don't normally use Excel either, it's only because I'm redesigning a spreadsheet for a client which uses Excel; so I need to ensure it works in that environment. I'm using Excel Starter (2010), which came with another laptop, but it's limited in functionality.

By the way, sorry about the formula breakdown/notes in the S column, it was a rush job. Next time I'll label each address location clearly. /blush.

In the example under discussion SUMPRODUCT is only used with one parameter. The intention is to take advantage of the ForceArray specification. Explicit array formulas are a bit unhandy under some respects.
I would expect Excel to also correctly evaluate SUMPRODUCT(1*condition) then. Otherwise SUMPRODUCT(IF(condition;1;0)) will do.

The interactive tools for filtering and sorting are often useful. However, they raise breaches of the original paradigm of spreadsheets which is the equilibrium maintained by recalculation of formulas after changes to cell contents. Strict observance requires the usage of formulas for every task. At least the use of interactive means is a risky extension of the concept of changing contents. Yes. Tools for data import are of the same kind.

I ended up using a double unary (--) with SUMPRODUCT to coerce the boolean. As I mentioned in my reply to Villeroy, it seems the best way for this type of thing.

Originally, I had tried the filtering/sorting tools. But then I discovered they don't automatically update if I added to the data. Being this is for a client, my goal is to only display the information relevant to the current pay week for example. This way he will be able to enter a pay week, and the corresponding information will populate. I'm not sure how far I can scale this though, because based on the populated data, I will need to join in other worksheet data points based on the populated rows... In other words, a pay week results in a bunch of employees, with a few corresponding columns of data. But I can maybe use a VLOOKUP or something to match each employee name with another worksheet which has: Employee Name, Hourly Wage, Date. That sort of thing, and another Payroll worksheet which contains hours worked by each employee. Anyway, difficult to describe here without an example. The goal today is to try and make up lost time and adapt your original spreadsheet concept; adjust for Excel compatibility, expand the data columns accordingly and test functionality. Then from there, attempt to incorporate a lookup based on the populated results. I'll probably end up posting again here (in another thread), because I'm bound to run into some issues. Also, another set of eyes from experts; such as you guys/gals, would be ideal as well.

All the best,
-jd-oo-user

[jd-oo-user]

Hi, and welcome to the forum.

The issue is, I cannot for the life of me figure out how to include additional corresponding row data. … Is it possible for the "Automatic subset and sort" worksheet to be expanded to include more cols of corresponding data?

Sure. Open the attachment below and select the Calc sheet. I removed the defined names and used the specific cell ranges.

F has the odd numbers:           =IF(ISERROR(En);"";INDEX($A$2:$A$21;En))
G has the corresponding colors:  =IF(ISERROR(En);"";INDEX($B$2:$B$21;En))
J has the sorted odd numbers:    =IF(ROWS(F$2:Fn)>$H$2;"";INDEX($F$2:$F$21;MATCH(COUNT(I$2:In);$I$2:$I$21;0)))
K has the corresponding colors:  =IF(ROWS(F$2:Fn)>$H$2;"";INDEX($G$2:$G$21;MATCH(COUNT(I$2:In);$I$2:$I$21;0)))

Hi MrProgrammer,

Thanks for the explanations. I forgot to edit my OP, but did edit my followup post where I was able to expand the functionality using Lupps spreadsheet example. But I'm also keeping your attachment on hand, as it doesn't include the defined names, and I'm not sure if those are compatible with Excel anyway.

However I'm doing this for a client who is using Excel.

The tutorial is written for Calc, of course. I don't have Microsnot Excel and can't test a solution for it but give the second sheet (Excel) a try. I've made some modifications in it to the SUMPRODUCT functions in column I which may allow it to work with that software.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

I'm able to muddle through some basic formulas.

[Tutorial] Ten concepts that every Calc user should know

Thanks for the SUMPRODUCT modifications. I ended up using a double unary operation, as mentioned in my previous replies. It's going to be easier for me to remember why that was there, as opposed to some other arithmetic operation.

All the best,
-jd-oo-user

[jd-oo-user]

I just wanted to take a moment to thank everyone for their patience and prompt replies to my questions/issues. I suspect I'll have more in the future. /blush.

I've marked this thread as "Solved", and selected a green checkmark, per the guidelines.

All the best,
-jd-oo-user