Formula or VBA help!!!

[Luchano]

Hello all!

I need heeeeeelp! I thought I was good in Excel until I encounter this problem I'm trying to create a formula or if possible a VBA script that fulfills 3 conditions.

Let's say we have the table below, and what we need is to:

1. if a Name in column 1 appears more than once AND in the 2nd column appears "Residential Lending," then Remove All Rows Above and/or Below where the Same Name appears more than once
2. if in column 2 appears banking but in column 1 the same name appears more than once Above or Below, then leave as is
3. Else leave As Is

Any help is appreciated. Thank you in advance!!!!

Frank, LLC</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP> Residential Lending</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP> $ - </SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP> Frank, LLC</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP> Banking</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP> $ 52,793.34 </SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP> Frank, LLC</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP> Lending</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP> $ 6,395,507.27 </SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP> <TBODY> </TBODY>	</SPAN>
		</SPAN>

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

 

[Luchano]

it is right. those are the conditions. if frank has a residential account, then we remove all frank's accounts.
And in the second case, if frank doesn't have a residential account, but has a banking and other accounts we leave it as is

 

[Luchano]

So this is the case. I have a sheet with over 2000 accounts and a client may appear multiple times with multiple accounts; so,what I need to do is:

1- find out if the client has a "Residential Lending" account, and if so, I remove all of his/her accounts from the list

2- if a client has a banking account ONLY with < 250K, then the client gets removed; however, if the client has more than one account we leave as is.


Does it make sense?


Thanks very much ztodd!

 

[MARK858]

You are both too brief in your posts. You ought to post example input and output to clarify.

Why would oldbrewer as the responder want to post sample data?

 

[ztodd]

You could be more clear on #2, like this- is this what you mean?

1- find out if the client has a "Residential Lending" account, and if so, I remove all of his/her accounts from the list

2- if a client only has one account, and that one account is a banking account with < 250K, then remove all accounts for this client; otherwise, if the client has more than one account we leave all the client's records as they are.

Is that right?

What if a client only has one account, and that one account is a banking account with >= 250K?

 

[ztodd]

Why would oldbrewer as the responder want to post sample data?

Haha, got me there. Guess I should've said, "You are both too brief in your posts. Luchano, you ought to post example input and output to clarify."

 

[Luchano]

Haha, got me there. Guess I should've said, "You are both too brief in your posts. Luchano, you ought to post example input and output to clarify."

Input - output. i hope this makes more sense

Frank, LLC</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Residential Lending</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	$ - </SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Remove all Frank bc client has Residential</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
Frank, LLC</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Banking</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	$ 52,793.34 </SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Remove all Frank bc client has Residential</SPAN></SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
Frank, LLC</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Lending</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	$ 6,395,507.27 </SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Remove all Frank bc client has Residential</SPAN></SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
Joe, LLC</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Lending</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	$ - </SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Remove all Joe bc client has Residential</SPAN></SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
Joe, LLC</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Residential Lending</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	$ 784,643.80 </SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Remove all Joe bc client has Residential</SPAN></SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
Joe, LLC</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	IA</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	$ 3,279,725.03 </SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Remove all Joe bc client has Residential</SPAN></SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
Mary, LLC</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Other</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	$ - </SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	stays <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
Mary, LLC</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Brokerage Advisory</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	$ - </SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	</SPAN>stays <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
Mary, LLC</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	IA</SPAN>	$ - </SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	</SPAN>stays <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
Mary, LLC</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Brokerage Advisory</SPAN>	$ 1,040,608.00 </SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	</SPAN>stays <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
Louis, LLC</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP> </SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Banking</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	$ 44,541.52 </SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Remove bc banking and < 250K</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
Rich</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Lending</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	$ 27,000,000.00 </SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	stays <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
Rich</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Lending</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	$ - </SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	stays <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
Alfred</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Lending</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	$ - </SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Stays bc more than one acct</SPAN></SPAN>
Alfred</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Banking</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	$ 870.20 </SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Stays bc more than one acct</SPAN></SPAN>
Alfred</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Lending</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	$ - </SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Stays bc more than one acct</SPAN>

<TBODY>
</TBODY>

 

[ztodd]

Thanks Luchano, that does help. How about these questions?

You could be more clear on #2, like this- is this what you mean?

1- find out if the client has a "Residential Lending" account, and if so, I remove all of his/her accounts from the list

2- if a client only has one account, and that one account is a banking account with < 250K, then remove all accounts for this client; otherwise, if the client has more than one account we leave all the client's records as they are.

Is that right?

What if a client only has one account, and that one account is a banking account with >= 250K?

 

[Luchano]

Thanks Luchano, that does help. How about these questions?

Thanks for the support!!!

1- YES!!!

2- ALMOST THERE! to clariffy: If a client has one account (has to be banking) < 250K then we remove that account, But, if the client has a Banking account < 250K as well as other accounts, then we leave all records as they are

3- If the client has only one account and is banking with >=250, we leave the record as it is


Thanks a bunch ztodd!

 

[ztodd]

Your clarification seems to be the exact same thing that I said.

So here's how I would do it-

1. Add a column "hasOnlySmallBanking", with formula in row 2 : =if(and(b2="Banking",c2<250000,countif(A:A,A2)=1,1,0), and fill down.

2. Add a column "hasResidential", with formula in row 2 : =countifs(A:A,A2,B:B,"Residential Lending"), and fill down.

3. Verify that the rows to be deleted either have hasOnlySmallBanking = 1 or have hasResidential >= 1, and that the rows to keep have zero in both of these. <-- Important to do this before going on!!

4. Filter to hasOnlySmallBanking = 1, and delete all visible rows.

5. Unfilter, then filter to hasResidential >= 1, and delete all visible rows.

 

[Luchano]

Your clarification seems to be the exact same thing that I said.

So here's how I would do it-

1. Add a column "hasOnlySmallBanking", with formula in row 2 : =if(and(b2="Banking",c2<250000,countif(A:A,A2)=1,1,0), and fill down.

2. Add a column "hasResidential", with formula in row 2 : =countifs(A:A,A2,B:B,"Residential Lending"), and fill down.

3. Verify that the rows to be deleted either have hasOnlySmallBanking = 1 or have hasResidential >= 1, and that the rows to keep have zero in both of these. <-- Important to do this before going on!!

4. Filter to hasOnlySmallBanking = 1, and delete all visible rows.

5. Unfilter, then filter to hasResidential >= 1, and delete all visible rows.

Thanks for the reply!

Not quite though.
Using that approach I would be getting rid of banking and residential, and if the client has more than one acctount, then my data would still have the other client's acccounts.