Balajibenz
Board Regular
- Joined
- Nov 18, 2020
- Messages
- 80
- Office Version
- 2013
- Platform
- Windows
Can you people help me with below query using a VBA code or excel formula (if it is feasible and doestn't take time).
I have data in sheet1 where there is ID in column "A" and category in Column "B". category will have multiple text separated by commas. Possible categories are - Production, Development, Staging, Test, Training, UserAcceptanceTest, DisasterRecovery. what i am looking for is to have below result in column "C" for the below mentioned scenarios.
If Category column is as below then Column "C" values should be the one after "-" as below
1. empty - No
2. Development - Dev
3. Production - Prod
4. Test - Test
5. Staging - Staging
6. Training - Training
7. UserAcceptanceTest - UAT
8. Development AND Test AND Production OR any other category - All
9. Development AND Test OR any other category (Except Production) - Dev/Test
10. Test AND Production OR any other category (Except Development) - Dev/Test
11. Development AND any other category (Except Production and Test ) - Dev
12. Production AND any other category (Except Development and Test ) - Prod
13. Test AND any other category (Except Development and Production) - Test
14. any other scenarion - Not Defined
Below is the sample data for the reference. original data has 1k lines and I couldn't share it due to access issues.
I have data in sheet1 where there is ID in column "A" and category in Column "B". category will have multiple text separated by commas. Possible categories are - Production, Development, Staging, Test, Training, UserAcceptanceTest, DisasterRecovery. what i am looking for is to have below result in column "C" for the below mentioned scenarios.
If Category column is as below then Column "C" values should be the one after "-" as below
1. empty - No
2. Development - Dev
3. Production - Prod
4. Test - Test
5. Staging - Staging
6. Training - Training
7. UserAcceptanceTest - UAT
8. Development AND Test AND Production OR any other category - All
9. Development AND Test OR any other category (Except Production) - Dev/Test
10. Test AND Production OR any other category (Except Development) - Dev/Test
11. Development AND any other category (Except Production and Test ) - Dev
12. Production AND any other category (Except Development and Test ) - Prod
13. Test AND any other category (Except Development and Production) - Test
14. any other scenarion - Not Defined
Below is the sample data for the reference. original data has 1k lines and I couldn't share it due to access issues.
ID | Category |
121 | Production |
135 | Production, Development, Staging, Test, Training, UserAcceptanceTest |
283 | Development, Test |
1058 | Production, Test |
148 | UserAcceptanceTest, Development |
1125 | Production, UserAcceptanceTest |
1138 | |