| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Nested Function
I'm attempting to assign contract descriptions based on multiple elements of
the contracts. All data is in the same worksheet / same tab. I could use some help developing a series of nested functions that compare data and pull the correct description. Here's what I've got to work with: Formula Objective: Search the combination of Contract RCCC, PEC, and BAG codes and select the applicable description if all elements are TRUE. If any of the elements are FALSE, enter €śNot GWOT related€ť. Formula Conditions: If the left 2 characters of RCCC Code (Column R) match anything in Column C, Rows 2-33 then continue, if not, then match is FALSE. If 3rd & 4th characters of RCCC Code (Column R) match the first two characters in Column D, then continue, if not, then match is FALSE. If PEC Code (Column S) matches PEC Code (either Column F or G) then continue, if not, then match is FALSE. If BAG Code (Column T) matches BAG Code (Column H) then continue, if not, then match is FALSE. Contract Codes: RCCC Code Column R; Rows 2-4804, inclusive PEC Code Column S; Rows 2-4804, inclusive BAG Code Column T; Rows 2-4804, inclusive Criteria: 6 Digit RCCC Code Column A; Rows 2-33, inclusive RCCC Digits 1 & 2 Column C; Rows 2-33, inclusive RCCC Digits 3 to 6 Column D; Rows 2-33, inclusive PEC Column F or Column G; Rows 2-33, inclusive BAG Column H; Rows 2-33, inclusive Desired Description Column E; Rows 2-33, inclusive |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Nested Function
Hello Russ,
The detailed description provided by you was excellent... Look for my answers below in the rows without an "" (Assuming you are working on Row 2) Enter this in U2 =MATCH("*"&LEFT(R2,2)&"*",C2:C33,0) Formula Conditions: If the left 2 characters of RCCC Code (Column R) match anything in Column C, Rows 2-33 then continue, if not, then match is FALSE. Enter this in V2 =ISNA(MATCH("*"&LEFT(R2,2)&"*",C2:C33,0)) If 3rd & 4th characters of RCCC Code (Column R) match the first two characters in Column D, then continue, if not, then match is FALSE. Enter this in W2 =ISNA(MATCH(MID(R2,3,2))&"*",D2:D33,0)) If you just want to look in D2 then replace D2:D33 with D2 If PEC Code (Column S) matches PEC Code (either Column F or G) then continue, if not, then match is FALSE. Enter this in X2 =OR(ISNA(MATCH(S2,F2:F33,0)),ISNA(MATCH(S2,G2:G33, 0))) If BAG Code (Column T) matches BAG Code (Column H) then continue, if not, then match is FALSE. Enter this in Y2 =ISNA(MATCH(T2,H2:H33,0)) Last Step Then put this in the Cell E2 =IF(SUMPRODUCT(--(V2:Y2))=4,INDIRECT(U2),"Not GWOT related") Copy these formulae down to the end of your dataset Hope I have not made a mistake in copying the formula here... I hope you got the idea... You can always combine all these formulae into one cell Let me know how it goes -- Always provide your feedback... "Russ" wrote: I'm attempting to assign contract descriptions based on multiple elements of the contracts. All data is in the same worksheet / same tab. I could use some help developing a series of nested functions that compare data and pull the correct description. Here's what I've got to work with: Formula Objective: Search the combination of Contract RCCC, PEC, and BAG codes and select the applicable description if all elements are TRUE. If any of the elements are FALSE, enter €śNot GWOT related€ť. Formula Conditions: If the left 2 characters of RCCC Code (Column R) match anything in Column C, Rows 2-33 then continue, if not, then match is FALSE. If 3rd & 4th characters of RCCC Code (Column R) match the first two characters in Column D, then continue, if not, then match is FALSE. If PEC Code (Column S) matches PEC Code (either Column F or G) then continue, if not, then match is FALSE. If BAG Code (Column T) matches BAG Code (Column H) then continue, if not, then match is FALSE. Contract Codes: RCCC Code Column R; Rows 2-4804, inclusive PEC Code Column S; Rows 2-4804, inclusive BAG Code Column T; Rows 2-4804, inclusive Criteria: 6 Digit RCCC Code Column A; Rows 2-33, inclusive RCCC Digits 1 & 2 Column C; Rows 2-33, inclusive RCCC Digits 3 to 6 Column D; Rows 2-33, inclusive PEC Column F or Column G; Rows 2-33, inclusive BAG Column H; Rows 2-33, inclusive Desired Description Column E; Rows 2-33, inclusive |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Nested Function
Hi Sheeloo,
I appreciate your advise; I was surprised at how quickly I received an answer. Could you explain what each of your formulas do? I'm not familiar with some of the variables you used and I don't quite understand how they relate to the array's being addressed. Also, on the last step you noted that I should put that function in cell E2. That's where my first RCCC description is located; I was hoping to put the final nested function on the right side of my spreadsheet (which would be column AK) and have it pull the RCCC description from E2:E33. Again, I thank you for your help. You've got me a lot closer than I've been able to do on my own. Russ "Sheeloo" wrote: Hello Russ, The detailed description provided by you was excellent... Look for my answers below in the rows without an "" (Assuming you are working on Row 2) Enter this in U2 =MATCH("*"&LEFT(R2,2)&"*",C2:C33,0) Formula Conditions: If the left 2 characters of RCCC Code (Column R) match anything in Column C, Rows 2-33 then continue, if not, then match is FALSE. Enter this in V2 =ISNA(MATCH("*"&LEFT(R2,2)&"*",C2:C33,0)) If 3rd & 4th characters of RCCC Code (Column R) match the first two characters in Column D, then continue, if not, then match is FALSE. Enter this in W2 =ISNA(MATCH(MID(R2,3,2))&"*",D2:D33,0)) If you just want to look in D2 then replace D2:D33 with D2 If PEC Code (Column S) matches PEC Code (either Column F or G) then continue, if not, then match is FALSE. Enter this in X2 =OR(ISNA(MATCH(S2,F2:F33,0)),ISNA(MATCH(S2,G2:G33, 0))) If BAG Code (Column T) matches BAG Code (Column H) then continue, if not, then match is FALSE. Enter this in Y2 =ISNA(MATCH(T2,H2:H33,0)) Last Step Then put this in the Cell E2 =IF(SUMPRODUCT(--(V2:Y2))=4,INDIRECT(U2),"Not GWOT related") Copy these formulae down to the end of your dataset Hope I have not made a mistake in copying the formula here... I hope you got the idea... You can always combine all these formulae into one cell Let me know how it goes -- Always provide your feedback... "Russ" wrote: I'm attempting to assign contract descriptions based on multiple elements of the contracts. All data is in the same worksheet / same tab. I could use some help developing a series of nested functions that compare data and pull the correct description. Here's what I've got to work with: Formula Objective: Search the combination of Contract RCCC, PEC, and BAG codes and select the applicable description if all elements are TRUE. If any of the elements are FALSE, enter €śNot GWOT related€ť. Formula Conditions: If the left 2 characters of RCCC Code (Column R) match anything in Column C, Rows 2-33 then continue, if not, then match is FALSE. If 3rd & 4th characters of RCCC Code (Column R) match the first two characters in Column D, then continue, if not, then match is FALSE. If PEC Code (Column S) matches PEC Code (either Column F or G) then continue, if not, then match is FALSE. If BAG Code (Column T) matches BAG Code (Column H) then continue, if not, then match is FALSE. Contract Codes: RCCC Code Column R; Rows 2-4804, inclusive PEC Code Column S; Rows 2-4804, inclusive BAG Code Column T; Rows 2-4804, inclusive Criteria: 6 Digit RCCC Code Column A; Rows 2-33, inclusive RCCC Digits 1 & 2 Column C; Rows 2-33, inclusive RCCC Digits 3 to 6 Column D; Rows 2-33, inclusive PEC Column F or Column G; Rows 2-33, inclusive BAG Column H; Rows 2-33, inclusive Desired Description Column E; Rows 2-33, inclusive |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Nested Function
Thanks for your feedback.
Here is the summary... AK2: =IF(SUMPRODUCT(--(V2:Y2))=0,INDIRECT("E"&U2),"Not GWOT related") U2: =MATCH("*"&LEFT(R2,2)&"*",C2:C33,0) V2: =ISNA(MATCH("*"&LEFT(R2,2)&"*",C2:C33,0)) W2: =ISNA(MATCH(MID(R2,3,2))&"*",D2:D33,0)) X2: =OR(ISNA(MATCH(S2,F2:F33,0)),ISNA(MATCH(S2,G2:G33, 0))) Y2: =ISNA(MATCH(T2,H2:H33,0)) Formulae explained below are different from the above for your understanding.. Look for my answers below in the rows without an " " If you are unable to make it work, then pl. send the file to me and I will put in the formulae You can put it in AK2. Also I made a mistake... In the formula =IF(SUMPRODUCT(--(V2:Y2))=4,INDIRECT(U2),"Not GWOT related") replace INDIRECT(U2) by INDIRECT("E"&U2) as U2 has the row no. where a match occurs to get... =IF(SUMPRODUCT(--(V2:Y2))=4,INDIRECT("E"&U2),"Not GWOT related") Hope it works all the way... (Assuming you are working on Row 2) Enter this in U2 =MATCH("*"&LEFT(R2,2)&"*",C2:C33,0) Left(R2,2) will extract first two characters from R2, say AB Then MATCH will look for *AB* in the range C2:C33 and return the row number where match occurs (AB is found anywhere in the range) This will be used to find the corresponding entry in Col E if all conditions are met If no match is found it will return #N/A. Formula Conditions: If the left 2 characters of RCCC Code (Column R) match anything in Column C, Rows 2-33 then continue, if not, then match is FALSE. Enter this in V2 =ISNA(MATCH("*"&LEFT(R2,2)&"*",C2:C33,0)) This will retrun TRUE or FALSE (when match is not found and #N/A is returned. ISNA() checks for #N/A. **I forgot to put a NOT() around it to get TRUE so use =NOT(ISNA(MATCH("*"&LEFT(R2,2)&"*",C2:C33,0))) If 3rd & 4th characters of RCCC Code (Column R) match the first two characters in Column D, then continue, if not, then match is FALSE. Enter this in W2 =ISNA(MATCH(MID(R2,3,2))&"*",D2:D33,0)) **Use =NOT(ISNA(MATCH(MID(R2,3,2))&"*",D2:D33,0))) If you just want to look in D2 then replace D2:D33 with D2 MID extracts two characters from R2 starting at position 3 (which means 3rd and 4th characters) If PEC Code (Column S) matches PEC Code (either Column F or G) then continue, if not, then match is FALSE. Enter this in X2 =OR(ISNA(MATCH(S2,F2:F33,0)),ISNA(MATCH(S2,G2:G33, 0))) True if match is found either in F or G cols. OR will be FALSE only if both match return FALSE **USe =NOT(OR(ISNA(MATCH(S2,F2:F33,0)),ISNA(MATCH(S2,G2: G33,0)))) If BAG Code (Column T) matches BAG Code (Column H) then continue, if not, then match is FALSE. Enter this in Y2 =ISNA(MATCH(T2,H2:H33,0)) **Use =NOT(ISNA(MATCH(T2,H2:H33,0))) Last Step Then put this in the Cell E2 =IF(SUMPRODUCT(--(V2:Y2))=4,INDIRECT(U2),"Not GWOT related") SUMPRODUCT sums the number of TRUE in the range V2 to Y2. If all four have TRUE then only you find for the match in Col E. Copy these formulae down to the end of your dataset Hope I have not made a mistake in copying the formula here... I hope you got the idea... You can always combine all these formulae into one cell Let me know how it goes |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Nested Function
I think...
Having read the thread and reread your initial post from yesterday that the following is the formula you need to use in AK2 and copy through to AK4804. =INDEX($E$2:$E$33,MATCH(1,(LEFT(R2,2)=$C$2:$C$33)* (MID(R2,3,2)=LEFT($D $2:$D$33,2))*((S2=$F$2:$F$33)+(S2=$G$2:$G$33))*(T2 =$H$2:$H$33),0)) This is an *array* formula. In AK2 commit with Shift+Ctrl+Enter. Then copy down. HTH Kostis Vezerides On Oct 15, 7:43*pm, Russ wrote: I'm attempting to assign contract descriptions based on multiple elements of the contracts. *All data is in the same worksheet / same tab. *I could use some help developing a series of nested functions that compare data and pull the correct description. *Here's what I've got to work with: Formula Objective: Search the combination of Contract RCCC, PEC, and BAG codes and select the applicable description if all elements are TRUE. *If any of the elements are FALSE, enter “Not GWOT related”. Formula Conditions: If the left 2 characters of RCCC Code (Column R) match anything in Column C, Rows 2-33 then continue, if not, then match is FALSE. If 3rd & 4th characters of RCCC Code (Column R) match the first two characters in Column D, then continue, if not, then match is FALSE. * If PEC Code (Column S) matches PEC Code (either Column F or G) then continue, if not, then match is FALSE. If BAG Code (Column T) matches BAG Code (Column H) then continue, if not, then match is FALSE. Contract Codes: RCCC Code * * * Column R; Rows 2-4804, inclusive PEC Code * * * * * * * *Column S; Rows 2-4804, inclusive BAG Code * * * * * * * *Column T; Rows 2-4804, inclusive Criteria: 6 Digit RCCC Code * * * * * * * Column A; Rows 2-33, inclusive RCCC Digits 1 & 2 * * * * * Column C; Rows 2-33, inclusive RCCC Digits 3 to 6 * * * * * * *Column D; Rows 2-33, inclusive PEC * * * * * * * * * * Column F or Column G; Rows 2-33, inclusive BAG * * * * * * * * * * Column H; Rows 2-33, inclusive Desired Description * * * * * * Column E; Rows 2-33, inclusive |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple nested If statements | Excel Worksheet Functions | |||
| Multiple (Nested) IF | Excel Discussion (Misc queries) | |||
| Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
| multiple nested IF statements | Excel Worksheet Functions | |||
| Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions | |||
Linear Mode
