May 23 2022 05:04 AM
Hello everyone,
What does the formula calculate? Cell "$B$3 contains the substring "DE.9" which is searched for within the column of the table "tab_nessie_data[Project]". The opened search result is divided by the found results. Why do I get "#WERT!" as the result? .
Note: Using the formula by searching for numbers works great.
Thanks for your support
May 23 2022 05:28 AM
Since you divide text on number, like ="text"/1
May 30 2022 01:24 AM
@Sergei Baklan Thank's for the answer to my question.
Is there a solution to my challenge from your point of view. Importantly, the solution works in conjunction with the Filter()-/Array function. The data area is extremely extensive, so that only a solution with a very low run-time is practicable.
I'm looking forward to your proposal.
Many thanks
May 30 2022 01:42 AM
Perhaps you can start by explaining what the challenge is. As @Sergei Baklan explained, all your formula does is divide an array of texts by an array of numbers 1. Hence, it generates only VALUE errors. Obviously, that's not your goal. But what do you want to achieve?
May 30 2022 02:27 AM
@Riny_van_Eekelen Alphanumeric characters are stored in a column with over one hundred thousand cells. When entering characters in a form field, the content in the dropdown menu should be restricted with each additional character (without double entries).
When using numbers, my solution works very quickly and precisely.
=SORTIEREN(EINDEUTIG(WENN(ODER(cap_id=" ";cap_id="");"";FILTER(tab_data[ID];
WENNNV(tab_data[ID]=WENNFEHLER(tab_data[ID]/(SUCHEN(cap_id;tab_data[ID])>=1);"");"")));FALSCH;FALSCH);1)
May 30 2022 02:44 AM
@Heiko_Tiedemann Sorry. Difficult to follow based on a picture and a German formula. But I guess that when you work with numbers, dividing that number by 1 results in that number. but perhaps the attached file is something you had in mind.
May 30 2022 04:32 AM
@Riny_van_Eekelen
Hi Riny,
This is a simple and great solution. (Please do not get this wrong)
I made some changes so that the function works on the filters () regardless of the length and position.
=FILTER(Table1[Project];IFERROR(SEARCH($B$4;Table1[Project];1)>=1;"FALSE"))
Another question: Is there a solution to suppress "#CALC" ("CALC") as an erroneous result, such as "IFERROR".
THX
May 30 2022 04:47 AM - edited May 30 2022 04:51 AM
SolutionUse the third optional argument in FILTER (i.e. if_empty).
=IFERROR(FILTER(Table1[Project],SEARCH($B$4,Table1[Project],1)>=1),"")
No need for the IFERROR on the SEARCH part either.
May 30 2022 04:56 AM - edited May 30 2022 04:57 AM
@Heiko_Tiedemann Ooops! Sorry, but I missed the purpose of the IFERROR in your original formula. I believe you need to have it as you had it.
So it would be like this:
=IFERROR(FILTER(Table1[Project],IFERROR(SEARCH($B$4,Table1[Project])>=1,0)),"")
May 30 2022 04:56 AM
May 30 2022 04:47 AM - edited May 30 2022 04:51 AM
SolutionUse the third optional argument in FILTER (i.e. if_empty).
=IFERROR(FILTER(Table1[Project],SEARCH($B$4,Table1[Project],1)>=1),"")
No need for the IFERROR on the SEARCH part either.