Apache OpenOffice Community Forum

I'm not finding anything for what I'm trying to do in searching, but honestly am not certain I'm even searching for the right terms, so I'm hoping this isn't a duplicate. What I have is collection of ticker codes and I've been using data validity to create a drop down list, then using in another cell the countif function to count how many times that ticker is found in the range. What I'd like to do is have it list out which ticker codes show up the most often. I've uploaded a truncated version of the file, the ticker section is in columns AR - AV.

  AR          AS          AT          AU          AV
1│EFT Options│Search for:│MLPX       │3          │Occurances│
2│MLPX       │MDST       │TPYP       │USAI       │EINC      │
3│MLPX       │MDST       │EINC       │UMI        │TPYP      │
4│UTES       │DIVP       │ECLN       │RSPU       │BDVG      │
5│TDIV       │TDVI       │IWTR       │SOXX       │PSI       │
6│BEEZ       │TBG        │MLPX       │SAMM       │LOPP      │
7│IDGT       │DTCR       │CRED       │DTRE       │SRVR      │
8│SAWS       │PSCT       │CAFG       │SIXS       │FSCS      │
9│JGRW       │IGV        │FINX       │CLOD       │CWS       │

So if you know a specific ticker you can enter it in AT1, and AU1 will spit out how many time it appears. But if I'm searching for the top say 5, I'd have to search for each code and then track how many times each shows up. Very cumbersome. I've thought about trying with conditional formats to highlight but it would only work for certain ones, or sorting but since they could be in multiple columns that wouldn't work either.

I'm not the most proficient with the more advanced functions but am always trying to learn how to make it more functional. Is something like this even possible?

Or perhaps to have a list generate on a different page that removes duplicates but then sorts all of the tickers in order of frequency?

gwystyl wrote: ↑Thu Jan 09, 2025 11:09 pm What I'd like to do is have it list out which ticker codes show up the most often.

There is no good way to do that when you store the codes in five columns. If they were in one column, with a column header, you could create Calc's pivot table feature. Change the table's summary function from Sum to Count, as explained in this link.
Calc Guide Chapter 8 - Using a Pivot Table   (formerly called Data Pilot)

gwystyl wrote: ↑Thu Jan 09, 2025 11:09 pm Or perhaps to have a list generate on a different page that removes duplicates but then sorts all of the tickers in order of frequency?

If the codes were all in one row, you could use the TRANSPOSE function on a different sheet to put them in a column and create the pivot table there. The link above explains how to sort the pivot table in descending order by count.

[Tutorial] Ten concepts that every Calc user should know

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.

Thank you for the response, if they were all in one column though then that would require multiple rows for each entry which would ruin the ability to sort and filter the rest of the sheet (including these rows).

What I've done when I need to is take the five columns, paste them all down into one long combined column, then sort, add a countif function in column B, and then sort descending by that result. I'm just hoping there is a way to automate that, but perhaps it would require a separate sheet.

hhmmm I guess a pivot table could work, if the results were copied all to column A of a second sheet and run the table from that second sheet.

For countif you don't need to put the data into one column, except when you need to know which codes are in the array, but maybe that needs to be done only once.

Correct, the countif function in cell au1 is counting through all 5 columns. In the example above for instance it was searching for MLPX, and found it in ar2, ar3, and at6. That part works as intended for counting whatever is entered into cell at1. Thanks for the response!