Help in figuring out how to copy rows out of a sheet based on value in a column.

[Karsaxx]

Hi there,

Hope all is well.

I am having trouble figuring this out and would love some assistance as my knowledge is limited.

I have 3 sheets in different docs,

Source doc : Master Sheet
Processing doc: (Results
Search Terms doc: Search list

What i am trying to do is using a value found in the Search list sheet and looking for it in the F column in the Source Doc and if found, bring the whole row into results. The value may repeat throughout the source doc but always in the same column.

Is this something that i can do in VBA so i can just change the value and redo the search?

Help is greatly appreciated.
Karsa

 

[KRice]

What i am trying to do is using a value found in the Search list sheet and looking for it in the F column in the Source Doc

What does this mean? Are the "values" in the Search list numbers or text? And by "looking for it" are you looking for an exact match of the entire "value" or merely looking to determine if the value is found somewhere within the entries of column F of the Source worksheet/document? For example, if my search list includes "g" and column F consists of three rows containing {"g"; "dog"; "cat"}, should the results return only the 1st row...or both the 1st and 2nd rows?

 

[KRice]

Although I'm not sure what the answers are to the questions I asked above, I've assumed the "values" are text and that "looking for" matches means that if any of the search strings are found anywhere in any of the entries in the column F Master table, then you want the row. I've used a spilling formula to return the results. The Search terms shown in this sheet are there for reference only...they are not used directly and can be deleted from this sheet. If this is close to what is needed, I would like to know if the search terms on the Search sheet are in an official Excel table, or do they occupy a range? In the example here, they occupy a range...I haven't converted that range to a Table, although doing so would have some advantages, as the formula could be simplified.

MrExcel_Results.xlsx
	A	B	C	D	E	F	G	H	I	J
9	Results:
10	Col1	Col2	Col3	Col4	Col5	Col6	Col7	Col8		Search Terms
11	q	w	e	r	t	y	u	i		y
12	z	x	c	v	b	n	m	<		n
13	b	v	c	x	f	n	r	d
14
Results

Cell Formulas
Range	Formula
A11:H13	A11	=FILTER([MrExcel_Source.xlsx]Master!$A$2:$H$100,BYROW(--ISNUMBER(SEARCH(TRANSPOSE([MrExcel_Search.xlsx]Search!$A$2:INDEX([MrExcel_Search.xlsx]Search!$A:$A,COUNTA([MrExcel_Search.xlsx]Search!$A:$A))),[MrExcel_Source.xlsx]Master!$F2:$F100)),LAMBDA(row,SUM(row))))
J11:J12	J11	=[MrExcel_Search.xlsx]Search!$A$2:INDEX([MrExcel_Search.xlsx]Search!$A:$A,COUNTA([MrExcel_Search.xlsx]Search!$A:$A))
Dynamic array formulas.

The Master sheet found in the workbook MrExcel_Source.xlsx looks like this:

MrExcel_Source.xlsx
	A	B	C	D	E	F	G	H
1	Col1	Col2	Col3	Col4	Col5	Col6	Col7	Col8
2	q	w	e	r	t	y	u	i
3	a	s	d	f	g	h	j	k
4	z	x	c	v	b	n	m	<
5	p	l	m	n	k	h	i	j
6	b	v	c	x	f	n	r	d
7
Master

The Search sheet found in the workbook MrExce_Search.xlsx looks like this:

MrExcel_Search.xlsx
	A
1	Search List
2	y
3	n
4
Search