Need Help On A Challenging Formula

[ranjith2523]

Hi Friends,

I was just breaking my head for the past two days to build a formula and I would like to ask sorry to you all since I know its a kind of huge help but I don't have a choice now.

I tried to apply a formula in from cell C2 like IF(OR(ISNUMBER(SEARCH("PW" ... but it didn't work as per the expectation.

Product	Desc Txt	Short Notes	100 ch description
HM1T3PE	HPE 1Y PW OEM Prts Log DL360 Gen10 SVC	PW	HPE 1 Year Post Warranty OEM Parts Logistics DL360 Gen10 Service
HM1T4PE	HPE 1Y PW OEM PrtsLog wDMR DL360 G10 SVC	PW-Edu	Aruba 1 Year Foundation Care Next Business Day Exchange Education/Retail AP-503H Unified Service
HM1T5PE	HPE 1Y PW OEMPrtsLog wCDMR DL360 G10 SVC	PW-Edu/Vol	Aruba 1 Year Foundation Care Next Business Day Exchange Volume T1 AP-503H Unified Service
HS3B1E	HPE 3Y PC NBD Apollo Rack CDU SVC	PC-Edu	Aruba 1 Year Foundation Care Next Business Day Exchange Education/Retail AP-503H Unified Service
HS3B1E	HPE 3Y PC NBD Apollo Rack CDU SVC	PC-Edu/Vol	Aruba 1 Year Foundation Care Next Business Day Exchange Volume T1 AP-503H Unified Service
HS3B1E	HPE 3Y PC NBD Apollo Rack CDU SVC	PC	HPE 3 Year Proactive Care Next Business Day Apollo Rack CDU Service
HU4F5E	Aruba 1Y FC NBD Exch EDU/R AP-503HUniSVC	FC-Edu	Aruba 1 Year Foundation Care Next Business Day Exchange Education/Retail AP-503H Unified Service
HU4F6E	Aruba 1Y FC NBD Exch VolT1 AP-503HUniSVC	FC-Edu/Vol	Aruba 1 Year Foundation Care Next Business Day Exchange Volume T1 AP-503H Unified Service
HS3B4E	HPE 3Y FC CTR Apollo Rack CDU SVC	FC	HPE 3 Year Foundation Care Call-To-Repair Apollo Rack CDU Service
HA9J1E	HPE Synergy Basic 1st Frame Deploy SVC	Edu	Aruba 1 Year Foundation Care Next Business Day Exchange Education/Retail AP-503H Unified Service
HA9J2E	HPE Synergy Basic Add Frame Deploy SVC	Edu/Vol	Aruba 1 Year Foundation Care Next Business Day Exchange Volume T1 AP-503H Unified Service
HA9J1E	HPE Synergy Basic 1st Frame Deploy SVC		HPE Synergy Basic First Frame Deployment Service
HA9J2E	HPE Synergy Basic Add Frame Deploy SVC		HPE Synergy Basic Add Frame Deployment Service

Scenario 1 - In A Column, if Product Number ends with PE (E.g., HM1T3PE) then in C column the short note value should be written as "PW", additionally, in D column if the text contains "Edu" then the value should be written as "PW-Edu" or in D column if the text contains "Vol" then the value should be written as "PW-Edu/Vol"

Scenario 2 - In A Column, if Product Number DOES NOT end with PE (E.g., HM1T3PE) and in B column text contains "Y P" then the short note value should be written as "PC", additionally, in D column if the text contains "Edu" then the value should be written as "PC-Edu" or in D column if the text contains "Vol" then the value should be written as "PC-Edu/Vol"

Scenario 3 - In A Column, if Product Number DOES NOT end with PE (E.g., HM1T3PE) and in B column text contains "Y F" then the short note value should be written as "FC", additionally, in D column if the text contains "Edu" then the value should be written as "FC-Edu" or in D column if the text contains "Vol" then the value should be written as "FC-Edu/Vol"

Scenario 4 - In A Column, if Product Number DOES NOT end with PE (E.g., HM1T3PE) and in B column text DOES NOT contain "Y P" or "Y F" but in D column if the text contains "Edu" then the value should be written as "Edu" or in D column if the text contains "Vol" then the value should be written as "Vol"

Scenario 5 - If none of the above scenarios matched then the C Column short note value should be "Blank"

Thanks for all your help in advance.

Regards,
Ranjith

 

[ranjith2523]

How about:

=IF(RIGHT(A2,2)="PE","PW"&LOOKUP(2,1/SEARCH({"","Edu","Vol"},D2),{"","-Edu","-Edu/Vol"}),SUBSTITUTE(TRIM(LOOKUP(2,1/FIND({"","Y P","Y F"},B2),{"","PC ","FC "})&LOOKUP(2,1/SEARCH({"","Edu","Vol"},D2),{""," Edu"," Edu/Vol"}))," ","-"))

Hi Rory,

I am continuously thanking peoples here and I am really happy about that.

Thank you so much for your formula. It is perfectly working as expected. Thanks a lot for your time and help.

If you don't mind, could you please break up the formula and explain to me how it works since I really wanted to learn.

Regards,
Ranjith

 

[ranjith2523]

They all work, you have at least 3 other different suggestions.

My first suggestion is just a longer version of what you started doing so nothing that I can break down really.

My second suggestion will only work in the latest versions of excel, this is why you see the #NAME? error, your version does not have the TEXTJOIN function.

The suggestions from @Eric W and @RoryA will both work in your version of excel.

Thanks Jacob, Now, I understand that the TEXTJOIN function will work only on the latest version of excel.

Yes, Jason, Eric and Rory's formula working for me and i requested them to explain the formula to know how it work.

I hope they will help for me to learn.

Thank you again Jacob.

 

[Bo_Ry]

Others 2 formula

=SUBSTITUTE(TRIM(IF(RIGHT(A2,2)="PE","PW",LOOKUP(99,FIND({"","Y P","Y F"},B2),{"","PC","PF"}))&LOOKUP(0,-FIND({"","Edu","Vol"},D2),{""," Edu"," Edu/Vol"}))," ","-")

=SUBSTITUTE(TRIM(LOOKUP(0,-SEARCH({"","PW","PC","FC"},B2),{"","PW","PC","FC"})&LOOKUP(0,-FIND({"","Edu","Vol"},D2),{""," Edu"," Edu/Vol"}))," ","-")

 

[RoryA]

In line with Jason's approach, I can actually shorten that a little to:

=SUBSTITUTE(TRIM(IF(RIGHT(A2,2)="PE","PW ",LOOKUP(2,1/FIND({"","Y P","Y F"},B2),{"","PC ","FC "}))&LOOKUP(2,1/SEARCH({"","Edu","Vol"},D2),{""," Edu"," Edu/Vol"}))," ","-")

To break that down a bit:

LOOKUP(2,1/​

FIND({"","Y P","Y F"},B2)​

,​

{"","PC ","FC "}​

)

the red part looks for an array of text values in B2, and returns an array with either the position that the value was found, or an error. Dividing one by that array will return an array of fractions or errors. The lookup function will then find the last number value in that array (since it ignores errors, and 2 will be bigger than any fraction with 1 as the numerator), and return the corresponding text from the blue result array.
This technique is repeated with the Edu/Vol searches.

You'll note that the first return values end with a space and the second array values that are appended begin with a space. This means that you could end up with double spaces if the first and second parts both return a value, so TRIM removes any leading or trailing spaces and also replaces any double spaces with a single space. The SUBSTITUTE function then replaces any remaining spaces with a - character.

You should try using the formula evaluation tool with each cell to see how each part works. ​

 

[Eric W]

Hi Eric,

Thank you so much for your formula.. It is perfectly working as expected. Thanks a lot for your time and help.

If you don't mind, could you please break up the formula and explain me how it works since i really wanted to learn.

Regards,
Ranjith

I'm going to defer explaining how my formula works. The other suggestions are both shorter and simpler than what I came up with, so I'd recommend going with one of those. Interesting how many ways there are to do things in Excel.

Good luck!

 

[ranjith2523]

ooks for an array of text value

Others 2 formula

=SUBSTITUTE(TRIM(IF(RIGHT(A2,2)="PE","PW",LOOKUP(99,FIND({"","Y P","Y F"},B2),{"","PC","PF"}))&LOOKUP(0,-FIND({"","Edu","Vol"},D2),{""," Edu"," Edu/Vol"}))," ","-")

=SUBSTITUTE(TRIM(LOOKUP(0,-SEARCH({"","PW","PC","FC"},B2),{"","PW","PC","FC"})&LOOKUP(0,-FIND({"","Edu","Vol"},D2),{""," Edu"," Edu/Vol"}))," ","-")

Not sure how to say Thank you for all your help. Both Formulas are working as expected. Thank You !!!

 

[ranjith2523]

Something like

=SUBSTITUTE(TRIM(IF(ISNUMBER(SEARCH("PE",A2)),"PW ",IF(ISNUMBER(SEARCH("Y P",B2)),"PC ",IF(ISNUMBER(SEARCH("Y F",B2)),"FC ","")))&IF(ISNUMBER(SEARCH("Edu",D2)),"Edu",IF(ISNUMBER(SEARCH("Vol",D2)),"Edu/Vol","")))," ","-")

Hi Jason,

Sorry to bother you again !!!

Actually, your formula is easy for me to understand.

I am clear with the ISNUMBER and SEARCH functionality here but please explain why the substitute and trim formula needed here and how it actually works.

Thank you
Ranjith

 

[jasonb75]

Trim removes the space after the first part when the second part is empty.

Substitute replaces the space between the 2 parts with -

Without those you would get PW- instead of PW when the second part is empty.

 

[ranjith2523]

Trim removes the space after the first part when the second part is empty.

Substitute replaces the space between the 2 parts with -

Without those you would get PW- instead of PW when the second part is empty.

Thank you Jason.. It's really very clear for me now. I just wrote the formula on my own now. Thanks again for your patience and help !!!