In our first quiz the task is simple – but not too easy: find the secret codes in a list of shipment numbers using Excel!
Download the workbook:
The code hidden in the shipment numbers is 007. Use Excel to come up with a solution that tells how many of the shipment numbers have a secret code in them.
Write your solution and answer in the comments! 🙂
Our solution:
You must be logged in to post a comment.
Is it…? Is it worth the time you need to spend before you learn the...
In today’s post, you will learn how you can use Excel for solving the shortest...
We have now come to the post for the powerful function SUMPRODUCT… my favourite function....
Emil har jobbat på Learnesy som produktutvecklare i flera år och ligger bakom flera av Learnesys kurser. Han har en utbildning inom datavetenskap med ett stort intresse för det analytiska och teoretiska. Utöver Finska och Svenska snackar Emil även flytande Excelfunktioner!
Multiple helper columns:
Position of 1st 0: =IFERROR(FIND(B$2,$A3,1),””)
Position of 2nd 0: =IFERROR(IF($B3″”,FIND(C$2,$A3,$B3+1),””),””)
Position of 7 after second 0: =IFERROR(FIND(D$2,$A3,$C3+1),””)
3 digit code check: =IF(LEN(TEXTJOIN(“”,TRUE,B3:D3))=3,1,””)
Code Sequence Check: =IF(AND(B3<C3,C3<D3),"YES","NO")
Code present: =IF(E3=1,F3,"NO")
Probably not the optimal solution, but works!
One way to solve it:
=IFERROR(IF(FIND(7;B4;FIND(0;B4;FIND(0;B4;1)+1))>0;”Yes”;”No”);”No”)
@Gustav
Awesome! Almost identical to our solution!
In the IF – function you actually don’t need to perform a logical test, since an integer by it self will return True.
Denna verkar funka fint: =OM(OMFEL(SÖK(“0*0*7″;B4;1);0)>0;”Yes”;”No”)