[Solved] Determine numbers common to two cells

[traggik]

Hi there, I am a newbie here. I am trying to get a function command for the following. This is just a short example. Lets say you have 1 set of numbers (6,12,33) in one cell, and (2,12,54) in another cell. How do i find the common numbers in the 2 cells, I know it is 12. But what function formula do i use. This is just a short example, but for the real thing would be numerous numbers with commas spacing them. Thanks, any information will be greatly appreciated.

Edit: Changed subject, was Help with function formula?  Make your post understandable by others  -- MrProgrammer, forum moderator

[traggik]

which command do i use to determine numbers common to two cells. For example, 1 cel has a set of numbers (1,3,4,5,6), and the other cell has a set of numbers (1, 2, 10,11,12) The repeating or common number is (1). Which command do i use to determine this?

[crusader]

Can each cell be populated by one number only, creating multiple columns as shown in the attachment? If that is possible/acceptable, conditional formatting can provide excellent visual answers.

[traggik]

Ok, but which formula do I use to find the repeating number? So that i can show that in a different cell?

[FJCC]

The attached file shows how to get what you want using some helper columns in crusader's file. I can't think of a simple way to do this in a single cell.

[crusader]

Ok, but which formula do I use to find the repeating number? So that i can show that in a different cell?

In the example I attached, I used "conditional formatting," as opposed to a formula.
Steps: highlight the relevant cells, then on the menu, click on Format -> Conditional -> Condition -> More Rules -> enter your choices.
The result will highlight numbers common in both columns (or all columns, depending on your range) - as shown in the attachment.

Edit: my apologies, I just realized you are using Openoffice 4.1.15; the steps I outlined above are for LibreOffice 24.8. The essence is the same in all spreadsheets; individual steps may differ (think of this as an opportunity to upgrade to LibreOffice).

[crusader]

The attached file shows how to get what you want using some helper columns in crusader's file. I can't think of a simple way to do this in a single cell.

FJCC's spreadsheet should do it - and it has formulas that will help you get what you want!

[LastUnicorn]

Making the switch from OpenOffice to LibreOffice is good advice. There are several good reasons for making the switch, some of which are mentioned here: [Tutorial] Considering a Switch from OpenOffice to LibreOffice? Some Useful Information

[traggik]

ok great thank you so much. this really helps out alotl. thank you again for your time and effort, greatly appreciate it.

[Hagar Delest]

Thanks to report if you still need help or if you're good. In latter case, please add [Solved] at the beginning of the title in your first post (top of the topic) with the 🖉 button if your issue has been fixed.

[Lupp]

In some very special cases something you want to achieve without the need of interactive usage of the UI, simply has no solution or only extremely complicated solutions based on standard fuctions.
In such cases the creation of an UDF may be next to indispensable.

For the given case the attached example file contains such a solution:

aooForum112375_Numbers_Common_to_Multiple_Columns.ods

(45.2 KiB) Downloaded 73 times

Also: Don't use the comma as the list separator where the elements are (can be) numeric strings.
For good reasons AOO Calc uses the semicolon as the argument separator in function calls.
The majority of locales uses the comma as the decimal separator.