Can someone help me create a count in column D of different numbers in colum C?

Hi,

I have a question. I am developing a simple excell sheet which I sa on youtube. You can find what I want here:
https://www.youtube.com/watch?v=EkbfjoxQYyY

I allready created something which you can find inside the attachment but I need some help because from here my skills
stops.

I still need the third and fouth column.

The third colum (Column C) should show how many unhit numbers there still are

The fourth column (Column D) should show when there are 12 unhit numbers left and which they are. And the cell lightens up red.

Can someone help me?

I hope so. Thanks.

P.s. the video on youtube shows what I want .

A little knowledge about the game of roulette would come in handy .

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

If you don't mind to split the un-hit number list into multi columns, instead of keeping in single cell:

In C3:

Please Login or Register  to view this content.

In D3:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Drag accross
Copy whole row down.

This attempt gives very different results to bebo021999's solution, so one of us may have misunderstood.

In D3:

Formula:

Please Login or Register  to view this content.

In E3:

Formula:

Please Login or Register  to view this content.

Now copy E3 across to AN3

Finally select D3:AN3 and copy down to row 102

The above is implemented in the attached workbook.

As an added bonus for debug/curiosity, if you set cell C2 to "yes" then all rows will show the numbers not yet hit, not just the row(s) where there are exactly 112 numbers not yet hit.

I think this works per your description and the youtube video. Let us know!

Yes, but you are allowed ONE thread per individual issue here.

If that takes care of your original question in THIS thread, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Originally Posted by Seafreight Discounte

Thanks to everyone. Indeed the correct version is GeoffW283.

Thanks again.

I'm going to develop this somewhat more. Can I ask you some more questions when need?

Sorry - I noticed an "off by one" error in my post #5. The problem is in the E3:AN102 formula. ROW($1:$38) needs to be changed to ROW($1:$37). The correction is highlighted in red:

Formula:

Please Login or Register  to view this content.

I have made this correction in the attached workbook.

Glad to help. Thanks for the rep