3 Sales Review Validation
Explication de la tâche .
My mom is a very well known designer in the world of Jeans! She retired in 2004 from the business after altering the whole world with her skills at making patterns. She even revolutionised how jeans are mass produced and almost every factory in the world uses her method still today.
With COVID hitting the world hard, she realized that masks are a key tool to helping control the spread of possibly deadly diseases. My mom noticing the large lack of comfortable, good looking and washable masks went on a quest to produce a new pattern for masks that would soon take her career by storm.
She opened up a little online store and has found over 600 orders for masks and has been booming since then! Talk about pulling your bootstraps.
At the begining of the adventure, a simple online form was made to track satisfaction of the product. We recently downloaded the Excel file and were impressed to see over 600 entries but the reviews are really bad. People seem to hate the product.
After careful review we noticed that there seemed to be quite some data that seemed illegitemate. First of all, some of the data was from countries the masks weren't even provided too. As well as many reviews not comming from registered users or authenticated users. Finally, we noticed a lot of duplicate comments. We had to confirm that there was some input that was not from actual clients and maybe someone trying to smear the Mom's Masks names! All these seemed to have happened in between 15th & 25th of April.
We would need to build a statement that could check if it's a Valid real entry or just someone trying to smear the name.
Can you make a collumn that would test to see if the users are real :
First Check if they aren't from Canada/US/Mexico
Second thing is if they aren't in between 15th and 25th of April they are valid.
The ones left should be tested to see if they had a registered user and a proof of purchase. If they have both they are valid if they don't they are Invalid.
🌶 Défi épicé . 🌶
Comment faire.
Well. The quest to build an extreme IF starts now.
If you want to try and achieve this goal you will need to build 3 functions first. One for Each Test described and after we will combine it into 1 Massive IF!
Let's go with the first test! Country Testing
IF it's not from Canada AND not from the US AND not from Mexico THEN It's "INVALID" OR ELSE it's "Still unsure"
To try it out, put this function in cell J2 and populate it down:
=IF(AND(E2<>"Canada",E2<>"US",E2<>"Mexico"),"INVALID","Still Unsure")
The second test - Date Testing
We will run a new IF with an OR to check if the date is before the 15th and after the 25th of April 2020. If it is the case they receive the VALID label or else it's Still unsure
The serial number for these dates are (43936 & 43946 respectfully)
To try it out, put this function in cell K2 and populate it down:
=IF(OR(B2<43936,B2>43946),"VALID","Still Unsure")
You could read this function as :
IF the date is before the 15th OR the date is after the 25th THEN it's VALID OR ELSE it's still unsure
The third test - Double TRUE
The goal of this test is to make sure that the both the cell in collumn C and collumn D are populated and if they are, they receive the VALID label and if they aren't they are considered INVALID
Here it is :
=IF(AND(C2=TRUE,D2=TRUE),"VALID","Still Unsure")
Once you have all 3 collumns you can now start thinking of how combining them together. What will be required is some skillfull copying and pasting!
Here is how you can do it in Excel:
First you will go take the function in the K2 cell and select everything except the = and hit CTRL-C to copy it and hit ESCAPE to leave the formula. Go paste it where it says "still unsure" in the IF statement in J2 (because since it's still unsure you add a level of certainty by putting the IF in it)
Look at this gif to get a better idea
Once you validated this last formula go in cell L2 and take this formula without the = and copy it again with CTRL-C. Then hit the ESCAPE key to leave the formula and go in J2 and paste this formula where there is the "Still unsure" and replace the last Still unsure after the paste to "INVALID" hit enter and fill it down and you will have created a column that tests the validity of the data!
Here's the Gif to see the final Step!
Epic right? You can now make IF statements to remove unwanted data from your tables
