excel match feature

[simon31]

Hello there,

I use countif formula to find out how many times in one year 8 numbers have matched.

Usually 8 numbers match only once a year or maybe not. prize money for 8 numbers is 10k as above.

But there many times 4,5 or 6 numbers match.

For example to find out how many times 4numbers have matched, I have to scroll through for the entire year to find that out and that takes a very long time.

1) What formula can I use to find out how many times 4 numbers have matched so I can get the answer in one go.?

Each 4 numbers matched has prize money of $2.

2) what formula can I use to find out the total dollar value of the 4 numbers matched ?

https://filebin.net/kkiwhh8z3nsxif74

Thanks
simon

 

[simon31]

Re: excel filter - using source keno file to update the current file

I will test it out now and get back. Thanks for putting it all under one sheet as it helps with the viewing.

 

[simon31]

excel filter - update of keno results

The layout looks good, but forgot to add that the keno player can play upto 10 numbers per line in each draw. If you could please extend the highlighted range to include two more cells (W5 and X5) to make it a total range between O5:X5 to make it 10 winning numbers as I am playing 10 numbers, which would need a filter to extend to 10 numbers in cell M5.

Regarding the New Data Macro button, how does the New Data Macro button work ?
I mean how is the new data macro updating the results?

When I click new data button, it creates 4 empty rows which is good, but it does not update the new draws.

So am I typing the results manually? or is it looking elsewhere for updating the new results?

When I typed the new 4 draws results manually and clicked on the new data button, it repeats the previous draw. Not sure why ?

 

[AlanY]

Re: excel filter - update of keno results

I've implemented the changes to cover upto 10 nos.

https://drive.google.com/file/d/1J3Tj6Yw_usEPrGLF0dKnKOw4GRXO8VwI/view?usp=sharing

as for the new data macro it will just create 4 new rows with the formulas ready for the new draw data so that you don't have to or forget to copy the hidden formulas. you have to copy and paste the new draw data manually.

 

[simon31]

Re: excel filter - update of keno results

The viewing range in excel has changed while updating to 10 numbers and a horizontal scroll has appeared at the bottom, forcing me to scroll to the right to view column V and W, column V and W has fallen outside the viewing range.

I am using the file on 15.4 inch windows laptop.

Are you able to eliminate the horizontal scroll bar to bring column V and W in viewing range to fit on 15.4 inch screen. You could marginally reduce the column width of the numbers 1 to 20 for that to happen, unless you have a better way.?

If I got you right, I have typed the 4 draws in the source file and then I copied it and pasted it into your file and that has gone well.
But the moment I click on the new data macro button, the last draw gets autopopulated into 4 new rows. so they are not empty rows anymore. Don't know whats wrong there ?

 

[AlanY]

Re: excel filter - update of keno results

yes, i think it's better to reduce the column width (C:W or Col B as well) by a small amount so that they can all fitted in.

I'd fixed the problem re. the new data macro as well.

https://drive.google.com/file/d/136G0a-QtXRF7RiFk6I_WDQA2fOnrt_Ub/view?usp=sharing

 

[AlanY]

Re: excel filter - update of keno results

If I got you right, I have typed the 4 draws in the source file and then I copied it and pasted it into your file and that has gone well.

what is the source file with the draw results looks like, that may be a better way than typed them in manually

 

[simon31]

Re: excel filter - update of keno results

the source file on the keno website is updated only twice a week on Thursday and Monday. That is why I have to type the draws manually in the source file.

If I wait for the source file of the website till thursday or monday, I will be falling behind in updating your file to the latest draw.

I have attached the source file which I have manually updated till 18th June 19.

source file : https://filebin.net/oq5s362a4spekvwh

The draw serial numbers are auto populating when pressing the new data macro button which is good so I don't have to type them, but not the draw dates. I have to type the 4 dates of the 4 draws manually . can the draw date be auto populated as well along with the draw number when I click the new data macro button?

If you look at cell E5 in your file, there is an exclamation mark error with the number 17151. do you know what it is? And what is in column X and AB ?

Column W is still outside view range Is there a way to autofit the file to different screens without losing the view range ? just like the internet browser which adjusts itself to different screens.
In the office I use a larger 17 inch monitor screen, but at home I use my windows laptop which has screen size of 15.4 inches. That is why I need one file to autofit both those screens to see the data within view range.

 

[AlanY]

Re: excel filter - update of keno results

Cell E5 shown the no of matches, with no filter the no 17151 is the total no of draws in the file.
if you apply the filter, the number should reduce to show the no of matches.

the hidden 3 columns Y:AA are formulas are to set the criteria for the filters, you can unhide them to have a look.
I'm afraid you have to keep reducing the columns width until it fit all onto your screen.

 

[simon31]

Re: excel filter - missing out on the time column

e5 is a good way of displaying the match frequency in your current file.


I realised the filter process is incomplete without the time column.


I am unable to know at a glance which of the four draw times hit and at what frequency of that time slot, when I use the filter as some hits show one draw for that day.


I understand that view range will be affected on a 15.4 inch screen and I will reduce the column size as you advised. Now I am using a 17 inch monitor at the office and I have a wide viewing range upto column AH.


Could you please add a time column with the four times? 10am, 1pm, 3pm and 6pm.


Again I forgot the website you had shown me a while ago to insert snapshots in the post.? If you could please display that info again.

 

[simon31]

Re: excel filter - missing out on the time column

Forgot to mention , when you add a time column , could you please integrate that in the new data macro button. So when I click on the new data macro button, four blank rows along with draw times, draw numbers and draw dates would get automatically added.