[Solved] Calculating Most Reoccurring Text

[Myrm]

Hi

I am currently creating a database of customers and products sold. I am after a formula that will look at all the customer names in a given column, and return the customer name that appears most often. I managed to do this with Excel 2007 using the following formula;

=INDEX(A1:A27,MATCH(MAX(INDEX(COUNTIF(A1:A27,A1:A27),0)),INDEX(COUNTIF(A1:A27,A1:A27),0),0))

Unfortunately Calc doesn't like it.

Any suggestions please?

Thank you.

[Hagar Delest]

Have you tried to open the file with Calc or have you pasted the formula in the Calc file ?
Basically, comas in Excel are replaced by semi-colons in Calc. Changing the comas in your formula should be worth the try.

[Myrm]

Hello Hagar

Thanks for the suggestion, but replacing the commas with semi-colons did not work

I cut and pasted the formula from Outlook 2007.

[Villeroy]

This office includes a database component. A spreadsheet is no database.
Your formula can not work with Calc nor Excel.
After replacing all commas I analysed the formula with the formula wizzard (Ctrl+F2).
It includes an INDEX call
INDEX(COUNTIF(A1:A27;A1:A27);0)
with a number as first argument (return value of COUNTIF) and a row index of zero.

[Myrm]

Hi

Thanks to everybody for their help above.

I have worked out how to do what I wanted using "PilotData", which appears to be the OO equivalent of Excel's "Pivot Tables".

Villeory; you stated that the formula I pasted from Excel cannot work. Sorry mate, but it has been doing the job I required of it in Excel 2007 for the past few months.