Vba Find 


Vba Find  Excel 
View Answers 
So I have a bunch of names in column "A" of my worksheet (about 2500). I'm trying to figure out a way to use the vba find code to jump to the first letter of a name... like if someones last name starts with an "M" I want to type in the letter "M" in a cell, hit a button, and execute a code which brings me to the fist last name beginning with the letter "M".
I have all of my names listed in the format Last, First Middle. The problem with the find function obviously is that it finds EVERY occurence of the letter "M" rather than just the first occurence of the letter "M" as the first letter of the last name. Any help would be much appreciated... thanks!
I have all of my names listed in the format Last, First Middle. The problem with the find function obviously is that it finds EVERY occurence of the letter "M" rather than just the first occurence of the letter "M" as the first letter of the last name. Any help would be much appreciated... thanks!
Similar Excel Video Tutorials
Amazing Find & Go To Trick!
 See how to use the Find command to go to all cells with a certain formatting, or even a certain function! Then it is easy to make universal changes af ...
Find & Replace Format or Formula
 Find all the occurrences of a word, number, format or formula and then change or replace all of them! See how to use the Find and Replace feature in E ...
Teach Yourself Excel Lesson Find And Replace  Find And Replace
 ExcelExperts.com brings you training video on: Teach Yourself Excel Lesson Find And Replace  Find And Replace ...
Find Last Value In List
 See how to Find the Last Value in a list using three different formulas: See the functions: LOOKUP, VLOOKUP, OFFSET, MAX, MATCH. ...
Helpful Excel Macros
Excel Macro to Save a Specific Worksheet as a New File
 This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
 This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Save the Current Worksheet as a New Excel Workbook File
 This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
 This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Macro to add a New Line to Message Box Popup Windows in Excel
 This is a very simple Message Box, popup window, macro for Excel that illustrates how to put new lines, the same thi
 This is a very simple Message Box, popup window, macro for Excel that illustrates how to put new lines, the same thi
Complete Guide to Printing in Excel Macros  PrintOut Method in Excel
 This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
 This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Excel Macro that Searches Entire Workbook and Returns All Matches
 This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
 This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Similar Topics
Hello,
I am trying to build a spreadsheet that calculates a GPA.
Basically what I have is the letter grades in the C column and the value of the letter grades in D column. Instead of entering the letter grade and then the value of the letter grade, I want excel to know what the value of each letter grade is and display it accordingly in the next column.
I can figure out how to make it work with one letter:
=IF(($C2="A"),"4.0","")
I want to add in
=IF((C2="A"),"3.7","")
along with
=IF((C2="B+"),"3.3","")
etc.
Is the IF function the wrong function to do this with? How could I go about doing something like this?
Thanks a lot for your help
Sean
I am trying to build a spreadsheet that calculates a GPA.
Basically what I have is the letter grades in the C column and the value of the letter grades in D column. Instead of entering the letter grade and then the value of the letter grade, I want excel to know what the value of each letter grade is and display it accordingly in the next column.
I can figure out how to make it work with one letter:
=IF(($C2="A"),"4.0","")
I want to add in
=IF((C2="A"),"3.7","")
along with
=IF((C2="B+"),"3.3","")
etc.
Is the IF function the wrong function to do this with? How could I go about doing something like this?
Thanks a lot for your help
Sean
I am new to forums so I hope I am doing this correctly. I have a bunch of cell in column D that have multiple names of people (qty2) with a noun on the end. In the middle of these words is the appropriate 3 letter month that I am trying to find and put into a the column of cells next to each. I figured out how to do it with the IF THEN below but just can't get it into the VBA. I have made a valid attempt to figure this out to no luck. Excel wouldn't let me go to JUL because I exceeded my Nesting Levels. Can you help put this into VBA for 2007?
Example of cells with names is:
Cell D26 > JOHN SAM FEB ENGINEER
Cell D27 > SARA FRANK JAN HR
Cell D28 > JIM LORY FEB MNGR
Cell D29 > TERRY FLOYD JUN MFG
Cells in this "D" column can be anywhere from 2 or 3 to 20. It varies.
Change cell F26 to VBA> =IF(ISNUMBER(FIND(" JAN ",D26)),"JAN",IF(ISNUMBER(FIND(" FEB ",D26)),"FEB",IF(ISNUMBER(FIND(" MAR ",D26)),"MAR",IF(ISNUMBER(FIND(" APR ",D26)),"APR",IF(ISNUMBER(FIND(" MAY ",D26)),"MAY",IF(ISNUMBER(FIND(" JUN ",D26)),"JUN"," "))))))
Change cell F27 to VBA > =IF(ISNUMBER(FIND(" JAN ",D27)),"JAN",IF(ISNUMBER(FIND(" FEB ",D27)),"FEB",IF(ISNUMBER(FIND(" MAR ",D27)),"MAR",IF(ISNUMBER(FIND(" APR ",D27)),"APR",IF(ISNUMBER(FIND(" MAY ",D27)),"MAY",IF(ISNUMBER(FIND(" JUN ",D27)),"JUN"," "))))))
F28 etc
F29 etc
etc,etc.
Can you help?
Thank you,
Kutar
Portland, OR
Example of cells with names is:
Cell D26 > JOHN SAM FEB ENGINEER
Cell D27 > SARA FRANK JAN HR
Cell D28 > JIM LORY FEB MNGR
Cell D29 > TERRY FLOYD JUN MFG
Cells in this "D" column can be anywhere from 2 or 3 to 20. It varies.
Change cell F26 to VBA> =IF(ISNUMBER(FIND(" JAN ",D26)),"JAN",IF(ISNUMBER(FIND(" FEB ",D26)),"FEB",IF(ISNUMBER(FIND(" MAR ",D26)),"MAR",IF(ISNUMBER(FIND(" APR ",D26)),"APR",IF(ISNUMBER(FIND(" MAY ",D26)),"MAY",IF(ISNUMBER(FIND(" JUN ",D26)),"JUN"," "))))))
Change cell F27 to VBA > =IF(ISNUMBER(FIND(" JAN ",D27)),"JAN",IF(ISNUMBER(FIND(" FEB ",D27)),"FEB",IF(ISNUMBER(FIND(" MAR ",D27)),"MAR",IF(ISNUMBER(FIND(" APR ",D27)),"APR",IF(ISNUMBER(FIND(" MAY ",D27)),"MAY",IF(ISNUMBER(FIND(" JUN ",D27)),"JUN"," "))))))
F28 etc
F29 etc
etc,etc.
Can you help?
Thank you,
Kutar
Portland, OR
Hi,
I have three cells:
G5  can contain True or false
G6  contains text but the if is on the basis of this cell being "Hand Delivered" or not
I have tried the following to illustrate what I after:
Code:
It speaks for itself but my formula is rubbish!:
IF G5=FALSE,"Letter Not Required"
IF G5=TRUE AND G6="Hand Delivered Letter) then show "Letter Delivered"
IF G5=TRUE,"Letter Required"
There should be no else so blank will do.
Hope someone can help. Thank you in advance.
I have three cells:
G5  can contain True or false
G6  contains text but the if is on the basis of this cell being "Hand Delivered" or not
I have tried the following to illustrate what I after:
Code:
=IF(G5=FALSE,"Letter Not Required",IF(AND((G5=TRUE),(G6="Hand Delivered Letter),"Letter Delivered",IF(G5=TRUE,"Letter Required"),"","")))
It speaks for itself but my formula is rubbish!:
IF G5=FALSE,"Letter Not Required"
IF G5=TRUE AND G6="Hand Delivered Letter) then show "Letter Delivered"
IF G5=TRUE,"Letter Required"
There should be no else so blank will do.
Hope someone can help. Thank you in advance.
Dear all,
I would be very grateful if anyone could help me with the following problem:
I have one column: in the first cell comes text (beginning either with the letter "L" or "R") and afterwards, in the next cell, comes a number (either "7" or "8"). Basically the column is made up of alternating cells containing either text (code of a movie) or numbers (responses to the respective movies). I want to find a formula which writes either:
 "correct" if the number "7" follows a cell containing the letter "L" or an "8" follows a cell containing the letter "R";
or
 "incorrect" if the number "7" follows a cell starting with the letter "R" or the number "8" follows a cell starting with the letter "L".
Basically the 8 is always correct with an R and the 7 with an L:
movie R01
response 8 > this would be correct
movie R02
response 7 > this would be incorrect
movie L01
response 8 > this would be incorrect
movie L02
response 7 > this would be correct
etc.
I have tried using IF(ISNUMBER(SEARCH...but somehow, I just don't manage to make a useful formula out of it...
I would very much appreciate any ideas! Thank you very much in advance!
Alex
I would be very grateful if anyone could help me with the following problem:
I have one column: in the first cell comes text (beginning either with the letter "L" or "R") and afterwards, in the next cell, comes a number (either "7" or "8"). Basically the column is made up of alternating cells containing either text (code of a movie) or numbers (responses to the respective movies). I want to find a formula which writes either:
 "correct" if the number "7" follows a cell containing the letter "L" or an "8" follows a cell containing the letter "R";
or
 "incorrect" if the number "7" follows a cell starting with the letter "R" or the number "8" follows a cell starting with the letter "L".
Basically the 8 is always correct with an R and the 7 with an L:
movie R01
response 8 > this would be correct
movie R02
response 7 > this would be incorrect
movie L01
response 8 > this would be incorrect
movie L02
response 7 > this would be correct
etc.
I have tried using IF(ISNUMBER(SEARCH...but somehow, I just don't manage to make a useful formula out of it...
I would very much appreciate any ideas! Thank you very much in advance!
Alex
Good Morning all,
A little help would be most welcomed.
I know how to conditionally format in general, but wish to do something a little different with the attached.
In column "A" i wish to input the letter "v","D" or "N", and the row to be filled a different colour depending on the letter imputed
For example:
"A3" = "v"  row 3 infilled red.
"A4" = "D"  row 4 infilled Blue
"A5" = "N"  row 5 infilled yellow
Any help would be gratefully received
Thanks
Scott
I would like to be able to do the following in an Excel spreadsheet in a single cell:
=IF(AND(D7="U",D13D6>=3.1),"NM","X")
or
=IF(AND(D7="P",D13D6>=15.1),"NM","X")
or
=IF(AND(D7="R",D13D6>=30.1),"NM","X")
or
=IF(AND(D7="CU<24",D13D6>=3.1),"NM","X")
or
=IF(AND(D7="CU>24",D13D6>=1.1),"NM","X")
Is it possible to combine these conditions for a single cell? Also, I only want the "X" to populate if D7="specific letter" AND D13D6=3.1 is not true. If D7 does not equal the specific letter, then I don't want the "X" to populate.
CrossPost to: http://www.mrexcel.com/forum/showthread.php?t=350799
Thanks
=IF(AND(D7="U",D13D6>=3.1),"NM","X")
or
=IF(AND(D7="P",D13D6>=15.1),"NM","X")
or
=IF(AND(D7="R",D13D6>=30.1),"NM","X")
or
=IF(AND(D7="CU<24",D13D6>=3.1),"NM","X")
or
=IF(AND(D7="CU>24",D13D6>=1.1),"NM","X")
Is it possible to combine these conditions for a single cell? Also, I only want the "X" to populate if D7="specific letter" AND D13D6=3.1 is not true. If D7 does not equal the specific letter, then I don't want the "X" to populate.
CrossPost to: http://www.mrexcel.com/forum/showthread.php?t=350799
Thanks
Hi
I would like to have a macro or something running in the background of my spreadsheet so that when I enter the letter "c" in Cell B4 it becomes active and runs a formula  (B2*7)/47  and places the result in cell B4, by removing the letter "c". This is to be done as I type the info in.
So, basically as I type the letter "c" in B4  excel automatically calculates (B2*7)/47 and replaces "c" in B4 with the result of the calculation.
This is only to happen if the letter "c" is typed into B4  it should not run if any other character/figure is typed into B4  in fact if another character/figure is typed, then that character/figure should stay in B4.
Is this possible?
All help appreciated, as always.
I would like to have a macro or something running in the background of my spreadsheet so that when I enter the letter "c" in Cell B4 it becomes active and runs a formula  (B2*7)/47  and places the result in cell B4, by removing the letter "c". This is to be done as I type the info in.
So, basically as I type the letter "c" in B4  excel automatically calculates (B2*7)/47 and replaces "c" in B4 with the result of the calculation.
This is only to happen if the letter "c" is typed into B4  it should not run if any other character/figure is typed into B4  in fact if another character/figure is typed, then that character/figure should stay in B4.
Is this possible?
All help appreciated, as always.
I have a column with 1 letter in it. I need to write a formula that will look at each cell in that column and assign a numeric value depending on the letter of the alphabet. A & B would be 1, c & d would be 2, e & F would be 3 and so forth up to 10. I used a nested if then, but after about G it stopped working  I believe because it was too nested. The formula is below (I added brackets around each result). Any ideas on how to nest for all 26 letters?
Thanks.
=IF(C3="a","[1]", IF(C3="b","[1]", IF(C3="c","[2]", IF(C3="d","[2]", IF(C3="e","[3]", IF(C3="f","[3]", IF(C3="g","[4]",300)))))))
Thanks.
=IF(C3="a","[1]", IF(C3="b","[1]", IF(C3="c","[2]", IF(C3="d","[2]", IF(C3="e","[3]", IF(C3="f","[3]", IF(C3="g","[4]",300)))))))
I have this huge formula that I am using to create a field with text in it defining ingredients in food items. Is there anyway I can break it up?
Here's the formula:
Quote:
=C2&IF(SUM(D2:E2)>0," ","")
&IF(D2>0,"Vegetarian","")
&IF(D2=3," on request","")
&IF(SUM(D2:E2)=2," or ","")
&IF(E2>0,"Vegan","")
&IF(E2=3," on request","")
&IF(SUM(D2:E2)=2," on request","")
&IF(SUM(F2:O2)>0," contains (","")
&IF(P2=1," May Contain","")
&IF(F2>0,"Alcohol","")
&IF(AND(G2=1,H2=0,I2=0,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(Q2=1," May Contain","")
&IF(G2>0," Dairy","")
&IF(AND(H2=1,I2=0,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(R2=1," May Contain","")
&IF(H2>0," Eggs","")
&IF(AND(I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(S2=1," May Contain","")
&IF(I2>0," Gluten","")
&IF(AND(J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(T2=1," May Contain","")
&IF(J2>0," Soy","")
&IF(AND(K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(U2=1," May Contain","")
&IF(K2>0," Peanuts","")
&IF(AND(L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(V2=1," May Contain","")
&IF(L2>0," Nuts","")
&IF(AND(M2=1,N2=1,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(W2=1," May Contain","")
&IF(M2>0," Seeds","")
&IF(AND(N2=1,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(X2=1," May Contain","")
&IF(N2>0," Fish","")
&IF(AND(O2=1,SUM(F2:O2)>1)," &","")
&IF(Y2=1," May Contain","")
&IF(O2>0," Shell Fish","")
&IF(SUM(F2:O2)>0,")","")
Here's the formula:
Quote:
=C2&IF(SUM(D2:E2)>0," ","")
&IF(D2>0,"Vegetarian","")
&IF(D2=3," on request","")
&IF(SUM(D2:E2)=2," or ","")
&IF(E2>0,"Vegan","")
&IF(E2=3," on request","")
&IF(SUM(D2:E2)=2," on request","")
&IF(SUM(F2:O2)>0," contains (","")
&IF(P2=1," May Contain","")
&IF(F2>0,"Alcohol","")
&IF(AND(G2=1,H2=0,I2=0,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(Q2=1," May Contain","")
&IF(G2>0," Dairy","")
&IF(AND(H2=1,I2=0,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(R2=1," May Contain","")
&IF(H2>0," Eggs","")
&IF(AND(I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(S2=1," May Contain","")
&IF(I2>0," Gluten","")
&IF(AND(J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(T2=1," May Contain","")
&IF(J2>0," Soy","")
&IF(AND(K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(U2=1," May Contain","")
&IF(K2>0," Peanuts","")
&IF(AND(L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(V2=1," May Contain","")
&IF(L2>0," Nuts","")
&IF(AND(M2=1,N2=1,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(W2=1," May Contain","")
&IF(M2>0," Seeds","")
&IF(AND(N2=1,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(X2=1," May Contain","")
&IF(N2>0," Fish","")
&IF(AND(O2=1,SUM(F2:O2)>1)," &","")
&IF(Y2=1," May Contain","")
&IF(O2>0," Shell Fish","")
&IF(SUM(F2:O2)>0,")","")
I have six columns, Column A titled "Cooperation", Column B titled "Work Habits", and Column C titled "Total". Column D titled "Letter Grade", Column E titled "Cooperation Grade", and Column F titled "Work Habits Grade". I want the number that is entered into columns A, B, and C to automatically enter a corresponding letter grade into columns D, E, and F. Column A corresponds with column E, Column B with column F, and column C with column D.
If the number in column A is less than or equal to 2 i need the letter "E" to appear in column E, if the number is 34 then the letter "S" should appear, if the number is 5 or higher the letter "U" should appear.
If the number in column B is less than or equal to 2 i need the letter "E" to appear in column F, if the number is 34 then the letter "S" should appear, if the number is 5 or higher the letter "U" should appear.
If the number in column C is less than or equal to 3 i need the letter "A" to appear in column D, if the number is 46 then the letter "B" should appear, if the number is 78 the letter "C" should appear, if the number is 910 the letter "D" should appear, if the number is greater than 10 the letter "F" should appear.
I hope this makes sense. Thanks for looking
If the number in column A is less than or equal to 2 i need the letter "E" to appear in column E, if the number is 34 then the letter "S" should appear, if the number is 5 or higher the letter "U" should appear.
If the number in column B is less than or equal to 2 i need the letter "E" to appear in column F, if the number is 34 then the letter "S" should appear, if the number is 5 or higher the letter "U" should appear.
If the number in column C is less than or equal to 3 i need the letter "A" to appear in column D, if the number is 46 then the letter "B" should appear, if the number is 78 the letter "C" should appear, if the number is 910 the letter "D" should appear, if the number is greater than 10 the letter "F" should appear.
I hope this makes sense. Thanks for looking
This is really annoying, my formula is not picking up words starting with the letter "Y" it will pick "Y" up on its own but not if you start a word with that letter, see my attachment and change the word "Alpha" in column A to "Yoda" or something begining with "Y" and you will see what i mean.
Thanks
DJ
Thanks
DJ
I'm a total Excel VBA dunce and wondered if there is a more efficient way to do this. I have a long list of names in Column A sorted alphabetcially. I dont know how long the list is.
I wish to find the start and end row numbers of names that begin with a specific letter, for example if the letter is B I want the first row number that starts with B and the row number that ends with B. Assuming the letter I want to use is in C1, here is what I came up with:
Code:
Please dont laugh at me or throw rotten fruit
I wish to find the start and end row numbers of names that begin with a specific letter, for example if the letter is B I want the first row number that starts with B and the row number that ends with B. Assuming the letter I want to use is in C1, here is what I came up with:
Code:
Sub find_range() Dim start As String Dim FinalRow As Long Dim i As Integer i = 1 start = Cells(1, 3) FinalRow = Range("A65536").End(xlUp).Row: Debug.Print FinalRow 'Loop through names starting with "start" Do Until Letter = start i = i + 1 Letter = Left(Cells(i, 1), 1) Loop BeginRow = i Do Until Letter <> start i = i + 1 Letter = Left(Cells(i, 1), 1) Loop EndRow = i  1 Debug.Print "Begin Row: "; BeginRow; " "; "End Row: "; EndRow End Sub
Please dont laugh at me or throw rotten fruit
Need help with this formula. this is the exact formula i used =sum($I$11:I$620)+countif($E$11:E$620,"S"). The problem i have is i do not want column "E" multiplied or divided or anything just counted. so for example i have in column "E" on rows 1115 3"S" and 1"F" and then in column"I" i have 3"3.00" and 1"3.00"listed as "s" and "f" respectively.
So the answer to the formula i am looking for is "9.00" it counts all "s" then goes over to column "I" and sums up all numbers that correspond to the "s".
Now since i have a "+" in there before counted it adds the number of "s" to the total giving me the answer "12.00" Is there a formula that i can input that just counts column "E"and designates the letter i choose then sums up column"I" under that letter?
So the answer to the formula i am looking for is "9.00" it counts all "s" then goes over to column "I" and sums up all numbers that correspond to the "s".
Now since i have a "+" in there before counted it adds the number of "s" to the total giving me the answer "12.00" Is there a formula that i can input that just counts column "E"and designates the letter i choose then sums up column"I" under that letter?
Good afternoon,
I need to check if a cell ends with {"a","b","c","d","e","S","t","v"}
and if it doesn't diplay a warning
this is what i have
C
104444s
=IF(RIGHT(C10,1)={"a","b","c","d","e","s","t","v"},"GOOD", "Must enter Suffix Letter")
I need to check if a cell ends with {"a","b","c","d","e","S","t","v"}
and if it doesn't diplay a warning
this is what i have
C
104444s
=IF(RIGHT(C10,1)={"a","b","c","d","e","s","t","v"},"GOOD", "Must enter Suffix Letter")
Fellow Forum Members,
I would be very grateful if someone can help me develop a macro that can search all of "Column C" for any cell that begins with the letter "M". After it finds a cell that begins with the letter "M" it inserts (in the cell directly above the "M" cell) custom TEXT contained in cell "E1". For example, Cells C4, C9, C15 all begin with the letter "M". And after the macro runs, Cells C3, C8 and C14 are automatically populated with text the macro grabs from cell "E1".
Any help will be greatly appreciated. Thanks.
I would be very grateful if someone can help me develop a macro that can search all of "Column C" for any cell that begins with the letter "M". After it finds a cell that begins with the letter "M" it inserts (in the cell directly above the "M" cell) custom TEXT contained in cell "E1". For example, Cells C4, C9, C15 all begin with the letter "M". And after the macro runs, Cells C3, C8 and C14 are automatically populated with text the macro grabs from cell "E1".
Any help will be greatly appreciated. Thanks.
Hello,
I've attached a sample spreadsheet that I'm hoping someone can help with. Basically I need to change a existing "letter" to the next corresponding "letter". All "letters" are to change EXCEPT the "letter A". There could be an upward of 2000 single part#'s that will require this change so I would need it to "loop". The sample spreadsheet will say it all. All help is most appreciated!
I've attached a sample spreadsheet that I'm hoping someone can help with. Basically I need to change a existing "letter" to the next corresponding "letter". All "letters" are to change EXCEPT the "letter A". There could be an upward of 2000 single part#'s that will require this change so I would need it to "loop". The sample spreadsheet will say it all. All help is most appreciated!
Hello,
My question is about determining whether or not a particular letter is uppercase.
I've written code that reads from a text file one character at a time. And I know that the following determines if each character is equal to the letter "a".
Code:
But I want to code an If statement that tells if the character is any uppercase letter. Like "A", "B", "C", etc. I know I could do the following:
Code:
But that seems like a lot of unnecessary typing. Could I possibly use a wildcard character and a VBA type of UCase to accomplish the task?
If so, how would I code that?
Thanks for any and all help!
My question is about determining whether or not a particular letter is uppercase.
I've written code that reads from a text file one character at a time. And I know that the following determines if each character is equal to the letter "a".
Code:
If Character = "a" Then
But I want to code an If statement that tells if the character is any uppercase letter. Like "A", "B", "C", etc. I know I could do the following:
Code:
If Character = "A" Or Character = "B" Or Character = "C" Then
But that seems like a lot of unnecessary typing. Could I possibly use a wildcard character and a VBA type of UCase to accomplish the task?
If so, how would I code that?
Thanks for any and all help!
Hi,
I have a string that I want to remove some text from;
11.2.B.9 XXXXXXXXXX  I want to remove the 2, the B, and the 9 separetly. The first number can be one or two digits, same with the second number, same with the third number. The letter is always a single letter. the text at the end is not consistent at all.
here is the function I have to pull out the "2"...the problem is it does not work with a two digit number: =MID(A1,FIND(".",A1,FIND(".",A1))+1,FIND(".",A1,FIND(".",A1,FIND(".",A1,FIND(".",A1))))2)
I can figure out the rest of the parses if I can get some help pulling out the 2.
thanks!
I have a string that I want to remove some text from;
11.2.B.9 XXXXXXXXXX  I want to remove the 2, the B, and the 9 separetly. The first number can be one or two digits, same with the second number, same with the third number. The letter is always a single letter. the text at the end is not consistent at all.
here is the function I have to pull out the "2"...the problem is it does not work with a two digit number: =MID(A1,FIND(".",A1,FIND(".",A1))+1,FIND(".",A1,FIND(".",A1,FIND(".",A1,FIND(".",A1))))2)
I can figure out the rest of the parses if I can get some help pulling out the 2.
thanks!
I have a worksheet that has rows of data, that are made up of blank cells or cells containing a single letter (see screenshot example). The cells I'm interested in are groups of 6 or 7 cells that contain the letters "d", "a" or "n".
I need to count how many of these groups of 6 and how many groups of 7 cells exist in a row. Note that often such a group is followed by cell containing a letter "U", these should be considered as a blank cell (and not counted).
There are also groups of cells containing the letter "v", these should not be counted as well.
Hope this makes sense
I need to count how many of these groups of 6 and how many groups of 7 cells exist in a row. Note that often such a group is followed by cell containing a letter "U", these should be considered as a blank cell (and not counted).
There are also groups of cells containing the letter "v", these should not be counted as well.
Hope this makes sense
Values are as follows:
Cell A1 is a calculated value between 1 and 26. In this case, it equals 19
Cell A2 is the letter "J"
In Row 19, Columns A through Z, I have one letter of the alphabet in each cell, in sequence, BUT I'm starting with the letter "S".
So, A19="S", B19="T" ...Y19="Q", Z19="R"
Here's what I'm trying to accomplish:
I want a formula to determine the intersecting column for the value in A2 in the row specified in A1.
MATCH(A2,$A$19:$Z$19) will work; however, I need to calculate the lookup_array value.
=CONCATENATE(ADDRESS(A1,1),":",ADDRESS(A1,26)) will produce the correct lookup array value, BUT...
Unfortunately, when I imbed the CONCATENATE function iside the MATCH function as the 2nd parameter, the resultant equation errors out.
Any help would be appreciated, thank you.
Cell A1 is a calculated value between 1 and 26. In this case, it equals 19
Cell A2 is the letter "J"
In Row 19, Columns A through Z, I have one letter of the alphabet in each cell, in sequence, BUT I'm starting with the letter "S".
So, A19="S", B19="T" ...Y19="Q", Z19="R"
Here's what I'm trying to accomplish:
I want a formula to determine the intersecting column for the value in A2 in the row specified in A1.
MATCH(A2,$A$19:$Z$19) will work; however, I need to calculate the lookup_array value.
=CONCATENATE(ADDRESS(A1,1),":",ADDRESS(A1,26)) will produce the correct lookup array value, BUT...
Unfortunately, when I imbed the CONCATENATE function iside the MATCH function as the 2nd parameter, the resultant equation errors out.
Any help would be appreciated, thank you.
I have a list of names (which will change) in column B "data work sheet". I'd like to assign a letter from colmun A to represent the cell that each name is entered (a=B1). I'd like to use that letter to identify the name to be used in the introduction of a generic message on "message work sheet". I'd like to place that letter in a location like C5 and have it reference the name.
Please advise the best way to do that and how to approach it. Help appreciated.
Example:
"DATA WORK SHEET"
................ A..............B
1................a..........MR SMITH
2.................b.........TOM SMITH
3.................c..........TOM L SMITH
Enter "a" into cell C5
"LETTER WORK SHEET"
Dear (data work sheet, C5) MR SMITH
Please advise the best way to do that and how to approach it. Help appreciated.
Example:
"DATA WORK SHEET"
................ A..............B
1................a..........MR SMITH
2.................b.........TOM SMITH
3.................c..........TOM L SMITH
Enter "a" into cell C5
"LETTER WORK SHEET"
Dear (data work sheet, C5) MR SMITH
I would like to combine an IF formula with a VLOOKUP.
The problem is I would like it to only look at the first Letter in the Cell.
If the Cell starts with an "F" then.... (F12562)
If the Cell starts with a "U" then..... (U2568)
if it makes a difference sometimes the code I'm inputting is
a Letter & 5 numbers, and sometimes a Letter & four numbers.
I must be missing something cause it can't be this frustrating.
The problem is I would like it to only look at the first Letter in the Cell.
If the Cell starts with an "F" then.... (F12562)
If the Cell starts with a "U" then..... (U2568)
if it makes a difference sometimes the code I'm inputting is
a Letter & 5 numbers, and sometimes a Letter & four numbers.
I must be missing something cause it can't be this frustrating.
Hi,
I have two columns, A and B respectively, that contain the following values:
Code:
How can I write the SUMIF worksheet function to search for everything in column B that contains the letter "E" and if it contains that letter, it will sum the value in column A?
Thanks...
Mike
I have two columns, A and B respectively, that contain the following values:
Code:
4 "E12" 1 "V2A1" 1 "Vm55" 2 "E12" 3 "E13" 1 "E411"
How can I write the SUMIF worksheet function to search for everything in column B that contains the letter "E" and if it contains that letter, it will sum the value in column A?
Thanks...
Mike
Hi,
I have two columns, A and B respectively, that contain the following values:
Code:
How can I write the SUMIF worksheet function to search for everything in column B that contains the letter "E" and if it contains that letter, it will sum the value in column A?
Thanks...
Mike
I have two columns, A and B respectively, that contain the following values:
Code:
4 "E12" 1 "V2A1" 1 "Vm55" 2 "E12" 3 "E13" 1 "E411"
How can I write the SUMIF worksheet function to search for everything in column B that contains the letter "E" and if it contains that letter, it will sum the value in column A?
Thanks...
Mike
This may be a simple formula, but I can't figure out one detail.
=IF(NOT(ISERROR(SEARCH("a",A1:A15))),"a","")
When using a test sheet to try out this formula, I have populated column A with various single letters of the alphabet. For example, A1 and A15 have the letter "a" in them. A2 and A14 have the letter "b", (and so on down the column with different letters). The formula is in column B, staring with B1:B15, and it worksshowing B1 and B15 with the letter "a". What I want to do is sort these letters alphabetically in order, i.e. B1=a, and B2=a, B3=b, and B4=b. Is it possible with this formula, or is there another simple way of completing this function. Also, the way I have the formula, I know it is searching for that one letter "a". I have put the formula in different columns to search for individual letters of the alphabet. I would like to write one formula, but I haven't yet figured that out.
Thanks in advance, you guys are great.
=IF(NOT(ISERROR(SEARCH("a",A1:A15))),"a","")
When using a test sheet to try out this formula, I have populated column A with various single letters of the alphabet. For example, A1 and A15 have the letter "a" in them. A2 and A14 have the letter "b", (and so on down the column with different letters). The formula is in column B, staring with B1:B15, and it worksshowing B1 and B15 with the letter "a". What I want to do is sort these letters alphabetically in order, i.e. B1=a, and B2=a, B3=b, and B4=b. Is it possible with this formula, or is there another simple way of completing this function. Also, the way I have the formula, I know it is searching for that one letter "a". I have put the formula in different columns to search for individual letters of the alphabet. I would like to write one formula, but I haven't yet figured that out.
Thanks in advance, you guys are great.