Search engine for excel

[greg2091]

I am looking to refine my search page on excel, for reference I have a sheet with nearly 1000 rows and each row has nearly 100 cells of information, most of this information is true/false but some is written data. I have found a way to refine the search for relevant rows using the "Filter", "Isnumber", and "Search" functions, however I am looking for a way to refine the data further based on the True/False segments. The issue I am running into is that this will only return true OR false sections, but I am looking for a way to return the rows where a specific cell is labeled "True" when I am looking for that criteria, but when I am not looking for that criteria specifically it returns all rows where those cells are labelled both True AND False. Any help would be appreciated!!

 

[KRice]

Without more detail, it's difficult to offer specifics. I would recommend using an index of some type, perhaps formed with SEQUENCE or ROW functions. Here is an example showing how the row numbers can be obtained. If desired, those row numbers can be converted to index numbers...and if a filtered list is then desired, either of those arrays if fed into the FILTER function.

MrExcel_20240108.xlsx
	A	B	C	D	E	F	G
1				Where All TRUE
2	Data1	Data2	Values	Values	Rows	Indexes	Filtered Indexes
3	TRUE	FALSE	99	55	0	0	5
4	FALSE	FALSE	88	33	0	0	7
5	TRUE	FALSE	77		0	0
6	FALSE	FALSE	66		0	0
7	TRUE	TRUE	55		7	5
8	FALSE	TRUE	44		0	0
9	TRUE	TRUE	33		9	7
10	FALSE	TRUE	22		0	0
Sheet3

Cell Formulas
Range	Formula
D3:D4	D3	=FILTER(C3:C10,A3:A10*B3:B10=1)
E3:E10	E3	=A3:A10*B3:B10*ROW(C3:C10)
F3:F10	F3	=A3:A10*B3:B10*(ROW(C3:C10)-ROW(E3)+1)
G3:G4	G3	=FILTER(F3#,F3#<>0)
Dynamic array formulas.

 

[greg2091]

Okay to clear things up I am including a quick example of the table and the current mishmash formula I am using, I am not looking for something elegant, just something that works. So I have a table similar to that about (just much much larger)). I figured out how to search for the rows I need using partial text using the following formula "=FILTER(BIW!F4:R1000,(ISNUMBER(SEARCH(Search!C2,BIW!K4:K1000)))*(ISNUMBER(SEARCH(Search!C3,BIW!F4:F1000)))*(ISNUMBER(SEARCH(Search!C4,BIW!P4:P1000)))*(ISNUMBER(SEARCH(Search!C5,BIW!N4:N1000)))*(ISNUMBER(SEARCH(Search!C4,BIW!P4:P1000)))*(ISNUMBER(SEARCH(Search!C6,BIW!J4:J1000))),"none")" (I plan on changing it to refer to the table as a whole as soon as I figure out the next part so it is expandable)
The current set up is that I can search any of the 8 text base headings using 8 separate, corresponding cells on a different sheet and it will return the rows that match all searched criteria. Now I want to further refine the search so that I can select check boxes or choose a heading from a drop down menu (or if there is a third choice that I don't personally know of whatever that may be), when the check box is checked, or the heading is selected it will return only the rows that have an "x" in that column, but if it is unchecked, or nothing is selected it will return all rows, regardless of whether there is an "x" or it is blank.

Hopefully this clears somethings up, if you need any more details I will be happy to provide them to the best of my knowledge.

Thank you,
Greg2091

 

[KRice]

I will encourage you to look into the XL2BB add-in (see link in my signature block). The add-in allows you to post a small working example, which shows row and column headings and any formulas in the displayed area.
In your latest post, you have two of these expressions:

(ISNUMBER(SEARCH(Search!C4,BIW!P4:P1000)))

...only one instance is necessary, and I suspect this is a typo, and perhaps is really meant to reference other cells. But there are several points here: 1) if this is a typo, it would not have occurred with the XL2BB add-in; 2) the reader cannot tell what cells this expression refers to, as there are no row/column references on your image; and 3) providing an image places a greater burden on someone offering to help you, as they need to mock up a working example of a sheet to illustrate and test any formulas. To help others help you, offering a small working example pasted as a mini-sheet from the XL2BB add-in is highly preferred.

...it will return only the rows that have an "x" in that column

Can you elaborate on this point further, please? If you were to indicate that the results should consider the contents from two additional columns (by specifying their column headings), does this mean that a reportable row must have:

1. an "x" in either of those two columns (so the other column might be blank)
2. an "x" in both of those columns

 

[offthelip]

One way which you could do this which would be very fast, would be put some code in the workbook open event that loaded a public dictionary with the key which is the concatenation of your headings 1 to 8, the value is the row number. Then on your second sheet you can instantly get the row number by just looking up the same 8 elements concatnentated together to find the row number. You could write a function to do this

 

[KRice]

Have a look at this example to see if it delivers expected results. I'm assuming the answer to my question above is #1...for a row to be reported back, an "x" may appear in any of the specified columns, and if no "x columns" are specified, then there is no evaluation involving those Heading1, Heading2, etc. columns.

MrExcel_20240108.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1							Search terms			Columns to Evaluate for "x"
2	Data1	Data2	Heading1	Heading2	Heading3		dog	Data1 column		x	Heading1
3	cat	blue			x		blue	Data2 column		x	Heading2
4	dogs	blue	x								Heading3
5	cat	green	x
6	dog	blue			x		Results
7	cat	red			x		Data1	Data2	Heading1	Heading2	Heading3
8	dog food	blue	x	x			dogs	blue	x	0	0
9	cat	blue	x				dog food	blue	x	x	0
10	hotdog	blue		x	x		hotdog	blue	0	x	x
Sheet3

Cell Formulas
Range	Formula
G7:K10	G7	=LET(headings,A2:E2,primary,FILTER(A3:E10,(ISNUMBER(SEARCH(G2,A3:A10)))*(ISNUMBER(SEARCH(G3,B3:B10)))*IF(COUNTIF(J2:J4,"x")=0,1,(MMULT(--(C3:E10="x"),--(J2:J4="x"))>0)) ), VSTACK(headings,primary) )
Dynamic array formulas.

 

[greg2091]

Okay, let me try again, I cannot seem to figure out the XL2BB, but if this post doesn't work, I will figure it out and post a small working example, however, that will take some time as the actual tables contain confidential information, so I will have to create a working new example. So, currently, this is the basic setup of my data table. It spans from column B to column HB, which is rather large. On another sheet, I have the "Search Engine" (example below)

On this sheet I have the previously stated formula "=FILTER(BIW!F4:R1000,(ISNUMBER(SEARCH(Search!C2,BIW!K4:K1000)))*(ISNUMBER(SEARCH(Search!C3,BIW!F4:F1000)))*(ISNUMBER(SEARCH(Search!C4,BIW!P4:P1000)))*(ISNUMBER(SEARCH(Search!C5,BIW!N4:N1000)))*(ISNUMBER(SEARCH(Search!C6,BIW!J4:J1000))),"none")"
In cell E3, I do not need help with this part unless the next part would somehow affect this, which I do know it very well could. This function lets me search for the different necessary headings that have text in them to refine my search. (Keep in mind the code pasted does not refer to the examples here; it is the actual code in the real document) but the search fields(Cells C2 - C6 all refer to fields with text in the table that is being searched). The function returns the relevant rows depending on the searched terms.


Now for my problem, I would like to further refine the search based on the commodities (In the example headings 9 - 15). These rows either have to deal with these commodities or do not. If this commodity is within the scope of the project, these rows summarize it is marked with an 'x'; otherwise, it is left blank. Is there a way to:

1) Link "Commodity 1" to the column labelled "Heading 9" (column J) and "Commodity 2" to "Heading 10" (column K).
2) If they are checked, they will refine the search. (assuming image 1 is the jobs that the text search finds) e.g. if "commodity 1" is checked, it returns rows 4 and 8; if "commodity 2" is checked, it returns rows 6 and 8; if they are both checked, it will return down 4, 6, and 8 and if neither is checked it will return all of the rows.

Lastly, would this be better done with VBA code vs functions?

 

[KRice]

Have a look at my last post. It includes a couple of components that use the search construction with which you are familiar. Those components are simply expanded to include all of your baseline search criteria…and all of those resultant arrays are multiplied together to give a single array indicating the relative row positions associated with all criteria being true. Then note that there is an additional formula component, one that evaluates which of the rightmost columns should be further evaluated to determine if they contain an “x”. The MMULT function operates on the rightmost columns and determines which rows contain at least one “x” in the specified columns. MMULT is used inside IF statement to produce the logic that if no columns are specified, then a 1 is returned…and all of the rows from the original search are used. But if any columns are specified, then MMULT returns an array of TRUE/FALSE values (indicating that an “x” was found on that row under one of the specified columns). And the 1 or the MMULT T/F array are multiplied by the original search filtering array to further trim down the results.

If this is generally what you want, then adapt and expand the formula to your workbook…and then let’s talk about the “link” that you mentioned in question 1 of your last post (I don’t understand what that means). About your last question, VBA is always an option, but I don’t consider this so onerous that it would make sense to prefer it over a formula.

 

[KRice]

I'm having difficulty following the formula you posted and the table image...they don't match up with each other. Assume you have a data table on the BIW sheet that resembles this:

MrExcel_20240108.xlsx
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	1	2	3	4	5	6	7	8	9	10	11	12	13	14
2	C3				C6	C2			C5		C4
3	Heading1	Heading2	Heading3	Heading4	Heading5	Heading6	Heading7	Heading8	Heading9	Heading10	Heading11	Heading12	Heading13	Heading14
4	blue				cat	1			3		2			x
5	blue				dogs	1			3		2	x
6	green				cat	1			3		2
7	blue				dog	1			3		2		x	x
8	red				cat	1			3		2			x
9	blue				dog food	1			3		2	x	x	x
10	blue				cat	1			3		2
11	blue				hotdog	1			3		2			x
BIW

I've included some helper information in the first two rows...none of that is needed by the formula, but it was used to map the formula you posted to the correct columns and to provide guidance on setting up the formula.
On the summary sheet, the search criteria are in columns B:C, including the special Commodity section shown in green. Information in column D is not necessary (also used to help debug the formula).

The formula does require matching criteria in all 5 of the main search fields (C2:C6), and blanks in the data array may be a problem (blanks in the data array will not match with blanks in the search criteria)...do you have blanks in the data table? The formula is somewhat more elaborate so that all of the formula adaptation to your worksheet is done up front, defining the range for the headings, the main data array, the number of left-side columns (to the left of the special "x" columns), and the commodity array. And then more convenient variable names are assigned to certain columns in the data array...confirm that the correct column index is declared within the CHOOSECOLS functions, and that the five search criteria in C2:C6 are correctly assigned to their variable names.

MrExcel_20240108.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		Search criteria	compares to:
2	Brand	blue	BIW!F		Results
3	Region		BIW!K		Heading1	Heading2	Heading3	Heading4	Heading5	Heading6	Heading7	Heading8	Heading9	Heading10	Heading11	Heading12	Heading13	Heading14
4	Model Year		BIW!P		blue				dogs	1			3		2	x
5	Program Code		BIW!N		blue				dog	1			3		2		x	x
6	Division	dog	BIW!J		blue				dog food	1			3		2	x	x	x
7	Commodity 1	x	Heading12
8	Commodity 2	x	Heading13
9	Commodity 3		Heading14
Summary

Cell Formulas
Range	Formula
F3:S6	F3	=LET(headings,BIW!F3:S3,ary,BIW!F4:S11,nleftcols,11,commodity_ary,C7:C9, brand,CHOOSECOLS(ary,1),region,CHOOSECOLS(ary,6),modyr,CHOOSECOLS(ary,11),pgmcode,CHOOSECOLS(ary,9),div,CHOOSECOLS(ary,5), find_brand,C2, find_region,C3, find_modyr,C4, find_pgmcode,C5, find_div,C6, rtdata_ary,DROP(ary,,nleftcols),primary,FILTER(ary,(ISNUMBER(SEARCH(find_brand,brand)))*(ISNUMBER(SEARCH(find_region,region)))*(ISNUMBER(SEARCH(find_modyr,modyr)))*(ISNUMBER(SEARCH(find_pgmcode,pgmcode)))*(ISNUMBER(SEARCH(find_div,div)))*IF(COUNTIF(commodity_ary,"x")=0,1,(MMULT(--(rtdata_ary="x"),--(commodity_ary="x"))>0))), ans,VSTACK(headings,primary),IF(ans=0,"",ans))
Dynamic array formulas.

 

[greg2091]

Thank you! It took a while but this did solve my issue!