• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

programme for filling up an excel worksheet

L

latelearner

New Member
Can a programme be written for the following situation? in C or C+ or C# or dot net or any other language


1. Nine numbers 1,2,3,4,5,6,7,8and 9 are to be filled in 12 adjacent cells in a row in an excel worksheet.


2. A cell can be blank. It can contain one or more numbers.

3. In a row a number can appear only once.

4. In a cell the order of numbers does not matter. ie., 2,3,5, is the same as 2,5,3 or 3,5,2, or 3,2,5

or 5,2,3 or 5,3,2


How many rows will be needed to fill the cells in all possible combinations with the conditions stated above
 
Hi, latelearner!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


I can't believe that I still get amazed with certain questions, so let me try to be as clear as the outer space (which in fact is pristine due to its emptiness):

a) you can write a program in any of those languages to fill up an Excel worksheet, as long as you have an Excel Object Library referenced within your Visual Studio .NET project

b) you can write a program in Visual Basic too, which is almost the same language that Excel uses for macros

c) you can write an Excel procedure to do so, in the same or in another module of the workbook, or in the same or in another workbook, which I think it's the most natural way, unless your project specifications say something else

d) you say "12 adjacent cells in a row", 2 lines after "in a row a number can appear only once", it wasn't specified in "nine numbers .. are to be filled in 12 adjacent cells in a row"?

e) this is the most intriguing part: "how many rows will be needed to fill the cells in all possible combinations"?... 1, kid, as no number could be repeated in a row but a cell with all the numbers or a number per cell will be enough.


Regards!
 
Lateleaner


Firstly, Welcome to the Chandoo.org Forums


You might want to have a read of

https://sites.google.com/site/e90e50fx/home/combinatorics-using-excel-formulas-and-examples

I think it will answer your question
 
Hello Sir/Madam,

Thank you very much for the prompt reply. I am a beginner and I am yet to learn even the basics. I am learning slowly.

Actually I wanted this information regarding filling cells , in the study of astrology. There also I am a beginner. 'The twelve adjacent cells ' in my question refers to the 12 zodiac signs. The numbers '1,2,3,4,5,6,7,8,and 9 'in my question refer to the nine planets ( we can call them heavenly bodies because the sun which is a star is also grouped with the others as a 'planet') in Hindu Vedic Astrology namely Sun, Moon, Mars, Jupiter, Venus, Mercury, Saturn, Rahu(Shadow planet) and Kethu(Shadow Planet). The positions of these heavenly bodies at the time of ones birth are noted in one's birth chart called horoscope. So I wanted to find out how many combinations of the positions of the planets are possible. Each row represents the horoscope of one individual. Actually I am trying to fill the cells manually. The manual work seems to have no end. So out of necessity I am trying to find an easier way to fill cells

I shall try the method suggested in your two mails.

Regards,

Late learner.
 
Latelearner


I have had a quick look at the link I posted for you above.


I could not get the formula's to work with n>=6 so just be aware when you start looking at that as an option
 
Hi, latelearner!


If you happen to come back soon perhaps you'd want to give a look at this link, I didn't test anything, just read, but the source and the author are respectable and have good reputation:

http://www.linkedin.com/groups/Roberto-Mensa-Combinatorics-Excel-3843467.S.212801588?view=&srchtype=discussedNews&gid=3843467&item=212801588&type=member&trk=eml-anet_dig-b_mc-ttl-cn&ut=2fIMRsrlFU-lI1


Regards!
 
Hi ,


I don't think Mensa's formulae can be applied directly in this case , since OP says cells can even be blank. Two questions :


1. Can you clarify as to how many cells out of the 12 in a row can be blank ?


2. How many numbers can be entered in a cell ? Can all 9 numbers appear in one cell ?


It would be nice if you could give at least a few examples of how the 12 adjacent cells can be filled up with the 9 numbers , especially since you say that you have started this task manually. Can you just copy + paste whatever you have already done ?


Narayan
 
Hi,

I am sorry, I had not framed my question properly.

1. a. A maximum of 11 cells (within the 12 adjacent cells) can be blank in one row .

ie., the twelfth cell will contain all the nine numbers.

b. All the 12 cells can contain numbers i.e., each cell will

contain one number in a row.

c. If one cell is blank in a row, the nine numbers will be distributed among the

remaining 11 cells.


d. If two cells are blank in a row, the nine numbers will be distributed among

the remaining 10 cells(within the 12 adjacent cells).


e. Will it be easy if instead of nine numbers I add 0 also and have it as 10

numbers. In that case my previous blank cell will now be a cell with only a 0

in it.Can the Mensa's formula be applied now?


2. Actually my twelve adjacent cells represent the twelve Rasis (zodiacal signs)

ie.,Mesham Rishabam,Mithunam...Kadagam...Dhanus...Meenam etc.(Aries,

Taurus...Sagittarius..etc). These rasis are to be filled with the nine heavenly

bodies Sun, Moon, Mars, Saturn, Jupiter, etc. Each row containing the 'twelve

adjacent cells' will denote one individual's horoscope.

3. If I can fill the cells with numbers, later I can replace number1 with Sun, number 2

with moon, number 3 with mars etc.


As I do not know whether I can copy+paste a table or excel sheet I am trying to give it

in some other form.

Row 1: First 11 cells are blank.12 th cell contains all the 9 numbers.

Row 2: All the 12 cells contain numbers, each cell contains one number.

Row 3: Cell one contains numbers 7,2, and 9

Cell two, three, four and five blank

Cell six contains numbers 1,5,3,4 and 6

Cell seven blank

Cell eight contains number 8

Cell nine ten eleven and twelve are blank

Thanks.

Latelearner.
 
Hi,

In my previous posting I had made one mistake. In the line which starts with Row 2: I had stated that all the twelve cells contain numbers. It is not correct. It should read 'nine cells contain numbers'. Is there any way by which I can send 10 or 12 worksheets to the forum?

late learner
 
Hi ,


I doubt that your problem is such an easy one ; going by the details you have given , I doubt that the number of combinations will fit in an Excel worksheet , unless you can also give some exclusion rules , so that certain combinations can be excluded.


I do not know the purpose of uploading 10 or 12 files , unless they cover all cases ; if they are only a sample of the possible combinations , then even one workbook should do.


This forum does not have a provision for either uploading files or attaching files to posts.


What you can do is to use your preferred file-sharing website ( RapidShare , Hotfile , DropBox , SkyDrive , GoogleDocs , SpeedyShare ... ) to upload your file , give others permission to access and download the file , and then post that access link here , in this same topic.


Narayan
 
12! = 479,001,600 combinations

13! = 6,227,020,800 combinations including a blank

So yes, much bigger than Excel will handle
 
You must log in or register to reply here.
Back
Top