• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 135
  • Last Modified:

Adding a new condition to an existing Excel formula

Attached is a sample spreadsheet implementing a formula (in Column J). I would like this formula revised. Column L shows the desired results I am looking for, with the new criteria that needs to be factored in highlighted in yellow (in row 4).  I've outlined the background details of the existing formula below and included the updated criteria change at the end.

CRITERIA OF EXISTING FORMULA:
I would like Column J to concatenate different columns, including a specific string within one column, based on various criteria outlined below:

If D = "CAD" or "TREP", AND H = "VOL 1" or "VOL 7" or "VOL 12" then Column J to concatenate as follows:
J = B + D + C [first word after closing bracket OR first second word in string if no closing bracket] + F
Otherwise, Column J to concatenate as follows:
J = B + D + E + F

CURRENT FORMULA:
=IF(AND(OR(H2="VOL 1",H2="VOL 7",H2="VOL 12"),OR(D2="CAD",D2="TREP")),B2&"-"&D2&"-"&LEFT(TRIM(IF(FIND(")",C2)<>LEN(C2),MID(C2,FIND(")",C2)+1,100),MID(C2,FIND(" ",C2)+1,100))),FIND(" ",TRIM(IF(FIND(")",C2)<>LEN(C2),MID(C2,FIND(")",C2)+1,100),MID(C2,FIND(" ",C2)+1,100))))-1)&IF(F2<>"","-"&F2,""),B2&"-"&D2&IF(E2&F2="","","-"&E2&F2))

CRITERIA OF UPDATED FORMULA:
As above, but including the following new conditions:
If Columns G and Column H is blank, and Column I is not blank, then  J = B + D + E + F
Otherwise:
J = B + D + C [first word after closing bracket OR first second word in string if no closing bracket] + F

Thanks,
Andrea
EE-Sample-Updated.xlsx
0
Andreamary
Asked:
Andreamary
  • 3
  • 2
2 Solutions
 
Glenn RayExcel VBA DeveloperCommented:
There are a couple of issues:
1) You refer to column H twice - once for the "Section" (actually in column H) and the "VOL" numbers (actually in column K).  The formula in the workbook refers to column K for the volume numbers; is that correct to continue?
2) The formula in the workbook is not the same as your example (does not contain the OR clause for the volume numbers 1,7,12).
0
 
AndreamaryAuthor Commented:
Sorry, Glenn!! I should have taken more time to review everything before posting!

I believe I've fixed the spreadsheet, and I've reuploaded it. Here is the current (fixed) formula in the spreadsheet:

=IF(AND(OR(K2="VOL 1",K2="VOL 7",K2="VOL 12"),OR(D2="CAD",D2="TREP")),B2&"-"&D2&"-"&LEFT(TRIM(IF(FIND(")",C2)<>LEN(C2),MID(C2,FIND(")",C2)+1,100),MID(C2,FIND(" ",C2)+1,100))),FIND(" ",TRIM(IF(FIND(")",C2)<>LEN(C2),MID(C2,FIND(")",C2)+1,100),MID(C2,FIND(" ",C2)+1,100))))-1)&IF(F2<>"","-"&F2,""),B2&"-"&D2&IF(E2&F2="","","-"&E2&F2))
EE-Sample-Fixed.xlsx
0
 
Glenn RayExcel VBA DeveloperCommented:
This modification to your formula seems to work:
=IF(AND(NOT(AND(G2&H2="",I2<>"")),OR(K2="VOL 1",K2="VOL 7",K2="VOL 12"),OR(D2="CAD",D2="TREP")),B2&"-"&D2&"-"&LEFT(TRIM(IF(FIND(")",C2)<>LEN(C2),MID(C2,FIND(")",C2)+1,100),MID(C2,FIND(" ",C2)+1,100))),FIND(" ",TRIM(IF(FIND(")",C2)<>LEN(C2),MID(C2,FIND(")",C2)+1,100),MID(C2,FIND(" ",C2)+1,100))))-1)&IF(F2<>"","-"&F2,""),B2&"-"&D2&IF(E2&F2="","","-"&E2&F2))

I highlighted the updated portion.  Since your condition of -
Columns G and Column H is blank, and Column I is not blank, then  J = B + D + E + F
is the same as the FALSE result of the original formula, its opposite
NOT(AND(G2&H2="",I2<>""))
should follow the TRUE result.

-Glenn
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rory ArchibaldCommented:
FWIW, a little shorter:

=IF(AND(OR(G2&H2<>"",I2=""),OR(K2={"VOL 1","VOL 7","VOL 12"}),OR(D2={"CAD","TREP"})),B2&"-"&D2&"-"&LEFT(TRIM(MID(C2,FIND(IF(FIND(")",C2)<>LEN(C2),")"," "),C2)+1,100)),FIND(" ",TRIM(MID(C2,FIND(IF(FIND(")",C2)<>LEN(C2),")"," "),C2)+1,100)))-1)&IF(F2<>"","-"&F2,""),B2&"-"&D2&IF(E2&F2="","","-"&E2&F2))
0
 
Glenn RayExcel VBA DeveloperCommented:
Rory, I did not know you could do that (i.e., compare a value to an array of values)!  That's my cool knowledge for the week.  :-)

And darnit, I don't know why I didn't figure out the first OR clause like yours; it's much simpler and easier to follow.
0
 
AndreamaryAuthor Commented:
Both worked like a charm...thanks so much!
Andrea
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now