Apache OpenOffice Community Forum

Hi all.
I've been asked to sort members of our community shed (like a men's shed but with women as well) by their birthdays, so the closer it is to the big day, the further up the table they go. This means a free bacon roll in the café with a candle on top.

It sort of works - as with everything I try.

Sheet 1 (Membership) is where the members data is entered
Sheet 2 (Birthdays) is where I try to do some magic.

The member names are fictitious, but the dates (or lack of them) are real.
Columns I to N are the helper cells and columns A to E are the result.

For those who opt out of giving their birthdays (3) I make their birthday today minus 367. The results are good enough - they're always at the bottom.
I use MATCH to find which rows the fields are to go into. I did use SMALL, but the results are the same.

Nicholas Nickleby and Ray Sleeder both have their birthday on the 16th June. Nick Nickleby is shown on the results twice and Ray Sleeder not at all.
Not quite sure how to check for that.. (no idea how to)

Same with the three non birthdays. Eugene comes up three times whilst the ghostbuster and the happy Allota are missed.
Any chance there is a solution to this problem?

Thanks in advance.

Fitch wrote: ↑Mon Oct 28, 2024 8:37 pm I use MATCH to find which rows the fields are to go into. I did use SMALL, but the results are the same.
Nicholas Nickleby and Ray Sleeder both have their birthday on the 16th June.
Any chance there is a solution to this problem?

When there are duplicates, neither MATCH nor SMALL can be used to sort the birthdays with formulas.
Study [Tutorial] Sorting and Filtering data with formulas, especially the February 2014 post.

Fitch wrote: ↑Mon Oct 28, 2024 8:37 pm I've been asked to sort members … by their birthdays, so the closer it is to the big day, the further up the table they go.

[Tutorial] Calc date formulas, section X
Age: =YEARS(birthdate;TODAY();0)
Days to next birthday: =EDATE(birthdate;age*12+12)-TODAY()

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

Confession:
I downloaded the tutorials a few years ago and then promptly forgot them.

Got myself in a mess now as date of birth and surname concatenated just results in a mess, and I have to subtract 69 from the sumproduct result

Columns P onwards on the Birthdays sheet is my utterly feeble attempt.

Hallo

maybe upgrade to LO24.8:

I created this with formulas straight from February 2014 tutorial attachment Sorting and Filtering data with formulas1.ods sheet MultiKey SubRank. For this to work the formulas must use $ correctly. Pay careful attention. The Ten Concepts tutorial explains $. This multi-key ranking method should work with any OpenOffice or LibreOffice version. It just needs SUMPRODUCT, MATCH, and INDEX, all of which have been available for 20 years.

Edit: The same basic method works in Numbers (Apple) or Excel (Microsnot), except that in those products SUMPRODUCT cannot be used with boolean expressions. But COUNTIFS can provide the same result. Example for H2: =SUMPRODUCT(B2>$B$2:$B$30;F2=$F$2:$F$30) becomes =COUNTIFS($B$2:$B$30;"‍<‍"&B2;$F$2:$F$30;F2). In this particular spreadsheet the YEARS function has to be replaced with DATEDIF.

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

Thanks.

I managed to figure it out with your latest download.

Cheers!