VLookup or maybe something completely different??

Hi- I am not even sure if this was the correct category to post the question, but I have such a specific thing I am trying to do that I am really having an impossible time trying to google it and find answers in this forum.

Below, I try to describe the situation and give a generic example- please let me know what information is missing or how I can better explain what I am hoping to do.

Here's my situation: I have two different spreadsheets and I need to somehow find all the numbers in the first spreadsheet in the second. Basically I have a list of ID numbers that are all associated with one code. What complicates the matter is that any given ID number can also be associated with several other codes. So, I have a spreadsheet that solely has the list of ID #'s that were associated with just the one code, and then I have a master spreadsheet that lists every single ID number (even those without any association to the first code), and for each time the ID number is associated with a new code, it has a new row in the spreadsheet.

Ultimately, I would like to somehow filter the master spreadsheet so that it only has the ID numbers from the "one code" spreadsheet, BUT keeps all rows associated with that particular ID number- in other words, I am able to see all of the various codes associated with those ID numbers. Does that make sense at all?

So, to give a practical example (in case it helps), let's say that I run my own doctor's office. Each patient has their own unique ID number in the system. Throughout the year, that person has many different treatments- one time they come because they need a prescription, another time it's just their annual check-up and another time let's say that they sprained their ankle. My first spreadsheet simply tells me any patient that got a prescription- let's say there are 50. It only has the list of ID's (in a column) for those patients that have gotten a prescription.

My master list, on the other hand, includes every single patient I have ever had, and the layout is such that one patient has as many rows as they have had different treatments. So the person described above has 3 total rows, each of the three has the patient's ID # in Column A, then in Column B, one row says prescription, the 2nd row says annual visit and the 3rd row as ankle sprain.

Ultimately, I want to be able to filter that master list and somehow (hopefully) copy the column that lists the 50 patient ID's from the first spreadsheet (representing those patients that have needed a prescription) and somehow (no clue as to how) filter the master spreadsheet so that I am left with the pool of 50 patients, but now I can see every single treatment they have come in for, in addition to needing a prescription.

does that make any sense at all??? Anyone? Bueller? The spreadsheets I am actually working with have over 500 rows and I do this weekly so I just cannot be dedicating 6 hours each week to manually match each ID number- I'd go crazy!!

Thanks so much for the help!

You should attach it to this thread, remember, this is a public forum so be sure to take out any personal content, and also remember, at any time you can remove a file once you post. However keep in mind once a user uploads it, they already have the content in their posession.

Ok, See third sheet in your workbook, if you need more rows, just copy paste

Hope this helps


Please consider:
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
Thanking those who helped you. Click the scales icon (Reputation) in the upper right corner of the contributor's post.

I think macro will come in handy for alot of different things.