Need to filter out specific identify a value in a list. Can I use VLOOKUP

[nmss18]

I have a large table that outlines the volume a client sent to a broker.
Below is a sample table:

So Lobalg sent volume to 4 brokers
ABCD Capital sent to 2 brokers etc.

Client Name</SPAN>	Broker</SPAN>	Total</SPAN>
Lobalg Tvara LLC</SPAN>	Morgan Stanley & Co</SPAN>	###</SPAN>
	JP Morgan Chase Bank</SPAN>	###</SPAN>
	Goldman Sachs</SPAN>	###</SPAN>
	Credit Suisse</SPAN>	###</SPAN>
ABCD Capital AG</SPAN>	JP Morgan Chase Bank</SPAN>	###</SPAN>
	Goldman Sachs</SPAN>	###</SPAN>
Electric Capital Services UK Ltd.</SPAN>	Wells Fargo Securities LLC</SPAN>	###</SPAN>
	United First</SPAN>	###</SPAN>
	Baypoint Trading</SPAN>	###</SPAN>
	Aviate Global</SPAN>	###</SPAN>
PRAGUE CAPITAL, LLC</SPAN>	UBS</SPAN>	###</SPAN>
	Knight Direct LLC</SPAN>	###</SPAN>
	Jefferies</SPAN>	###</SPAN>
	Goldman Sachs</SPAN>	###</SPAN>
	Deutsche Bank</SPAN>	###</SPAN>
	Credit Suisse</SPAN>	###</SPAN>
	BNYCONVERGEX (FT)</SPAN>	###</SPAN>
Starbright Capital Management</SPAN>	William Blair & Company</SPAN>	###</SPAN>
	Wells Fargo Securities LLC</SPAN>	###</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

What I need to do is to filter out those clients who have Goldman Sachs in their list. In the table example above there are 5 clients and 3 of them have sent volume to Goldman so I need to create a table that looks like this:

Client Name</SPAN>	Broker</SPAN>	Total</SPAN>
Lobalg Tvara LLC</SPAN>	Morgan Stanley & Co</SPAN>	###</SPAN>
	JP Morgan Chase Bank</SPAN>	###</SPAN>
	Goldman Sachs</SPAN>	###</SPAN>
	Credit Suisse</SPAN>	###</SPAN>
ABCD Capital AG</SPAN>	JP Morgan Chase Bank</SPAN>	###</SPAN>
	Goldman Sachs</SPAN>	###</SPAN>
PRAGUE CAPITAL, LLC</SPAN>	UBS</SPAN>	###</SPAN>
	Knight Direct LLC</SPAN>	###</SPAN>
	Jefferies</SPAN>	###</SPAN>
	Goldman Sachs</SPAN>	###</SPAN>
	Deutsche Bank</SPAN>	###</SPAN>
	Credit Suisse</SPAN>	###</SPAN>
	BNYCONVERGEX (FT)</SPAN>	###</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

I am trying to figure out if a VLOOKUP is the solution here but I dont know how I would do it. I would have to figure out something that would concatenate the client value with the broker value and perhaps do some sort of LEN and FIND formula.
I am hoping there is a much much easier way to do this.
Any suggestions or ideas would be welcome.
Thanks,
nmss

 

[dermie_72]

copy your data into a new table, and run the below code:

Sub nmss18()
Dim LR As Long
Dim nme As String
LR = Cells(Rows.Count, "B").End(xlUp).Row
nme = Cells(2, 1).Value
For I = 3 To LR
    If Cells(I, 1).Value = "" Then
        Cells(I, 1).Value = nme
        Else: nme = Cells(I, 1).Value
    End If
Next I
End Sub

This will populate the Client name next to the Broker.
You can then Copy all of the client names into a new field and remove duplicates on that name.
Then do a countif on the column with the duplicates. this will give you the number of brokers per client.
It will also enable you to filter the broker column, got goldman sachs, to identify any clients that have them as a broker.

Hope that helps.

 

[nmss18]

Thanks for the response. Could I trouble you to provide some instructions on how to enter the macro code.
Not too sure if this is going to do what I want but I'll try. I mean, since this data is in a pivo table I could ensure that all the data labels in column A are repeated, and I could then concancetenate the values of colums A and B. I think that is what your macro would do right?
If so, I dont understand what your next steps are.

 

[dermie_72]

Copy the pivot table (including 1 heading), to a new workbook.
Press alt + F11
Click insert - Module
Double click on the module.
In the screen top right, paste the macro provided.
Alt tab back to your new excel sheet
Click on View/Macros/View Macros
Click on the name nmss18
it will fill the name of the clients down to the last populated cell.

Assuming you only have 3 columns. Copy Column A into Column E
Highlight column E, then click on Data/Remove duplicates
In F2 use the following formula
=countif(A:A,E2)
Click back on F2, then double click on the bottom right hand corner of F2 (it will make your Cursor turn into a small black +)
This autofills down the data. This shows the number of brokers per client.

Highlight Cells A1 to C1
Click on data/filter
Filter Column C for Goldman Sachs
That will show all the clients that have that broker.

Hope that helps.

 

[nmss18]

The macro worked and I did follow the remaining instructions you provided but it still doesnt get me the filtered data in the format I am looking for.

Would you know of a formula to identify a cell only if it contains certain text?

 

[dermie_72]

Highlight Cells A1 to C1
Click on data/filter
Click on the down arrow in the column you want to filter.
Click on customer filters
In the left hand drop down list, choose contains.
In the right box, enter the data you are looking for.
This will filter the column for certain text contained in a cell.

 

[nmss18]

worked! thanks.