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