[Solved] Combining IsNumber/Search & Index//Match queries

Discuss the spreadsheet application
Post Reply
User avatar
AWoodShed
Posts: 53
Joined: Mon Aug 19, 2019 1:49 am
Location: Australia

[Solved] Combining IsNumber/Search & Index//Match queries

Post by AWoodShed »

Example.ods
(13.5 KiB) Downloaded 148 times
I'm trying to combine an IsNumber/Search query, with an Index/Match query, but just can't get the correct formula combination.

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
Example.png
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
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Combining IsNumber/Search & Index//Match queries

Post by karolus »

Hallo

Enable

Code: Select all

→→ Tools → Options → LO-Calc → Calculate →→→ [x]Enable regular Expressions in Formulas
and use in B2:

Code: Select all

=A2 & IFNA(" "& VLOOKUP( MID(A2; SEARCH("\d+$";A2) ;10)*1 ; $F$2:$G$28 ; 2 ; 0) ; "" ) 
and pulldown
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
User avatar
AWoodShed
Posts: 53
Joined: Mon Aug 19, 2019 1:49 am
Location: Australia

Re: Combining IsNumber/Search & Index//Match queries

Post by AWoodShed »

karolus wrote:Hallo

Enable

Code: Select all

→→ Tools → Options → LO-Calc → Calculate →→→ [x]Enable regular Expressions in Formulas
and use in B2:

Code: Select all

=A2 & IFNA(" "& VLOOKUP( MID(A2; SEARCH("\d+$";A2) ;10)*1 ; $F$2:$G$28 ; 2 ; 0) ; "" ) 
and pulldown

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
User avatar
AWoodShed
Posts: 53
Joined: Mon Aug 19, 2019 1:49 am
Location: Australia

Re: Combining IsNumber/Search & Index//Match queries

Post by AWoodShed »

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 "
Suite: LibreOffice 7.5.12 ||| OS: Windows 10.0 Build 19045 ||| Calc: threaded ||| CPU threads: 8
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Combining IsNumber/Search & Index//Match queries

Post by karolus »

Hallo

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
see and try with attached Document
Example.ods
(18.58 KiB) Downloaded 133 times
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Post Reply