Combine text cells using common id numbers

Gain

New Member
Joined
Jun 4, 2012
Messages
45
Hi,

I have 15 spreadsheets, each having a column of id numbers for chemicals and a column of corresponding chemical names. Each file is a list of chemicals that cause one condition. Each file is a list of chemicals that are either recognized or suspected of causing a certain health condition. For example, one file contains a list of chemicals that are recognized as being carcinogenic (causing cancer), and has one column of chemical names and the next column with the corresponding chemical id numbers. Another example: One file contains a list of chemicals that are suspected of causing developmental issues, with one column of chemical names and corresponding id numbers.

I need to make one master spreadsheet with all of the chemicals listed with all of the effects that they are either recognized of causing or are suspected of causing, in the following format:

IDCHEMICAL NAMEHAZARDVALUE
1BenzeneCancer, developmental, Respiratoryrecognized
1BenzeneGI tract, neurotoxicsuspected
2ArsenicReproductiverecognized
2Arsenicsuspected
3Fluorenerecognized
3FluoreneSkin or sense organ, musculoskeletalsuspected

<tbody>
</tbody>

Any help would be greatly appreciated,

Thank you in advance
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
I reckon VLookup would be probably the easiest way to go, but it will probably become a very long formula since you have 15 different files.

1. Create two master template with all of the element ids (one for recognized, one for suspected)

2. Use a formula in the sorts of:
Code:
=VLOOKUP(ID, FILE#1,COL#B,0) &", "& VLOOKUP(ID, FILE#2, COL#B,0) &", "& VLOOKUP(ID, FILE#3, COL#B,0) (...)

The &" ,"& is a separator. I reckon you can do that once for the recognized and once for the suspected, and just add to the end of the other table later and filter alphabetically or by ID and it should be in order.

Let me know if this helps you or not.
 
Upvote 0
When I enter the function, and drag the cell away, the text just is copied into each cell and no values are returned. Here is the function I wrote, with spaces to make it easier to read:
=vlookup (CAS Registry Number (or EDF Substance ID), '[Recognized Reproductive Toxicity.csv]Recognized Reproductive Toxicit'!$B:$B,0) &", "&
vlookup(CAS Registry Number (or EDF Substance ID),'[Suspected Carcinogen.csv]Suspected Carcinogen'!$B:$B,0) &", "&
vlookup(CAS Registry Number (or EDF Substance ID),'[Suspected Cardiovascular or Blood Toxicity.csv]Suspected Cardiovascular or Blo'!$B:$B,0) &", "&
vlookup(CAS Registry Number (or EDF Substance ID),'[Suspected Developmental Toxicity.csv]Suspected Developmental Toxicit'!$B:$B,0) &", "&
vlookup(CAS Registry Number (or EDF Substance ID), '[Suspected Endocrine Toxicity.csv]Suspected Endocrine Toxicity'!$B:$B, 0) &", "&
vlookup (CAS Registry Number (or EDF Substance ID), '[Suspected Gastrointestinal or Liver Toxicity.csv]Suspected Gastrointestinal or L'!$B:$B, 0) &", "&
vlookup(CAS Registry Number (or EDF Substance ID), '[Suspected Immunotoxicity.csv]Suspected Immunotoxicity'!$B:$B, 0) &", "&
vlookup (CAS Registry Number (or EDF Substance ID), '[Suspected Kidney Toxicity.csv]Suspected Kidney Toxicity'!$B:$B, 0) &", "&
vlookup (CAS Registry Number (or EDF Substance ID), '[Suspected Musculoskeletal Toxicity.csv]Suspected Musculoskeletal Toxic'!$B:$B, 0) &", "&
vlookup(CAS Registry Number (or EDF Substance ID), '[Suspected Neurotoxicity.csv]Suspected Neurotoxicity'!$B:$B, 0) &", "&
vlookup(CAS Registry Number (or EDF Substance ID), '[Suspected Reproductive Toxicity.csv]Suspected Reproductive Toxicity'!$B:$B, 0) &", "&
vlookup(CAS Registry Number (or EDF Substance ID), '[Suspected Respiratory Toxicity.csv]Suspected Respiratory Toxicity'!$B:$B, 0) &", "&
vlookup (CAS Registry Number (or EDF Substance ID), '[Suspected Skin or Sense Organ Toxicity.csv]Suspected Skin or Sense Organ T'!$B:$B, 0) &", "&
vlookup(CAS Registry Number (or EDF Substance ID), '[Recognized Developmental Toxicity.csv]Recognized Developmental Toxici'!$B:$B, 0) &", "&
vlookup (CAS Registry Number (or EDF Substance ID), '[Recognized Carcinogen.csv]Recognized Carcinogen'!$B:$B, 0)

The ID is called "CAS Registry Number (or EDF Substance ID)" , the file names are in square brackets and the list of ID numbers is in column B
 
Upvote 0
Didn't look at this too closely, but I'm guessing you would benefit by splitting this formula into multiple cells and then compiling them into one final formula that references each of the cells instead of listing out this monster formula. Also makes it MUCH easier to troubleshoot down the line by your or anyone else who happens to stumble upon it.
 
Upvote 0
When I input a single vlookup, I get an error message that says "The formula you typed contains an error". Here's the line I used:
=vlookup (CAS Registry Number (or EDF Substance ID), '[Recognized Reproductive Toxicity.csv]Recognized Reproductive Toxicit'!$B:$B,0)
Do you have any ideas about the error in the formula?
 
Upvote 0

Forum statistics

Threads
1,211,678
Messages
6,103,239
Members
447,848
Latest member
holale

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top