Page 1 of 1
[Solved] Match data and fetch common in third cell
Posted: Wed Nov 06, 2024 8:31 am
by yuv.370
I have two row A and B both have some common number, if i type 80900 in D2 it should give me all number from b that matches from A
Re: Math data and fetch common in third cell
Posted: Wed Nov 06, 2024 8:53 am
by Hagar Delest
Hi and welcome to the forum!
Please read the
Survival Guide for the forum.
I have changed all the caps in your message. Caps are for shouting and it is a quiet place here.
If you have vision difficulties, increase the font size of the whole page because nobody will reply in all caps anyway.
Re: Math data and fetch common in third cell
Posted: Wed Nov 06, 2024 2:29 pm
by Lupp
The version you give in your signature is a very old AOO, but the example file was created with LibO 7.4.4.2. Strange!
Anyway, you surely mean the
columns A and B. Both contain numbers, and the presentation of numbers is affected by formatting. Numbers used for IDs and the like should ne entered as text.
As I understand the question, you expect all the result from B where A matches what you put into D to be returned as a result in a single cell. This can only be done converting the numbers to strings and using the function TEXTJOIN() in array-mode. (Otherwise you would need to resort to user code.)
TEXTJOIN() is only available in LibO V 4.2 or higher.
If you actually run such a version you may study the attached example.
Re: Math data and fetch common in third cell
Posted: Wed Nov 06, 2024 4:21 pm
by Alex1
I tried 80400 in an empty row and got "<none".
Use this in E2 instead, entered as an array formula (i.e. using Ctrl+Shift+Enter) and use the fill handle:
=TEXTJOIN(";";1;IF(A$2:A$11=D2;B$2:B$11;"")) & IF(CURRENT()="";"<none>";"")
Re: Math data and fetch common in third cell
Posted: Wed Nov 06, 2024 5:37 pm
by Lupp
Thanks for the correction @Alex1!
Re: Match data and fetch common in third cell
Posted: Thu Nov 07, 2024 7:24 pm
by yuv.370
Hi still getting error in formula
Re: Match data and fetch common in third cell
Posted: Thu Nov 07, 2024 9:44 pm
by Lupp
1. Attach your example sheet.
2. Next to nobody else here will have an OO.o V3.1 for checks.
Re: Match data and fetch common in third cell
Posted: Thu Nov 07, 2024 9:54 pm
by yuv.370
My OO version is 4.1.11 example attached
Re: Match data and fetch common in third cell
Posted: Thu Nov 07, 2024 10:41 pm
by Lupp
If you consider to shift to LibreOffice, and to use a fresh version (24.8 or higher) the filtering solution in the attached sheet will work.
To do such filtering with formulas of the "old tradition" is too complicated, and requires helper ranges.
The solution using TEXTJOIN() will only work in LibO V 4.2 or higher.
Old OO.o has no means supporting the task without resorting to user code (macros).
Re: Match data and fetch common in third cell
Posted: Thu Nov 07, 2024 11:42 pm
by Alex1
yuv.370 wrote: ↑Thu Nov 07, 2024 9:54 pm
My OO version is 4.1.11 example attached
This version contains no formulas at all, so how do we know what the error is?
Re: Match data and fetch common in third cell
Posted: Fri Nov 08, 2024 1:45 pm
by Lupp