[Solved] Combining IsNumber/Search & Index//Match queries
[Solved] Combining IsNumber/Search & Index//Match queries
Example: I have text in Column A
I want to check if a particular text string exists in Column A, and if so, return the corresponding value from an Index/Match matrix in Columns F & G
Column B then concatenates the original text from Column A, plus a space, plus the result of the Index/Match matrix.
I can work out the Index/Match formula, e.g. Index($G$2:$G$28,Match( a single value I'm searching for, for example the number 10, $F$2:$F$28,0))
I can work out an IsNumber/Search formula, e.g. IF(IsNumber(Search( a single value I'm searching for, for example the number 10 , A2)), "YES" , "NO"))
I can work out a Concatenate formula, e.g. Concatenate (A2, " ", plus the results of the combined search I'm needing to perform)
The tricky thing, is determining the actual text string to search for in Column A, within the IsNumber formula.... which is then called into the Index/Match search criteria.
My desired outcome is to search, in turn, for every possible option within the Index/Match matrix in Column F.
i.e. Search A2 for the text "10" - if it's found, B2 concatenates A2, a space, the result of the Index/Match function searching for "10", plus another space.
I need this to recursively check through the whole matrix list for all possible values.
i.e. after searching A2 for the text "10" & returning the result, also search for the text in the next row of the matrix "11" and also return that value, if applicable, continue, continue until row 28 which is end of matrix.
Is this possible?
Example spreadsheet attached
Last edited by AWoodShed on Mon Nov 29, 2021 3:35 am, edited 1 time in total.
Suite: LibreOffice 7.5.12 ||| OS: Windows 10.0 Build 19045 ||| Calc: threaded ||| CPU threads: 8
Re: Combining IsNumber/Search & Index//Match queries
Hallo
Enable
and use in B2:
and pulldown
Enable
Code: Select all
→→ Tools → Options → LO-Calc → Calculate →→→ [x]Enable regular Expressions in Formulas
Code: Select all
=A2 & IFNA(" "& VLOOKUP( MID(A2; SEARCH("\d+$";A2) ;10)*1 ; $F$2:$G$28 ; 2 ; 0) ; "" )
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Re: Combining IsNumber/Search & Index//Match queries
karolus wrote:Hallo
Enableand use in B2:Code: Select all
→→ Tools → Options → LO-Calc → Calculate →→→ [x]Enable regular Expressions in Formulas
and pulldownCode: Select all
=A2 & IFNA(" "& VLOOKUP( MID(A2; SEARCH("\d+$";A2) ;10)*1 ; $F$2:$G$28 ; 2 ; 0) ; "" )
Thank you !
That worked in most instances, but for row 5, for example it only worked once, and returned the VLOOKUP value for the first value it found, i.e. "10"
i.e. the desired results is to find both the number 10 AND the number 163, in the original Text ColumnA, and return the VLOOKUP results for both of these, i.e. return ABC 40 x 10 x 163 11 12 13 160 (results of finding "10" AND "163")
Is it possible to modify this formula, to look for ALL possible values in ColumnF, within ColumnA ?
Suite: LibreOffice 7.5.12 ||| OS: Windows 10.0 Build 19045 ||| Calc: threaded ||| CPU threads: 8
Re: Combining IsNumber/Search & Index//Match queries
UPDATE: Before @karolus's response, I re-configured my spreadsheet with helper-columns to individually search for the terms in ColumnF, and return ColumnG, if ColumnF was found in ColumnA
Then I just Concatenated all of these helper columns together, with a space separating them, and pulled down the entire list.
This has worked 90% as I desired... however the search/isnumber function is not finding "exact matches"
i.e. a Column A cell containing ABC 240x31 is resulting in finding "31" (and returning "30" = correct) but it is also finding "40" within the number 240, and incorrectly returning "41 42"
I know there is a method to set a regular-expression formula to result in exact-matches, but there are so many results and threads about this in here, I'm struggling to find a solution from all the responses people have previously made.
I know that I will probably have to space out the "x" in the original terms, so that a regular-expression formula matches correctly, since I believe they are dependent on the search item having nothing in front or behind the term being searched.
i.e. ABC 240 x 31 =====> the desired result is: " ABC 240 x 31 30 " NOT " ABC 240 x 31 30 41 42 "
Then I just Concatenated all of these helper columns together, with a space separating them, and pulled down the entire list.
This has worked 90% as I desired... however the search/isnumber function is not finding "exact matches"
i.e. a Column A cell containing ABC 240x31 is resulting in finding "31" (and returning "30" = correct) but it is also finding "40" within the number 240, and incorrectly returning "41 42"
I know there is a method to set a regular-expression formula to result in exact-matches, but there are so many results and threads about this in here, I'm struggling to find a solution from all the responses people have previously made.
I know that I will probably have to space out the "x" in the original terms, so that a regular-expression formula matches correctly, since I believe they are dependent on the search item having nothing in front or behind the term being searched.
i.e. ABC 240 x 31 =====> the desired result is: " ABC 240 x 31 30 " NOT " ABC 240 x 31 30 41 42 "
Suite: LibreOffice 7.5.12 ||| OS: Windows 10.0 Build 19045 ||| Calc: threaded ||| CPU threads: 8
Re: Combining IsNumber/Search & Index//Match queries
Hallo
It seems we need some Scripting-foo:
see and try with attached Document
It seems we need some Scripting-foo:
Code: Select all
import re
numrex = re.compile(r'(\d+)')
def join_number_lookups_to_selection(*_):
doc = XSCRIPTCONTEXT.getDocument()
sheet = doc.Sheets.LOOKUP
referenz = sheet["F2:G28"].DataArray
referenz = dict((f"{key:.0f}", f"{value:.0f}"
if isinstance(value, float) else value)
for key, value in referenz)
data = doc.CurrentSelection.DataArray
out = []
for original, _ in data:
numbers = numrex.findall(original)
lookups = " ".join([referenz.get(number,"") for number in numbers])
out.append((original, f"{original} {lookups}".strip()))
doc.CurrentSelection.DataArray = out
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)