An Excel fan send this to me and has given me permission to share this with you. Enjoy!

I remembered sending you my ‘Toto Project’ which was then a ‘draft copy’. I did some improvement to fix some ‘bugs’ as the highlight of winning numbers were messy…please refer attached file.

I’m not a gambler though sometimes do buy TOTO (It is also known as Jackpot) for fun. This Toto Winning Checklist allows user to key in multiple ticket data and able to match up to ten winning numbers (I stopped at 10 though can do more as I think should be sufficient…just use a new sheet if beyond 10).

I seldom use it myself as the purpose of doing this project is to provide some practices for myself to explore new things in excel. I’m very pleased to learn from this project a new and interesting function that I’ve found from a website. I needed very much a function to filter duplicate data (not the one built in excel) in order to complete the project. After which I was able to use it to complete my IR8A project which was long pending awaiting for this duplicate filtering function. Without this function, I was not able to complete the IR8A project, a very Big Thank You to this person who provide this free function.

I’ve been seeking such function for a long time. I first used it on the Toto Project, after it works then I realised it could also work on IR8A to filter duplicate employee names that I was very excited to try it on the IR8A project. It has proven to work in year 2012. With some enhancement, year 2013 IR8A data submission was smooth and easy. As I like simple things, there has been No Macro and I also like everything in a workbook, so there is no link involved on multiple workbooks as it may cause unforeseen problems. Though there is copy and paste of data, I try to make it very simple with just one time data range select, copy and paste and the rest are self-extraction by Excel.

Below is the function for your reference as it may not be something new to you but it is certainly a very BIG and Important function to me. Although I do not know how exactly it works, It provides me with the answer I required and have been seeking for a long time:

 {=IF($X4=””,””,IF($X4=”end”,””,IF(INDEX(Payroll_Name,MATCH(0,COUNTIF($X$4:X4,’Payroll Data’!$E$1:$E$1109),0))=0,”end”,IF(ISNA(INDEX(Payroll_Name,MATCH(0,COUNTIF($X$4:X4,’Payroll Data’!$E$1:$E$1109),0))),”end”,(INDEX(Payroll_Name,MATCH(0,COUNTIF($X$4:X4,’Payroll Data’!$E$1:$E$1109),0)))))))}

I have also attached the Toto Winning Checklist she has sent to me for your reference.

Leave a Reply