hi

i am analyzing numbers. i have 6 distinct columns of numbers. each column has 5 to 7 variables. What I want to do is program each column with its variables, and have some program (excel? hence the need for help) spit out all the possible unique combinations of those numbers

for example:

col1 col2 col3 col4 col5 col6
1 10 21 34 40 11
3 14 23 37 42 14
4 17 28 38 43 18
5 19 30 39 44 20

What I want the spread sheet to do is spit out all the possible *unique* combinations of those numbers, with only the numbers showing in any of the columns at anytime that are uniquely assigned to the columns.

if you know of a program that does please let me know

thanks

Good news: Welcome to the forum!
Bad news: Excel will not do it...
So-So news: ...but you might be able to coax Excel to do so.

Let me see if I understand what you're data set is:

+--+--+--+--+--+--+
| 1|10|21|34|40|11|
+--+--+--+--+--+--+
| 3|14|23|37|42|14|
+--+--+--+--+--+--+
| 4|17|28|38|43|18|
+--+--+--+--+--+--+
| 5|19|30|39|44|20|
+--+--+--+--+--+--+

If that's right, then you should be able to do this...

+---------------+---------------+---------------+->
|       1       |      10       |      21       |
+---------------+---------------+---------------+->
|       3       |      14       |      23       |
+---------------+---------------+---------------+->
|       4       |      17       |      28       |
+---------------+---------------+---------------+->
|       5       |      19       |      30       |
+---------------+---------------+---------------+->
|               |               |               |
V               V               V               V
+---------------+---------------+---------------+->
|=COUNT(A1:A999)|=COUNT(B1:B999)|=COUNT(C1:C999)|
+---------------+---------------+---------------+->

...and use the SUM() function to get nCr (or is it nPr - It's been ages since I've done this...)

It's not quite what you're looking for, I'm sure, as it doesn't take uniqueness into account. For that you'll have to do some light programming.

hmm... I must have missed the part where this has anything to do with seti@home. By your user name and the fact that you have no computers registered to your account, I assume you are randomly posting on message boards trying to get someone else to do your homework for you :) Good luck. I would probably write a quick java or perl program to do what you need... I find it is often easier to do that than to learn all the tricks of a huge program just to do a simple task :)

---

A member of The Knights Who Say NI!
For rankings, history graphs and more, check out:
My BOINC stats site

it is better than what i have so far :)

where does the sum() function come into play, and what does nCr (or nPr) stnd for?

thanks

NeoAmsterdam

He or she is gonna get an "A" for your work!


Timmy

---

With Excel, you could do a "quick and dirty" check with the "match" function. For example, take these four rows of numbers.
1 | 5 | 7 | 4 | 34
33 | 7 | 56 | 32 | 4
55 | 6 | 27 | 22 | 5
5 | 62 | 11 | 4 | 34

The syntex for the match function to test cell A1 with row A2 is:
=match(A1,A2:E2,0) Since thre's no "1" in row 2 column A, a #N/A is returned.
However, the =match(C1,A2:E2,0) returns a "2" indication there was a match of a "7" from row 1 in row 2 at position 2 (B2).

Test row 1 against rows 2, 3, 4
A | B | C | D | E
#N/A | #N/A | 2 | 5 | #N/A
#N/A | 5 | #N/A | #N/A | #N/A
#N/A | 1 | #N/A | 4 | 5

and so on, for the next three rows

[edit] I know, my formatting is terrible, but it's nearly 3 AM. I forgot how bad pasting from Excel looked. Guess some [pre] tags would have helped.

---

<img src='http://www.boincsynergy.com/images/stats/comb-912.jpg'>

here's the answer


for 12 numbers,
i.e. 2 sets of 6 numbers in 6 cols:

you want: 2 x 2 x 2 x 2 x 2 x 2
= 2^6 = 64 combinations

In Sheet1
-------------
Assume the 12 numbers are in A1:F2
(all 12 numbers assumed unique)

1 10 21 34 40 11
3 14 23 37 42 13

In Sheet2
-------------
Put in:

A1: =OFFSET(Sheet1!$A$1,INT((ROW(A1)-1)/2),)
B1: =OFFSET(Sheet1!$B$1,MOD(ROW(A1)-1,2),)
C1: =OFFSET(Sheet1!$C$1,INT((ROW(A1)-1)/2),)
D1: =OFFSET(Sheet1!$D$1,MOD(ROW(A1)-1,2),)
E1: =OFFSET(Sheet1!$E$1,INT((ROW(A1)-1)/2),)
F1: =OFFSET(Sheet1!$F$1,MOD(ROW(A1)-1,2),)

Select A1:F1, copy down to F4

In Sheet3
-------------
Put in A1:
=OFFSET(Sheet2!$A$1,INT((ROW(A1)-1)/4),)&"-"&OFFSET(Sheet2!$B$1,INT((ROW(A1)-1)/4),)

Put in B1:
=OFFSET(Sheet2!$C$1,MOD(ROW(A1)-1,4),)&"-"&OFFSET(Sheet2!$C$1,MOD(ROW(A1)-1,4),1)

Put in C1:
=OFFSET(Sheet2!$E$1,MOD(ROW(A1)-1,4),)&"-"&OFFSET(Sheet2!$E$1,MOD(ROW(A1)-1,4),1)

Select A1:C1, copy down to C16

In Sheet4
------------
Put in A1:

=OFFSET(Sheet3!$A$1,INT((ROW(A1)-1)/4),)&"-"&OFFSET(Sheet3!$B$1,INT((ROW(A1)-1)/4),)&"-"&OFFSET(Sheet3!$C$1,MOD(ROW(A1)-1,4),)

Copy down to A64

A1:A64 will return all the 64 "unique" combinations
of the 12 numbers in Sheet1's A1:F4
(joined with hyphens)

1-10-21-34-40-11
1-10-21-34-40-13
1-10-21-34-42-11
1-10-21-34-42-13
1-10-21-37-40-11
1-10-21-37-40-13
......
......
3-14-23-37-40-11
3-14-23-37-40-13
3-14-23-37-42-11
3-14-23-37-42-13

=============================

Now, for the smart ass who wondered why I posted here, the title of the message board is "NUMBER CRUNCHING". To the rest, my gratitude.

---

> Now, for the smart ass who wondered why I posted here, the title of the
> message board is "NUMBER CRUNCHING". To the rest, my gratitude.

"Number Crunching" as it relates to seti@home - all the numbers around here are crunched by our CPUs. You obviously didn't bother to look at the context of this message board. I'm not saying you are a bad person for posting here or that no one should help you... I was just pointing something out. But heck... while you are here, why NOT install BOINC and do some serious number crunching? Then we can officially welcome you :)

---

A member of The Knights Who Say NI!
For rankings, history graphs and more, check out:
My BOINC stats site

Actually, when the program runs the CPU does the crunching, so I guess I was paying attention after all. Nothing the world needs more than another message board policeman. It makes the internet such a pleasant place. Fortunately, there are still those who don't play message board cop and actually address the post on its face instead of making smart assed flames and jabs. In ther land of Liliput quite the furor is raised over nothing at all.



> > Now, for the smart ass who wondered why I posted here, the title of the
> > message board is "NUMBER CRUNCHING". To the rest, my gratitude.
>
> "Number Crunching" as it relates to seti@home - all the numbers around here
> are crunched by our CPUs. You obviously didn't bother to look at the context
> of this message board. I'm not saying you are a bad person for posting here
> or that no one should help you... I was just pointing something out. But
> heck... while you are here, why NOT install BOINC and do some serious number
> crunching? Then we can officially welcome you :)
>

He or she is gonna get an "A" for your work!
I'd rather that stupid level 101 questions be redirected to Rent-a-coder, too. Either way, it'll serve our filtered friend right for not putting his brain to work if he takes my advice.

I left you a bug. .o0(Nyah, nyah!)
Crunch a few units - Then we'll talk.

> Actually, when the program runs the CPU does the crunching, so I guess I was
> paying attention after all.

Sir, you may have the forum name, but the site is all about the Search for Extraterrestrial Intelligence, not how to get someone else to do your homework.

If you read the forum a bit, you'd see that.

So, some people were incredibly nice -- and you've been invited to stay and contribute to the search.

---