I have two spreadsheets:

  • The first contains a large amount of data on research projects. Each project has its own unique ID, listed in the first column of the spreadsheet. However, these IDs are listed multiple times as the spreadsheet contains the details each organisation participating in each project.

  • The second contains a single column that lists the unique project IDs I am interested in (approximately 400 of them), that I have extracted from a different spreadsheet.

Is anyone able to suggest a formula that would:

  • Compare each unique project ID listed in the second spreadsheet to those in the first spreadsheet, and then select (or, even better extract to a new sheet) all the first spreadsheet rows that have a matching unique ID?

I use Excel very little and have no knowledge of Visual Basic, which is what I suspect I may need to use to do what I want. I've been through numerous tutorials on various Excel formulas, conditional formatting and so on, but I cannot find anything close to what I require.

Any help much appreciated.

  • 1
    Please note that superuser.com is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?. – DavidPostill Jan 4 '17 at 20:06

Duplicate the datasheet. Sort it by project id.

Now all of your data for a given project is in a single block.

Copy the ID's you are interested in to a new sheet. Create a named range for that data.

Back to your sorted sheet. Add a new column.


X is a cell reference to the cell with the project ID

PID-range is the name of the range with the project ID's that you are inerested in.

If the match is found, MATCH returns a number, if not, it returns N/A since in this case we asked for an exact match (...range,0)

The last 0 is what we put in place of the N/A code.

Now, you can filter the sheet, excluding all the stuff you aren't interested in.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.