Keeping Rows that Match Another Worksheet

[Guchman]

Hi,

I thought I had found the answer to my question but it didn't work so now I'm here.

I need to remove rows that do NOT match the contents of a separate workbook. The goal is to have the rows in my Account List workbook only contain data for account numbers that are in my Trade Blotter workbook. I'm not sure if VLOOKUP is the best formula to use but that's what I tried based on some information I have but it didn't work so I think I constructed the formula incorrectly or there's another formula that would be better to use.

How can I delete rows that don't match the account numbers in the Trade Blotter workbook?

Also, how do I apply the formula for each row in my Account List workbook so it looks at A2, A3, A4 etc.? It might be the way I had the formula constructed but when I tried to apply the formula all the way down the rows it just copied the A2 instead of doing A3 for the next one down etc.

[#2]

@Guchman 

One of the delightful aspects of Excel is that there are always multiple ways to accomplish a given objective. In your case, I have some questions and suggestions. It would help--let me say this in advance--if you were to post a copy of your actual workbook or (if it contains proprietary info) a replica with false names and identifiers that would still enable us to see what you're working with.

If I were in your place, I think I'd want to keep in a basic raw database clients who had been clients in the past but are for the moment not in your blotter. Having a historical record can come in handy, along with a clear way to know who is current. Toward that end, what you might want to have is made of multiple parts: 

1. a database on one worksheet that contains all past and current clients
2. a sheet that shows only current
3. your "TradeBlotter" workbook
4. other related items

To create item #2 above, from #1 and #3 you could use Power Query (which I don't have on my Mac) or, if you have the most current version of Excel, you might well be able to make use of FILTER. Here's a video that explains the FILTER function. https://www.youtube.com/watch?v=9I9DtFOVPIg

Your secondary question regarding the creation of a series of identifiers--A2, A3, A4, etc--is readily resolved (part of the problem of course is that it's not just simple math here because what you've created is text rather than numbers), but secondary to the primary issue. Let's deal with it later.