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:
Anders
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!
Gustav
One way to solve it:
=IFERROR(IF(FIND(7;B4;FIND(0;B4;FIND(0;B4;1)+1))>0;”Yes”;”No”);”No”)
Emil
@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.
David
Denna verkar funka fint: =OM(OMFEL(SĂK(“0*0*7″;B4;1);0)>0;”Yes”;”No”)