Need help with querying values

Hi,

I got unique Ticker values with Frequency=1 in Sheet 2. I want to query these unique values where all the related data in Sheet 1 should be copied in Sheet 3. The sample sheet is attached for better understanding.

In Sheet 2 (Test sheet), you can see unique Ticker value for author where frequency=1. I want help in querying the data in such a way that all the yellow highlighted instances in Sheet 1 (List sheet) should be copied to Sheet 3 (Test2).

Can anyone help me with the VBA query or any formula to achieve this will be great!

Thanks!

Perhaps a macro like this?

Sub Filter_rang()
Sheets(1).Activate
Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=RGB(255, 255, 0), Operator:=xlFilterCellColor
ActiveSheet.AutoFilter.Range.Copy
Sheets("Test2").Range("A1").PasteSpecial xlPasteValues
Sheets("Test2").Range("A1").PasteSpecial xlPasteFormats
Application.CutCopyMode = False
ActiveSheet.AutoFilterMode = False
Sheets("Test2").Activate
Columns("J:J").Delete Shift:=xlToLeft
Columns("H:H").Delete Shift:=xlToLeft
End Sub

I've used both "xlPasteValues" and "xlPasteFormats" instad of just using "xlPasteAll" since there are formulas in your data and I'm not to shure if the result stays the same after copying.

Alf

Hi Alf,

Let me give this a try. Will get back to you soon.

Thanks!