Matching Symbols

[rwmill9716]

I have a list of symbols in Tab "TSM_Picks" Col G starting in row 3. In a second Tab "Services" I have rows of information, each row associated with a symbol in Col E. I need a macro that reads each symbol in "TSM_Picks", identifies all the rows associated with the same symbol in "Services" and then copies those rows to a Tab "Results". Note, each symbol in "TSM_Picks" may show up in "Services" zero, one or several times.

Thanks for your help,

Ric

 

[Peter_SSs]

What are these "symbols"?
Any chance we could see some small sample dummy data at least for 'TSM_Picks'?

 

[rwmill9716]

This is what TSM_Picks looks like.

Excel 2010
	G	H	I	J	K
1	5/11/2018
2	TSM Match with Insider Transactions	Price	Insider Buy (P) or Sell (S)	Next Earnings Report Date	Zacks Rank (Current)
3	AAPL	$188.59	S	20180807	3
4	BAH	40.85	S	20180529	2
5	BAX	70.52	S	20180725	2
6	BRKS	$29.56	S	20180801	2
7	CAR	$43.40	S	20180806	1
8	DIOD	$32.56	S	20180814	2
9	ECHO	$28.10	S	20180726	1
TSM_Picks

This is what Services looks like.

Excel 2010
	A	B	C	D	E	F	G	H
1	Buy(P) Sell(S)	P/S date	File Date	Company	Symbol	Insider	Relationship	Share Amt.
2	S	5/22/2018	5/24/2018 17:12	AAC Holdings, Inc.	AAC	Cartwright Michael T.	director officer 10% owner (Chairman and CEO)	100,000
3	S	5/22/2018	5/23/2018 16:42	American Airlines Group Inc.	AAL	Isom Robert D Jr	officer (President)	10,000
4	S	5/23/2018	5/25/2018 18:32	APPLE INC	AAPL	Srouji Johny	officer (Senior Vice President)	14,976
5	S	5/22/2018	5/24/2018 16:51	ABAXIS INC	ABAX	ARON KENNETH	officer (Chief Technology Officer)	1,533
6	S	5/18/2018	5/22/2018 8:04	ABAXIS INC	ABAX	SINGH PRITHIPAL	director	20,000
7	S	5/18/2018	5/22/2018 16:36	ACORDA THERAPEUTICS INC	ACOR	Sabella Lauren M	officer (Chief Commercial Officer)	7,288
8	S	5/18/2018	5/22/2018 16:34	ACORDA THERAPEUTICS INC	ACOR	BLANK BURKHARD	officer (Chief Medical Officer)	25,000
9	S	5/22/2018	5/24/2018 16:43	Advantego Corp	ADGO	Grey Philip F.	director officer 10% owner (CEO)	10,000
10	P	5/22/2018	5/24/2018 16:42	Advanced Disposal Services, Inc.	ADSW	MROZEK ERNEST J	director	1,500
11	P	1/19/2018	5/22/2018 11:59	ADT Inc.	ADT	Dale Robert M	officer (SVP, National Account Sales)	8,000
12	P	1/19/2018	5/22/2018 11:55	ADT Inc.	ADT	Pulliam Amelia	officer (SVP, Chief Human Resources Off)	1,500
13	S	5/24/2018	5/25/2018 16:13	AMEREN CORP	AEE	Lindgren Mark C	director other	5,500
14	P	5/23/2018	5/24/2018 16:21	ABERDEEN EMERGING MARKETS EQUITY INCOME FUND, INC.	AEF	CITY OF LONDON INVESTMENT MANA	various 10% owner	25,600
15	S	5/22/2018	5/22/2018 17:59	AEHR TEST SYSTEMS	AEHR	HENDRICKSON DAVID S	officer (Chief Technology Officer)	5,000
Services

Note, none of these match here, but when the Symbols from TSM_Picks matches the Symbol in Services, which can happen multiple times, I want to copy the entire Row in Services to a Tab called Results. Note, the data in Services extends to the O Collumn.

 

[Fluff]

How about

Sub FltrCopy()
   Dim Ary As Variant
   
   With Sheets("TSM_Picks")
      Ary = Application.Transpose(.Range("G3", .Range("G" & Rows.Count).End(xlUp)).Value)
   End With
   With Sheets("Services")
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:O1").AutoFilter 5, Ary, xlFilterValues
      .AutoFilter.Range.Offset(1).Copy Sheets("Results").Range("A" & Rows.Count).End(xlUp).Offset(1)
      .AutoFilterMode = False
   End With
End Sub

 

[rwmill9716]

Thank you, Fluff, this does exactly what I wanted.

Ric

 

[Fluff]

Glad we could help & thanks for the feedback