[Solved] Search for multiple values

[RussvA]

Hi all
I am stuck and hoping for guidance. I have very limited knowledge of coding, formulas and macro's.
I have a spreadsheet (attached) which contains all the Preset Rife treatment programs loaded into my hardware. On this sheet is a column named 'Treatment Frequencies', and each cell has the list of frequencies covered in that preset program.

What I am trying to do, is use the 'Find' function to show me the cells that have 1 or more of a list of frequencies that I punch in. e.g. I wish to find cells that have frequencies 1550, 2720, 776, 5000 in it. I will then select the appropriate 'Program Name' from the spreadsheet and cut and paste to my report.

Is there a way to do this?
Many thanks
Russ

Edit: Changed subject, was Complex finding  Make your post understandable by others  -- MrProgrammer, forum moderator

[FJCC]

I think I'm not really understanding what you need. You can search for cells that have one or more of the given frequencies by putting

Code: Select all

1550|2720|776|5000

in the Search For box of the Find & Replace dialog, clicking More Options, selecting Regular Expressions, and clicking Find All. That will select many cells. How do you choose among those cells?

[MrProgrammer]

I have very limited knowledge …

[Tutorial] Ten concepts that every Calc user should know

… use the 'Find' function …

FIND() is a Calc function. Find is also a Calc feature: Edit → Find & Replace. Both of them might help you with this task. Your post does not make it clear to me which one you mean. I know you said function, but people often confuse these terms, and I've found it's better to ask for clarification.

1 or more of … frequencies 1550, 2720, 776, 5000 in it …

All of them? At least one of them? Your post does not make your meaning clear.

… use the 'Find' function …

Explain the goal, not the step. There may be an easier path than the one you chose.
XY Problem

… each cell has the list of frequencies …

Storing more than one piece of information per cell always complicates using Calc. However you can use the Regular Expressions feature to search for substrings in a cell. For example,  .*\b776\b.*  will match cells which contain 776.

I will then select the appropriate 'Program Name' from the spreadsheet and cut and paste to my report.

You could use Data → Filter → Advanced Filter. This will display only the desired rows. Use Edit → Copy and not Edit → Cut! Then you can paste all the program names in one step. Read about Advanced Filter in Help → Index or in User Guides (PDF) or searching for topics about it in the Calc Forum. You could also use Standard Filter. Always use Data → Define Name to give your range a name before using a filter. Given the data layout in your attachment do not include column A or row 1 in the range.

Rows filtered by Advanced Filter feature

202508080904.gif (91.02 KiB) Viewed 2933 times

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.

[RussvA]

Thanks guys. Greatly appreciated.
Sorry for my poor description of what I needed.
I will try and clarify my need.
So I have the preset Rife programmes spreadsheet as above. Now all I actually want to do, is select a Calc function that I can put a selection of frequencies into, either with a , or a | between them, and then get Calc to display the respective lines from the above spreadsheet, that have most or all of these frequencies.
Not sure if this is possible.
hope this is clearer.
Thanks
Russ

[FJCC]

Here is one solution that might be acceptable if you don't have to search for many frequencies. I added columns to look for each frequency and return a 1 if its in column D. The Total column adds the counts and has a filter on it, so you can view all the rows that have the highest count. You can change the numbers in row three to adjust the search and you can insert more columns if you need to search for more than four values. You can leave the cells in row 3 blank if you need to search for fewer than four values.
For the formulas to work, go to Tools -> Options -> OpenOffice Calc -> Calculate and turn on Regular Expressions and turn off the open to match whole cells.

[Alex1]

For the formulas to work, go to Tools -> Options -> OpenOffice Calc -> Calculate and turn on Regular Expressions and turn off the open to match whole cells.

These settings are stored in the file.

[RussvA]

Thanks MrProgrammer.
So I tried to use Advanced filter step by step in Calc 'Help" but keep getting a error. It all falls apart when the instruction... "Enter the filter conditions linked with OR in rows 21, 22, and so on...." but there is no hint as to where to enter these conditions. "Range does not contain a valid query"

[Lupp]

This seems to be one of the cases where a shift to LibreOffice should be considered.
There are the function REGEX() since V 6.2 and the array function FILTER() since V 24.8 which are useful in the context.
Of course FILTER() has also the disadvantages of all the array functions locking an output range.
For visitors who want to test a solution needing a recent LibO, I attach a reworked example:

aoo113003exampleNeedingLibO24_8_OrHigher.ods

(82.19 KiB) Downloaded 62 times

The above "solution" has flaws. Since nobody was interested in it I don't upload the fixed version.
Now there was a download. I therefore attach the "fixed" suggestion.

aoo113003exampleNeedingLibO24_8_OrHigherBsmall.ods

(83.89 KiB) Downloaded 61 times

I had to cut off rows to reduce the file size.

As always I'm interested in criticism.
The original example contains rows where I don't understand the "information" under "Treatment Frequencies"
See row 46 e.g. What shall the the "filter formula" return for a lookup value of 10 if such a cell contains "1111 for 10 min" but no "10" in the position of an actual frequency?
The solution by @FJCC regarding the remark by @Alex1 would return a match.
Postulate: Compounds are not really qualified as data.

[MrProgrammer]

there is no hint as to where to enter these conditions

You enter the conditions as described on the help page where it says: Choose Data - Filter - Advanced Filter, and then select the range A20:E22. It also shows the entries in those cells. When you're having trouble with a feature, always start by replicating the example that was provided to see it in operation. Then you have a working example to do further experimentation with.

I've attached an example of Advanced Filter on your data. I put the attachment's filter criteria in B1:D2. I have them in a row instead of a column because I wanted to use AND instead of OR. You can do it either way, row or column, depending on what you need to find. Because you've stored multiple frequencies in one cell, you need to use the \b feature and enable More → Regular Expressions in Advanced Filter. If you don't use \b, a search for 776 would find 2776.

202508140856.ods

(62.6 KiB) Downloaded 68 times

Although you could use Advanced Filter, given your data layout I think FJCC's filter method will work better for you.

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.

[RussvA]

Thanks Alex1