Match text and numbers from two spreadsheets based on common value

[Gain]

Hi,

I have one spreadsheet with two columns, with the headers: ID number and Chemical name. I have another spreadsheet with six columns: ID number, Chemical name, TEP 1, TEP 2, TEP 3 and TEP 4 (TEPs are numeric risk scores). I need to know what the TEP values are for the chemicals in the first spreadsheet. Not every chemical in the first spreadsheet have TEP values, but all of the chemicals that do have a TEP value will be listed in the second spreadsheet.

In the second spreadsheet, the cells in the TEP columns can either have a number or have a blank cell. A blank cell means that the TEP was not calculated or that there isn't enough information to calculate.

Some of the ID numbers and chemical names in the spreadsheets are not named exactly the same. For example, one name might be 'Arsenic (and its compounds)' and another might be 'Arsenic compounds', but they're both referring to the same group of chemicals, which has a set of TEP values. Another nuisance is that there are some ID numbers that are different for the same chemical. For example, for the same chemical, one spreadsheet has an ID number of 'NA-03' and the other will have it as 'CMJ500'.

My guess would be to do some sort of partial matching macro for chemical names, and returning all 4 TEPs for partially matching values in the next columns, but I don't know how to code for this.

The result would have all of the chemicals in the first spreadsheet, with six columns: ID number (either the NA-__ one or the six letter/number), chemical name, TEP 1, TEP 2, TEP 3, and TEP 4. Is this possible?

Thank you in advance, any help would be greatly appreciated

 

[smiley3141]

Hi Gain.

I would suggest that you create a master ID List that has three columns. The first column in the master ID list is the ID number on the first spreadsheet. The second column is the master number (the one that you will use to connect both spreadsheet numbers). The third column will be the ID number on the second spreadsheet. In what follows, I will call the three column range containing these numbers "Master_ID_List".

Then, on the second spreadsheet, I would add a column to the left of the data. The column you added will contain the number from the Master_ID_List that corresponds to the ID on the second spreadsheet.

After you do this, you can go back to the first spreadsheet, and then in the row for each ID number you can use a formula like: Vlookup(Vlookup(Spreadsheet1_ID#,Master_ID_List,2,false),Spreadsheet2_DataRAnge,3,false). In this formula, the "3" would be whatever the corresponding row is in your spreadsheet two data.

Also, if you do not want to manually input the Master_ID_List numbers on the spreadsheet two column you add, you can use some sort of vlookup or match formula to match them to the spreadsheet2 ID #s.

This may be a lot of work up front, but I imagine it would be a more effective use of your time than always having to check whether or not your partial matches work.

Hopefully that helps.