Need Help On A Challenging Formula

R

ranjith2523

Board Regular
Joined
Apr 29, 2014
Messages
137
Office Version
  1. 365
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.


ProductDesc TxtShort Notes100 ch description
HM1T3PEHPE 1Y PW OEM Prts Log DL360 Gen10 SVCPWHPE 1 Year Post Warranty OEM Parts Logistics DL360 Gen10 Service
HM1T4PEHPE 1Y PW OEM PrtsLog wDMR DL360 G10 SVCPW-EduAruba 1 Year Foundation Care Next Business Day Exchange Education/Retail AP-503H Unified Service
HM1T5PEHPE 1Y PW OEMPrtsLog wCDMR DL360 G10 SVCPW-Edu/VolAruba 1 Year Foundation Care Next Business Day Exchange Volume T1 AP-503H Unified Service
HS3B1EHPE 3Y PC NBD Apollo Rack CDU SVCPC-EduAruba 1 Year Foundation Care Next Business Day Exchange Education/Retail AP-503H Unified Service
HS3B1EHPE 3Y PC NBD Apollo Rack CDU SVCPC-Edu/VolAruba 1 Year Foundation Care Next Business Day Exchange Volume T1 AP-503H Unified Service
HS3B1EHPE 3Y PC NBD Apollo Rack CDU SVCPCHPE 3 Year Proactive Care Next Business Day Apollo Rack CDU Service
HU4F5EAruba 1Y FC NBD Exch EDU/R AP-503HUniSVCFC-EduAruba 1 Year Foundation Care Next Business Day Exchange Education/Retail AP-503H Unified Service
HU4F6EAruba 1Y FC NBD Exch VolT1 AP-503HUniSVCFC-Edu/VolAruba 1 Year Foundation Care Next Business Day Exchange Volume T1 AP-503H Unified Service
HS3B4EHPE 3Y FC CTR Apollo Rack CDU SVCFCHPE 3 Year Foundation Care Call-To-Repair Apollo Rack CDU Service
HA9J1EHPE Synergy Basic 1st Frame Deploy SVCEduAruba 1 Year Foundation Care Next Business Day Exchange Education/Retail AP-503H Unified Service
HA9J2EHPE Synergy Basic Add Frame Deploy SVCEdu/VolAruba 1 Year Foundation Care Next Business Day Exchange Volume T1 AP-503H Unified Service
HA9J1EHPE Synergy Basic 1st Frame Deploy SVCHPE Synergy Basic First Frame Deployment Service
HA9J2EHPE Synergy Basic Add Frame Deploy SVCHPE 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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
ranjith2523 said:
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.


ProductDesc TxtShort Notes100 ch description
HM1T3PEHPE 1Y PW OEM Prts Log DL360 Gen10 SVCPWHPE 1 Year Post Warranty OEM Parts Logistics DL360 Gen10 Service
HM1T4PEHPE 1Y PW OEM PrtsLog wDMR DL360 G10 SVCPW-EduAruba 1 Year Foundation Care Next Business Day Exchange Education/Retail AP-503H Unified Service
HM1T5PEHPE 1Y PW OEMPrtsLog wCDMR DL360 G10 SVCPW-Edu/VolAruba 1 Year Foundation Care Next Business Day Exchange Volume T1 AP-503H Unified Service
HS3B1EHPE 3Y PC NBD Apollo Rack CDU SVCPC-EduAruba 1 Year Foundation Care Next Business Day Exchange Education/Retail AP-503H Unified Service
HS3B1EHPE 3Y PC NBD Apollo Rack CDU SVCPC-Edu/VolAruba 1 Year Foundation Care Next Business Day Exchange Volume T1 AP-503H Unified Service
HS3B1EHPE 3Y PC NBD Apollo Rack CDU SVCPCHPE 3 Year Proactive Care Next Business Day Apollo Rack CDU Service
HU4F5EAruba 1Y FC NBD Exch EDU/R AP-503HUniSVCFC-EduAruba 1 Year Foundation Care Next Business Day Exchange Education/Retail AP-503H Unified Service
HU4F6EAruba 1Y FC NBD Exch VolT1 AP-503HUniSVCFC-Edu/VolAruba 1 Year Foundation Care Next Business Day Exchange Volume T1 AP-503H Unified Service
HS3B4EHPE 3Y FC CTR Apollo Rack CDU SVCFCHPE 3 Year Foundation Care Call-To-Repair Apollo Rack CDU Service
HA9J1EHPE Synergy Basic 1st Frame Deploy SVCEduAruba 1 Year Foundation Care Next Business Day Exchange Education/Retail AP-503H Unified Service
HA9J2EHPE Synergy Basic Add Frame Deploy SVCEdu/VolAruba 1 Year Foundation Care Next Business Day Exchange Volume T1 AP-503H Unified Service
HA9J1EHPE Synergy Basic 1st Frame Deploy SVCHPE Synergy Basic First Frame Deployment Service
HA9J2EHPE Synergy Basic Add Frame Deploy SVCHPE 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
Click to expand...

For first scenario =IF(AND(ISNUMBER(SEARCH("PE",A4,1))=TRUE,ISNUMBER(SEARCH("Edu",D4,1)))=TRUE,"PW-Edu",IF(ISNUMBER(SEARCH("Vol",D4,1))=TRUE,"PW-Vol","PW")) i wrote this formula and it is working as expected but i need a single formula which covers all scenarios.. Hope this formula give you some idea.
 
Upvote 0
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","")))," ","-")
 
Upvote 0
Try:

Book1
ABCD
1ProductDesc TxtShort Notes100 ch description
2HM1T3PEHPE 1Y PW OEM Prts Log DL360 Gen10 SVCPWHPE 1 Year Post Warranty OEM Parts Logistics DL360 Gen10 Service
3HM1T4PEHPE 1Y PW OEM PrtsLog wDMR DL360 G10 SVCPW-EduAruba 1 Year Foundation Care Next Business Day Exchange Education/Retail AP-503H Unified Service
4HM1T5PEHPE 1Y PW OEMPrtsLog wCDMR DL360 G10 SVCPW-Edu/VolAruba 1 Year Foundation Care Next Business Day Exchange Volume T1 AP-503H Unified Service
5HS3B1EHPE 3Y PC NBD Apollo Rack CDU SVCPC-EduAruba 1 Year Foundation Care Next Business Day Exchange Education/Retail AP-503H Unified Service
6HS3B1EHPE 3Y PC NBD Apollo Rack CDU SVCPC-Edu/VolAruba 1 Year Foundation Care Next Business Day Exchange Volume T1 AP-503H Unified Service
7HS3B1EHPE 3Y PC NBD Apollo Rack CDU SVCPCHPE 3 Year Proactive Care Next Business Day Apollo Rack CDU Service
8HU4F5EAruba 1Y FC NBD Exch EDU/R AP-503HUniSVCFC-EduAruba 1 Year Foundation Care Next Business Day Exchange Education/Retail AP-503H Unified Service
9HU4F6EAruba 1Y FC NBD Exch VolT1 AP-503HUniSVCFC-Edu/VolAruba 1 Year Foundation Care Next Business Day Exchange Volume T1 AP-503H Unified Service
10HS3B4EHPE 3Y FC CTR Apollo Rack CDU SVCFCHPE 3 Year Foundation Care Call-To-Repair Apollo Rack CDU Service
11HA9J1EHPE Synergy Basic 1st Frame Deploy SVCEduAruba 1 Year Foundation Care Next Business Day Exchange Education/Retail AP-503H Unified Service
12HA9J2EHPE Synergy Basic Add Frame Deploy SVCEdu/VolAruba 1 Year Foundation Care Next Business Day Exchange Volume T1 AP-503H Unified Service
13HA9J1EHPE Synergy Basic 1st Frame Deploy SVC HPE Synergy Basic First Frame Deployment Service
14HA9J2EHPE Synergy Basic Add Frame Deploy SVC HPE Synergy Basic Add Frame Deployment Service
Sheet5
Cell Formulas
RangeFormula
C2:C14C2=IF(RIGHT(A2,2)="PE","PW",IF(COUNTIF(B2,"*Y P*"),"PC",IF(COUNTIF(B2,"*Y F*"),"FC","")))&IF(SUM(COUNTIF(D2,{"*edu*","*vol*"})),CHOOSE(OR(RIGHT(A2,2)="PE",COUNTIF(B2,{"*Y P*","*Y F*"}))*4+SUM(COUNTIF(D2,{"*vol*","*edu*"})*{2,1}),"Edu","Edu/Vol","Edu/Vol","","-Edu","-Edu/Vol","-Edu/Vol"),"")
 
Upvote 0
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"}))," ","-"))
 
Upvote 0
Another one with 2019 or 365

=TEXTJOIN("-",1,IF(ISNUMBER(SEARCH({" PW "," PC "," FC "},B2)),{"PW","PC","FC"},""),IF(ISNUMBER(SEARCH({"Vol","Edu"},D2)),{"Edu/Vol","Edu"},""))
 
Upvote 0
jasonb75 said:
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","")))," ","-")
Click to expand...

Oh My God !!! This is perfectly working as expected.. If you don't mind could you please break up the formula and explain the formula for how it works. Sorry to ask this but I really wanted to learn. Hope you don't mind.
 
Upvote 0
jasonb75 said:
Another one with 2019 or 365

=TEXTJOIN("-",1,IF(ISNUMBER(SEARCH({" PW "," PC "," FC "},B2)),{"PW","PC","FC"},""),IF(ISNUMBER(SEARCH({"Vol","Edu"},D2)),{"Edu/Vol","Edu"},""))
Click to expand...

Thank you so much for your reply.

I am using Microsoft office professional 2013 and it is not working for me getting #Name?
 
Upvote 0
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.
 
Upvote 0
Eric W said:
Try:

Book1
ABCD
1ProductDesc TxtShort Notes100 ch description
2HM1T3PEHPE 1Y PW OEM Prts Log DL360 Gen10 SVCPWHPE 1 Year Post Warranty OEM Parts Logistics DL360 Gen10 Service
3HM1T4PEHPE 1Y PW OEM PrtsLog wDMR DL360 G10 SVCPW-EduAruba 1 Year Foundation Care Next Business Day Exchange Education/Retail AP-503H Unified Service
4HM1T5PEHPE 1Y PW OEMPrtsLog wCDMR DL360 G10 SVCPW-Edu/VolAruba 1 Year Foundation Care Next Business Day Exchange Volume T1 AP-503H Unified Service
5HS3B1EHPE 3Y PC NBD Apollo Rack CDU SVCPC-EduAruba 1 Year Foundation Care Next Business Day Exchange Education/Retail AP-503H Unified Service
6HS3B1EHPE 3Y PC NBD Apollo Rack CDU SVCPC-Edu/VolAruba 1 Year Foundation Care Next Business Day Exchange Volume T1 AP-503H Unified Service
7HS3B1EHPE 3Y PC NBD Apollo Rack CDU SVCPCHPE 3 Year Proactive Care Next Business Day Apollo Rack CDU Service
8HU4F5EAruba 1Y FC NBD Exch EDU/R AP-503HUniSVCFC-EduAruba 1 Year Foundation Care Next Business Day Exchange Education/Retail AP-503H Unified Service
9HU4F6EAruba 1Y FC NBD Exch VolT1 AP-503HUniSVCFC-Edu/VolAruba 1 Year Foundation Care Next Business Day Exchange Volume T1 AP-503H Unified Service
10HS3B4EHPE 3Y FC CTR Apollo Rack CDU SVCFCHPE 3 Year Foundation Care Call-To-Repair Apollo Rack CDU Service
11HA9J1EHPE Synergy Basic 1st Frame Deploy SVCEduAruba 1 Year Foundation Care Next Business Day Exchange Education/Retail AP-503H Unified Service
12HA9J2EHPE Synergy Basic Add Frame Deploy SVCEdu/VolAruba 1 Year Foundation Care Next Business Day Exchange Volume T1 AP-503H Unified Service
13HA9J1EHPE Synergy Basic 1st Frame Deploy SVC HPE Synergy Basic First Frame Deployment Service
14HA9J2EHPE Synergy Basic Add Frame Deploy SVC HPE Synergy Basic Add Frame Deployment Service
Sheet5
Cell Formulas
RangeFormula
C2:C14C2=IF(RIGHT(A2,2)="PE","PW",IF(COUNTIF(B2,"*Y P*"),"PC",IF(COUNTIF(B2,"*Y F*"),"FC","")))&IF(SUM(COUNTIF(D2,{"*edu*","*vol*"})),CHOOSE(OR(RIGHT(A2,2)="PE",COUNTIF(B2,{"*Y P*","*Y F*"}))*4+SUM(COUNTIF(D2,{"*vol*","*edu*"})*{2,1}),"Edu","Edu/Vol","Edu/Vol","","-Edu","-Edu/Vol","-Edu/Vol"),"")
Click to expand...
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
3 three CASCADING DATA VALIDATION (CATEGORY, DESCRIPTION and MODEL) PLEASE HELP ! ! ! ! ! ! Thank you and GOD BLESS....
2
Replies
14
Views
7K
Brixton11
B
A
  • Question
Conditional sum help?
Replies
5
Views
5K
stanleydgromjr
S

Forum statistics

Threads
1,215,009
Messages
6,122,674
Members
449,091
Latest member
peppernaut

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top