Combine text cells using common id numbers

[Gain]

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]CHEMICAL NAME[/TD]
[TD]HAZARD[/TD]
[TD]VALUE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Benzene[/TD]
[TD]Cancer, developmental, Respiratory[/TD]
[TD]recognized[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Benzene[/TD]
[TD]GI tract, neurotoxic[/TD]
[TD]suspected[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Arsenic[/TD]
[TD]Reproductive[/TD]
[TD]recognized[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Arsenic[/TD]
[TD][/TD]
[TD]suspected[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Fluorene[/TD]
[TD][/TD]
[TD]recognized[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Fluorene[/TD]
[TD]Skin or sense organ, musculoskeletal[/TD]
[TD]suspected[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be greatly appreciated,

Thank you in advance

 

[mikerickson]

Have you looked at Advanced Filter?

 

[Gain]

No I haven't, how can I use it to combine the data?

 

[giusepesm]

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:

=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.

 

[Gain]

Can you give me an example of what the master template would look like?

 

[giusepesm]

[TABLE="width: 500"]
<tbody>[TR]
[TD]IDS
[/TD]
[TD]NAME[/TD]
[TD]HAZARD[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]X[/TD]
[TD]=VLOOKUP FORMULA[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Y[/TD]
[TD]=VLOOKUP FORMULA[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gain]

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

 

[mathchick]

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.

 

[Gain]

What formula can I use to compile all of the data?

 

[Gain]

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?