• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using Index to search for text

Dean Thatcher

New Member
Iam trying to use =INDEX(Appt_date,$M9) in this file. this is the list of the searches. the "Appt date" do not have any effect on any searches.
Customer#, Cust name, >= Appt date1, <= Appt date2, Appt time, Venue, Coordinator, Assistant
here is a example of a search

SEARCH Search BoxesSearch results
Customer# RowCustomer#Cust nameAppt dateAppt timeVenueCoordinatorAssistant
Cust name 11Taylor6/1/20098:00-RobinsonLee
>= Appt date15/5/200544Jackson6/1/200911:00-RobinsonLee
<= Appt date26/6/20061010Martinez6/2/200913:00-RobinsonLee
Appt time #NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!
Venue #NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!
Coordinator #NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!
Assistantlee#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!
changing the Appt date does not change to results.
I would like to add 3 more searches items to the list and show results.
want to add Col8, col9, and col10.
 

Attachments

Peter Bartholomew

Well-Known Member
A couple of problems with the tests on dates.
Firstly the COUNTIF reduces the 10 individual tests to a single number rather than retaining an array.
Secondly, a zero is still a number so ISNUMBER will return TRUE even when the original tests on dates return FALSE. I have introduced 1/N() to generate a #DIV/0 error which is not a number.
 

Attachments

Peter Bartholomew

Well-Known Member
This is so much nicer in Office 365! The formula is no more than
= FILTER( Appointments, Criterion )
where the defined name 'Criterion' refers to
Code:
=ISNUMBER(
    SEARCH(Search_customer,Customer)*
    SEARCH(Search_cust_name,Cust_name)*
    1/N(Appt_date>=Search_Appt_date1)*
    1/N(Appt_date<=Search_Appt_date2)*
    SEARCH(Search_Appt_time,Appt_time)*
    SEARCH(Search_venue,Venue)*
    SEARCH(Search_Coordinator,Coordinator)*
    SEARCH(Search_Assistant,Assistant)
  )
66918
The attached file uses dynamic arrays and has a second version with the FILTER function.
It also uses a defined name to hold the 'criterion' so that the worksheet formulas are simplified.
I think older versions of Excel will show the results as CSE arrays but I have no way of checking.
 

Attachments

Last edited:

Dean Thatcher

New Member
A couple of problems with the tests on dates.
Firstly the COUNTIF reduces the 10 individual tests to a single number rather than retaining an array.
Secondly, a zero is still a number so ISNUMBER will return TRUE even when the original tests on dates return FALSE. I have introduced 1/N() to generate a #DIV/0 error which is not a number.
Peter THANK YOU very much for solving this. The first one where you introduced 1/N() works the best for me to expand and add more data to.
 

Dean Thatcher

New Member
A couple of problems with the tests on dates.
Firstly the COUNTIF reduces the 10 individual tests to a single number rather than retaining an array.
Secondly, a zero is still a number so ISNUMBER will return TRUE even when the original tests on dates return FALSE. I have introduced 1/N() to generate a #DIV/0 error which is not a number.
Again thanks for solving this problem.
I have anouther change I would like to make to the file you introduced the 1/N() to.
I would like to add 3 more searches items to the list and show results.
I want to add Col8, Col9, and Col10. The data would numbers and words. Again thanks for you help.
 

Peter Bartholomew

Well-Known Member
Why don't you extend the appointments table and insert the criteria that apply to the additional columns. You will most likely need to extend the array constant in the INDEX formula to return data from the additional columns. You may need help extending the named formula 'Criterion', not because the formula is beyond you; it is likely to be the unfamiliarity of working with named formulas that unsettles you.
 

Dean Thatcher

New Member
Why don't you extend the appointments table and insert the criteria that apply to the additional columns. You will most likely need to extend the array constant in the INDEX formula to return data from the additional columns. You may need help extending the named formula 'Criterion', not because the formula is beyond you; it is likely to be the unfamiliarity of working with named formulas that unsettles you.
You are right I am some what familiar with the formula but I don't know how to add the 3 columns and searches to the data and then add the 3 search boxes and the 3 results.
 

Peter Bartholomew

Well-Known Member
I have added columns and modified the criterion formula to process them but, clearly, I have no way of guessing what they should contain or how they should be named. If you use Formulas / Name Manager you can edit the Names to make them relevant to your situation.
 

Attachments

Dean Thatcher

New Member
Iam trying to use =INDEX(Appt_date,$M9) in this file. this is the list of the searches. the "Appt date" do not have any effect on any searches.
Customer#, Cust name, >= Appt date1, <= Appt date2, Appt time, Venue, Coordinator, Assistant
here is a example of a search

SEARCHSearch BoxesSearch results
Customer#RowCustomer#Cust nameAppt dateAppt timeVenueCoordinatorAssistant
Cust name11Taylor6/1/20098:00-RobinsonLee
>= Appt date15/5/200544Jackson6/1/200911:00-RobinsonLee
<= Appt date26/6/20061010Martinez6/2/200913:00-RobinsonLee
Appt time#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!
Venue#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!
Coordinator#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!
Assistantlee#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!
changing the Appt date does not change to results.
I would like to add 3 more searches items to the list and show results.
want to add Col8, col9, and col10.
I have added columns and modified the criterion formula to process them but, clearly, I have no way of guessing what they should contain or how they should be named. If you use Formulas / Name Manager you can edit the Names to make them relevant to your situation.
Could you add the same 3 columns to the file that uses these formulas. you added the 1/N() to it and it woked better for me. =IFERROR(SMALL(IF(ISNUMBER(SEARCH(Search_customer,Customer)*SEARCH(Search_cust_name,Cust_name)* 1/N(Appt_date>=Search_Appt_date1)*1/N(Appt_date<=Search_Appt_date2) *SEARCH(Search_Appt_time,Appt_time)*SEARCH(Search_venue,Venue)*SEARCH(Search_Coordinator,Coordinator)*SEARCH(Search_Assistant,Assistant)),ROW(Customer)-MIN(ROW(Customer))+1),ROWS(M7:$M$7)),"")
thanks
 

Dean Thatcher

New Member
I have added columns and modified the criterion formula to process them but, clearly, I have no way of guessing what they should contain or how they should be named. If you use Formulas / Name Manager you can edit the Names to make them relevant to your situation.
I forgot to ask to have the ValueField search the same way the MatchField and TextField do. Thanks Again
 

Dean Thatcher

New Member
I have added columns and modified the criterion formula to process them but, clearly, I have no way of guessing what they should contain or how they should be named. If you use Formulas / Name Manager you can edit the Names to make them relevant to your situation.
hI
Hopefully You can assist me.
In trying this out I found that the results when using Del key for a search will not show more that 10 lines. I tried to copy them down from 10 to 19
but did not work.
Also if I enter anything in F24 or F25 all the results clear out.
And the searches do not work above F23.
Thanks
 

Attachments

Dean Thatcher

New Member
Hi Again.
I ment to ask the have the search for the MatchField and ValueField fields changed to search like the TextField searches
thanks again
 

Peter Bartholomew

Well-Known Member
I have introduced an Excel Table to make the names dynamic. I have also turned the record numbers 'k' and the pointer 'ptr' into named formulas to make them respond dynamically. These days I only work with dynamic array versions of Excel (not many clients at present!) but I have had a go at using CSE to output the results. The region covered by CSE needs to be actively managed as the data is changed.
As it stands, the search will not return any record with blank fields. For that, you would need a specific test with ISBLANK.
 

Attachments

Dean Thatcher

New Member
I have introduced an Excel Table to make the names dynamic. I have also turned the record numbers 'k' and the pointer 'ptr' into named formulas to make them respond dynamically. These days I only work with dynamic array versions of Excel (not many clients at present!) but I have had a go at using CSE to output the results. The region covered by CSE needs to be actively managed as the data is changed.
As it stands, the search will not return any record with blank fields. For that, you would need a specific test with ISBLANK.
Thanks again for you help. Questions Below:
1588079201428.png1588079201428.png
Thanks for your help.
Why don't Rows 10, 13, 16, 18, 19 show in results when using Del?

Also When x is searched in TextField the results stop at row 8, rows 10, 13, 16, 18, 19 do not show.
they all have a x in them?

Please change MatchedField and VaulueField to have the same search as the TextField.
 

Dean Thatcher

New Member
p45cal
Thanks for that tip..
I can add more rows to the data C to L. up to 100 by going into design then Resize Table, but the results stop at 19.
A26 stays at 19
How can I add more rows to the results and change a26 to 20, a27 to 21 etc ?
Again thanks for your help.
 

Peter Bartholomew

Well-Known Member
@Dean Thatcher
Every solution I write is based upon defined names and dynamic arrays which adjust automatically, but that makes it tricky to revert to traditional Excel. Basically, the formulas in cells A8, Q8 and R8 should have read across as CSE array formulas. To extend these formulas to match the size of the Table, you select the entire range to which you wish to apply the formula, click in the formula bar, and then commit the formula with Ctrl+Shift+Enter.
If you prefer to have an oversize array to allow for growth you will get loads of #N/A at the foot of the range that can be hidden with conditional formatting by using a white font.
 

Dean Thatcher

New Member
this is what I tried.
I selected c8 to L50 went to top of sheet to where File Home Insert Page Layout Formulas Data Review and selcted formulas...
now what do i do??
 

Peter Bartholomew

Well-Known Member
The range C8:L22 is the body of a standard Excel table. If you go to the final cell and Tab an additional row should open up at the bottom of the table. Alternatively, you can simply type a new row of data and the Table will extend. The catch is that the array formulas for 'k', 'ptr' and the filtered rows will not extend automatically. It is those arrays that need to be extended when rows are added to the table.
Pascal Contribute if you are willing; I will not be offended!
 

Dean Thatcher

New Member
Peter I am finding the above Table to complicated to exspand and use.
I am for a Contribution but would want to use that on this File to finish as it is much easier to edit and expand.
basicly it uses
IFERROR(SMALL(IF(ISNUMBER(FIND(Search_customer,Customer)*
FIND(Search_cust_name,Cust_name)*
1/N(Appt_date>=Search_Appt_date1)*
1/N(Appt_date<=Search_Appt_date2)*
FIND(Search_Appt_time,Appt_time)*
FIND(Search_venue,Venue)*
FIND(Search_Coordinator,Coordinator)*
FIND(Search_Assistant,Assistant)),
ROW(Customer)-MIN(ROW(Customer))+1),
ROWS(Q$7:$Q11)),)

I need to have 3 Columns of data added named "Col 8, Col 9, Col 1o" and then show results with using the search set up that Assistant uses to show results for Col 8, Col 9 and Col 10
thanks

.
 

Attachments

Peter Bartholomew

Well-Known Member
I think our working styles simply do not match. I do not usually allow data to be input except in tables and never use direct references of the form Q$7. To develop such a sheet does required good knowledge of Tables and Structured Referencing but that is easy to acquired. The formula you quote is actually far more complicated.

What I see as being more of a problem is that my dynamic arrays will convert to CSE arrays when using older versions of Excel. Although I used CSE arrays extensively, I found them to be user-hostile and I remember complaining about them to Microsoft for years. Now as I have dynamic arrays, I am happy and never wish to revert to traditional techniques that I used to regard as ill-conceived.
 

p45cal

Well-Known Member
In the attached:
Cells O16:O18 have been added as search criteria
The search will search the table on the left down to row 200
The results table on the right has been extended to row 100
Some IFERROR statements have been added to hide #VALUE errors when something is not found.

I've done no checking.
 

Attachments

Top