Forum Discussion
Solved
moving multiple values in the same cell to a new one
Hello I have a long list (column) of multiple values and I am looking for an easier way to cut one value or more to another cell. In other words, I need to seperate two or more values from the same...
Hi, it worked well on most cells, thanks!
I wonder what is different in the cells it did not work on
will it work for text too?
now I am referring to text. actually it's two lists of chemical compounds
My last answer was still for your first question.
For the second one. You can use conditional formatting with "use formula to determine which cells to format"
The formula to apply to column C is
=NOT(ISNA(VLOOKUP($C1,$A:$A,1,0)))
The formula to apply to column A is the same by switching As with Cs and vice-versa.
Roy_lavi , from my point of view it works correctly (I mean COUNTIF), please check attached
By mistake, my example had columns A and C when it should be A and B. So, in the formulas, replace C with B.
The formulas do not go into cells, they go in the dialogue box of conditional formatting.
Select column A > Home tab > Conditional Formatting button > New Rule> select "use formula to determine..." > enter the formula for the column A there > click format > choose the format you want to happen when the formula is true.
Then select column B and do the same, switching the formula accordingly.
I think it only partly worked.
column a
Acenanaphthene-d10 (IS) Acenaphthene Acenaphthylene Acetophenone 2-Acetylaminofluorene 1-Acetyl-2-thiourea Aldrin 2-Aminoanthraquinone Aminoazobenzene 4-Aminobiphenyl Anilazine Aniline o-Anisidine Anthracene Aramite Atrazine Azinphos-methyl (Guthion) Azobenzene Barban Benzaldehyde Benz(a)anthracene Benzidine Benzo(b)fluoranthene Benzo(k)fluoranthene Benzoic Benzo(g,h,i)perylene Benzo(a)pyrene Benzo(e)pyrene p-Benzoquinone Benzyl alcohol a-BHC beta-BHC gamma-BHC (Lindane) d-BHC 1,1'-Biphenyl Bis(2-chloro-1-methylethyl)ether Bis(2-chloroethyl)ether Bis(2-chloroethoxy)methane Bis(2-ethylhexyl)phthalate Bromoxynil (Brominal) 4-Bromophenyl phenyl ether Butyl benzyl phthalate Caprolactam Captafol Captan Carbaryl (Sevin) Carbazole Carbofuran (Furaden) Carbophenothion Chlorfenvinphos 4-Chloroaniline Chlorobenzilate 5-Chloro-2-methylaniline 4-Chloro-3-methylphenol 1-Chloronaphthalene 2-Chloronaphthalene 2-Chlorophenol 4-Chlorophenyl phenyl ether 4-Chloro-1,2-phenylenediamine 4-Chloro-1,3-phenylenediamine Chrysene Chrysene-d12 (IS) Coumaphos p-Cresidine Crotoxyphos 2-Cyclohexyl-4,6-dinitrophenol 4,4'-DDD 4,4'-DDE 4,4'-DDT Demeton-O Demeton-S Diallate (cis or trans) 2,4-Diaminotoluene Dibenz(a,j)acridine Dibenz(a,h)anthracene Dibenzo(a,e)pyrene Dibenzofuran 1,2-Dibromo-3-chloropropane (DBCP) Di-n-butyl phthalate Dichlone 1,2-Dichlorobenzene 1,3-Dichlorobenzene 1,4-Dichlorobenzene 1,4-Dichlorobenzene-d4 (IS) 3,3'-Dichlorobenzidine 2,4-Dichlorophenol 2,6-Dichlorophenol Dichlorovos (DDVP,Dichlorvos) Dicrotophos Dieldrin Diethyl phthalate Diethyl sulfate Diethylstilbestrol Dimethoate 3,3'-Dimethoxybenzidine Dimethyl aminoazobenzene Dimethyl phthalate 7,12-Dimethylbenz(a)anthracene 3,3'-Dimethylbenzidine 2,4-Dimethylphenol α,α-Dimethylphenylamine 1,2-Dinitrobenzene 1,3-Dinitrobenzene (1,3-DNB) 1,4-Dinitrobenzene 4,6-Dinitro-2-methylphenol 2,4-Dinitrophenol 2,6-Dinitrophenol 2,4-Dinitrotoluene 2,6-Dinitrotoluene Dinocap Dinoseb (DNBP) Di-n-octyl phthalate 1,4-Dioxane 5,5-Diphenylhydantoin 1,2-Diphenylhydrazine Diphenylamine Disulfoton Endosulfan I Endosulfan II Endosulfan sulfate Endrin Endrin aldehyde Endrin ketone EPN Ethion Ethyl carbamate Ethyl methanesulfonate Famphur Fensulfothion Fenthion Fluchloralin Fluoranthene Fluorene 2-Fluorobiphenyl (surr) 2-Fluorophenol (surr) Heptachlor Heptachlor epoxide Hexachlorobenzene Hexachlorobutadiene Hexachlorocyclopentadiene Hexachloroethane Hexachlorophene Hexachloropropene Hexamethylphosphoramide (HPMA) Hydroquinone Indeno(1,2,3-cd)pyrene Isodrin Isophorone Isosafrole Kepone Leptophos Malathion Maleic anhydride Mestranol Methapyrilene Methoxychlor Methyl methanesulfonate Methyl parathion 3-Methylcholanthrene 4,4'-Methylenebis(2-chloroaniline) 4,4'-Methylenebis(N,N-dimethyl-aniline) 1-Methylnaphthalene 2-Methylnaphthalene 2-Methylphenol 3/4-Methylphenolc Mevinphos Mexacarbate Mirex Monocrotophos Naled Naphthalene Naphthalene-d8 (IS) 1,4-Naphthoquinone 1-Naphthylamine 2-Naphthylamine Nicotine 5-Nitroacenaphthene 2-Nitroaniline 3-Nitroaniline 4-Nitroaniline 5-Nitro-o-anisidine Nitrobenzene Nitrobenzene-d5 (surr) 4-Nitrobiphenyl Nitrofen 2-Nitrophenol 4-Nitrophenol 4-Nitroquinoline-1-oxide N-Nitrosodi-n-butylamine N-Nitrosodiethylamine N-Nitrosodimethylamine N-Nitrosodiphenylamine N-Nitrosodi-n-propylamine N-Nitrosomethylethylamine N-Nitrosomorpholine N-Nitrosopiperidine N-Nitrosopyrrolidine 5-Nitro-o-toluidine Octamethylpyrophosphoramide 4,4'-Oxydianiline Parathion Pentachlorobenzene Pentachloronitrobenzene Pentachlorophenol Perylene Perylene-d12 (IS) Phenacetin Phenanthrene Phenanthrene-d10 (IS) Phenobarbital Phenol Phenol-d6 (surr) 1,4-Phenylenediamine Phorate Phosalone Phosmet (Imidan) Phosphamidon Phthalic anhydride 2-Picoline Piperonyl sulfoxide Pronamide (Kerb) Propylthiouracil Pyrene Pyridine 3-(Chloromethyl)pyridine hydrochloride Resorcinol Safrole Strychnine Sulfallate Terbufos Terphenyl-d14 (surr) 1,2,4,5-Tetrachlorobenzene 2,3,4,6-Tetrachlorophenol Tetrachlorvinphos (Stirophos,Gardona) Tetraethyl dithiopyrophosphate Tetraethyl pyrophosphate (TEPP) Thionazine Thiophenol (Benzenethiol) 2,4-Toluene diisocyanate o-Toluidine Toxaphene 2,4,6-Tribromophenol (surr) 1,2,4-Trichlorobenzene 2,4,5-Trichlorophenol 2,4,6-Trichlorophenol O,O,O-Triethyl phosphorothioate Trifluralin (Treflan) Trimethyl phosphate 2,4,5-Trimethylaniline 1,3,5-Trinitrobenzene Tris(2,3-dibromopropyl)phosphate Tri-p-tolyl phosphated column b
Pyridine N-Nitrosodimethylamine Phenol Aniline Bis(2-chloroethyl)ether 2-Chlorophenol 1,3-Dichlorobenzene 1,4-Dichlorobenzene Benzyl alcohol 1,2-Dichlorobenzene 2-Methylphenol (o-cresol) 2,2'-oxybis(1-chloropropane) 3-Methylphenol (m-cresol) 4-Methylphenol (p-cresol) N-Nitroso-di-n-propylamine Hexachloroethane Nitrobenzene Isophorone 2-Nitrophenol 2,4-Dimethylphenol Bis(2-chloroethoxy)methane 2,4-Dichlorophenol 1,2,4-Trichlorobenzene Naphthalene 4-Chloroaniline Hexachlorobutadiene 4-Chloro-3-methylphenol 2-Methylnaphthalene 1-Methylnaphthalene Hexachlorocyclopentadiene 2,4,6-Trichlorophenol 2,4,5-Trichlorophenol 2-Chloronaphthalene 2-Nitroaniline 1,4-Dinitrobenzene Acenaphthylene 1,3-Dinitrobenzene Dimethylphthalate 2,6-Dinitrotoluene 1,2-Dinitrobenzene Acenaphthene 3-Nitroaniline 2,4-Dinitrophenol Dibenzofuran 2,4-Dinitrotoluene 4-Nitrophenol 2,3,4,6-Tetrachlorophenol 2,3,5,6-Tetrachlorophenol Fluorene 4-Chlorophenyl phenyl ether Diethylphthalate 4-Nitroaniline 4,6-Dinitro-2-methylphenol (Dinitro-o-cresol) Diphenylamine Azobenzene 4-Bromophenyl phenyl ether Hexachlorobenzene Pentachlorophenol Phenanthrene Anthracene Carbazole Di-n-butylphthalate Fluoranthene Pyrene Benzyl butyl phthalate Bis(2-ethylhexyl)adipate Benz(a)anthracene Chrysene Bis(2-ethylhexyl)phthalate Di-n-octyl phthalate Benzo(b)fluoranthene Benzo(k)fluoranthene Benzo(a)pyrene Indeno(1,2,3-cd)pyrene Dibenz(a,h)anthracene Benzo(g,h,i)perylene column c
TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE FALSE FALSE FALSE TRUE TRUE FALSE TRUE TRUE TRUE TRUE TRUE TRUE FALSE TRUE TRUE FALSE TRUE FALSE TRUE FALSE FALSE TRUE TRUE TRUE FALSE FALSE FALSE TRUE TRUE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE TRUE TRUE FALSE
