AGGREGATE Function Errors out in Calc, NOT in Excel

English
,
#1

I am trying to find nth occurrence of a text entry in an array

The AGGREGATE function I have used in Excel works beautifully but when I try the same in Calc, it does not work.

The post at

says this is because Calc handles the DIV#0 error differently than Excel
That posting is marked as outdated, so I am hoping a solution has been discovered since then.

However,in troubleshooting with a new worksheet, I tried using a helper column - which I CANNOT do for my actual application because I have over 1,000 columns and 100 rows with reference to another worksheet with the same layout - and it handled the DIV#0 error just fine when the error was in the array being referenced rather than embedded in the AGGREGATE function. Excel handles the DIV#0 error in either scenario.
For the simple, trouble-shooting spreadsheet I have

Names = array of names (C2:C41 if it matters)

Search = the text I am searching for ((H2)

Nth_Find= the Nth occurrence number N

Row_Number_Of_Nth_Occurrence=AGGREGATE(15,6,ROW(Names)/(Names=Search)-ROW($C$2)+1,Nth_Find

Works great in Excel
Errors out in Calc
When I put the “ROW(Names)/(Names=Search)-ROW($C$2)+1” in a helper column and use it as the reference in the above formula it worked.
But as said above, I cannot use Helper columns in this instance.

I want to use a Function-based solution, NOT an Array-Function-based solution since the Array-Function solution is too resource intensive for use in thousands of cells in a worksheet.

I can get this to work with a convoluted SUMPRODUCT formula but if possible, would like a simpler AGGREGATE formula with fewer calculations.

=SUMPRODUCT(SMALL((Names=Search)*(ROW(Names)-ROW(C2)+1),Nth_Find+COUNTIF(Names,"<>"&Search)))

Any suggestions for how to use the AGGREGATE function in Calc to find the Nth occurrence of a text item in an Array?

Using Sumproduct or Aggregate with Index to find nth occurance.xlsx

#2

Could you attach that small/simple test sheet to your question?

Edit your question posting (do not post as an answer) and use the paperclip icon to upload attachment.

This helps us to understand your data, and also relieves us from the chore of recreating a test subject to use for our suggestions. More resources spent on actually trying to solve your problem.

#3

Sample (simple) spreadsheet attached

#4

Works perfectly for me.
See nthOccurrenceByAggregateSmall.ods

(Please also consider what I wrote in Why do I often get an error (508, 504, 502 e.g.) if I paste a Calc formula from some post into my sheet? .)

EDITING a few minutes later:
Puzzle solved: It was tdf#129681 only recently fixed starting with V7.1.

EDITING the day after:
Missed to mention that there is a solution based on TEXTJOIN() and REGEX() avoiding the (slightly cryptic) AGGREGATE().

#5

I read your post and updated my LibreOffice Calc settings as shown in your post.

I downloaded your sample sheet and when I opened it, all looked oggd.

I then updated the value of N and the result came back with a #DIV/0! Error in the reuslts cell.

Did you try updating the N criteria after creating your formula?

Have you set additonal configuraitons that might make it work on your system and not mine?

You also incuded, in the cell below the selection criteria for N, a cell with the formula
=HYPERLINK("#" & ADDRESS(I2;COLUMN(F2));“Jump!”)
Not sure if that was just you playing around or if that was a requirement for it to work.

Thank you for the quick reply. Hopefully we get this nailed!

R

#6

By the way: The example attached by the OQer contains the misleading remark: “Note: SMALL function is NOT case sensitive; George = george”.
SMALL() (function number 15 when called via AGGREGATE() doesn’t work with text at all. In the given case it works with the row numbers of the array ‘Names’. If the setting for comparisons in Calc is Case-sensitive, and the formula should work NOT Case-sensitive, the two string expressions compared there by = must both be forced to either upper case or lower case. ((Upper(Names)=Upper(Search)) e.g.)

#7

I then updated the value of N and the result came back with a #DIV/0!

What version of LibreOffice do you run? There may have been a bug. The second argument passed as 6 to the AGGREGATE() function should suppress errors in LibO as well - and surely it does on my system with LibO V 7.1.1.1). I will soon test with a version of the series you tell me in response to my question.
(I meanwhile also tested with your example file, and it worked as expected.)
Anyway: Always save your documents to native ODF files (in this case .ods)

#8

My error in how I represented.
Also, this was created and documented in Excel so there may be differences.
What I should have said was the comparator used in the Small Function (array=“value”) was not case-sensitive.
Thank you for correecting what was, in its oringinal form, wrong.

#9

@Lupp (not sure if this is correct protocol in this forum)
My version is 6.4.6.2, the default install with Linux Mint 20.1

#10

@Lupp

I did save it as .ods and it gave the error so I then tried saving as .xlsx to see if it made a difference.

It did not.

#11

Linux repositories often are “conservartive”.
See my edited answer.
I had tested both examples with V5.4.4.2 meanwhile and also got the error.
An earlier patch for V 6.5 seems to have gotten a replacement.

In addition: My example used the function RANDBETWEEN.NV() (non-volatile) to create refreshable examples. The function only was implemented recently.

#12

The fix should be in 7.0 already, the bug’s 6.5.0 target was before the decision to make the next release be 7.0

#13

There is a bug in the version of LibreOffice Calc that is installed by default with Linux Mint 20.1
See the line of the other answer:
Puzzle solved: It was tdf#129681 only recently fixed starting with V7.1.
https://bugs.documentfoundation.org/show_bug.cgi?id=129681

Thank you all for such a quick and helpful set of responses

R

#14

You may click the checkmark left of the answer to show that the question has an accepted answer.

#15

I cannot Accept the answer

I tried and was told I cannot accept my own answer

This is my first posting in this forum so I gues I havee not earned enough “trust” points to do that.

#16

Currently I also cannot accept your answer for reasons unknown to me. Probably I need to wait some days (till 2021-03-17 I was told).
You might have been able to accept my answer which shows (about) the same information.

#17

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