• 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.

help needed on triple sets of numbers

G

guitarman

Member
Hi I have a problem.I would like to obtain the amount of times triple sets of numbers appear in a database.I formulated one for pairs of numbers which works fine with this formula. =IF(ISNA(VLOOKUP(Pairs!$A2 & "." &Pairs!C$1,PairStats!$A:$D,4,FALSE)),"",VLOOKUP(Pairs!$A2 & "." &Pairs!C$1,PairStats!$A:$D,4,FALSE)) but it does not seem to work on three can anyone advise were I am going wrong? The layout of the database is like this.

[pre]
Code: 
A,B,C,D,E,F
1,2,3,4,5,6
1,2,3,5,6,7
1,2,3,6,7,8
etc etc etc
[/pre]
Many Thanks for your time

Mike
 
Can you clarify what you mean by a triple set? Does the number only need to exist 3 times, or do they have to be next to each other (by row or column, or both are valid?) What if it exists 4 times? Does that count as 2 sets of 3?
 
Hi Luke. No what I mean't to say three(3) numbers for example 1,2,3 how many times they appear in the database.These numbers refer to club members it is easier than putting their names in.e.g 1=Joe Stanton 2=Brian Hatton 3=Gordon Dean and so on the numbers go upto 329 so as you can imaging the database is quite large.So it will incorporate all of them i.e.123 345 456 678 etc etc. Is that any clearer?

Thanks Mike
 
I'm afraid not, sorry. Could you post some examples of what counts as a triple, and what doesn't? Looking at the first post, I see that there is a single number in each cell, the rows tend to repeat the numbers from above, and not all the numbers are in order. Sorry if I'm just being slot today. =P
 
Hi Luke. Well a triple set of numbers is say 1,2,3 or 14,26,45 the earlier ones I gave were just examples.So the database looks like this.

A, B, C, D, E, F,

1 12 22 31 38 45

15 2 19 24 44 50

1 2 3 17 35 39

So I would want know how many times say (12,31,45)(1,2,3)(2,19,44) appear in the database bearing in mind these numbers represent names of club members. (12=Joe)(31=Brian)(45=Gordon) and this will tell me how many times they have played together and also who has not played with who. I hope that is a bit clearer.

Thanks Mike
 
If your set of 3 numbers is in K1, L1, and M1, this array formula will return a count of how many times those 3 numbers appear in a row.

=SUM(1*(MMULT((A2:F5=K1)+(A2:F5=L1)+(A2:F5=M1),TRANSPOSE(1*(COLUMN(A2:F5)>1)))>=3))


Confirm using Ctrl+Shift+Enter.
 
Hi Mike,

Here is how I interpreted your question.

You have a set of numbers in rows, with each row representing a set of players.

You are attempting to count the number of times a set of three players have been together (i.e. as found in a given row).

The players in a row can be in any order. You are just looking to see if the three players you are searching for, appear in some order within one or more rows. And you are looking for the number of rows where a combination of the three players are found.

If my interpretation of your question is correct, you could use the following formula:


=SUMPRODUCT(--(MMULT((--(ISNUMBER(MATCH(playersdata,players,0)))), {1;1;1;1;1;1})=3))


where "playersdata" refers to your rows of data, and

"players" are the list of three players you are looking for.

By the way, the number of 1s in {1;1;1...} is the same as the number of columns in "playersdata". As such, if your dataset has more columns, please adjust that value accordingly.

Also, if you need to look for combinations of more than three players, please change the reference to "3" above to the number of players in "players".


Hope this helps.


Cheers,

Sajan.
 
Hi Luke & stthomas

I have put in the Formula from Luke and all it returns is (0)in every cell but the formula is bracketed by {} so excel does not see an error. And the formula from stthomas returns (value) in every cell so what do you think guys what have I done wrong?

Thanks Mike
 
Hi Mike,

Could you clarify what you mean by "in every cell"? The formulas were not meant to be copied for every row in your playersdata.


You would want to use it something like as follows:

[pre]
Code: 
colX   colY   colZ   colAA
12      15     23    Put the formula here, replacing the reference to "players"
35      27     45    Put the formula here too, ...
etc.
[/pre]
Hope this helps.


Cheers,

Sajan.
 
Sajan,

I'm just glad to see that I wasn't the only one interpreting the question in that way. I'll also admit, that was the first time I've actually used the MMULT function.
 
Hi Luke,

Ever since I found out about the MMULT function, I "suddenly" found lots of ways to use it!!


One of the things I love about the function is that it returns an array of sums (unlike the SUMPRODUCT that returns a single value).


And by the way, welcome back! Hope you had a good vacation! (And back to the heat wave around here.)


Cheers,

Sajan.
 
Sajan,

Good point about returning an array, certainly gives you a bit more versatility. I just have to be more careful about array sizes and the order I put the arrays in.

Yeah, not so crazy about the heat...just feels so humid too. =/
 
Hi Luke & Sajan. My apologies for not getting back sooner but unfortunately we had a power cut here they blamed the constant rain for it typical anyway they have fixed it now.

so to revert back to the problem i will lay out how I want it to work.

A, B, C, D, E, F,

15 22 14 35 55 16

04 18 03 27 19 58

44 20 22 39 24 44

These are the records of the players dating from 1987 so as you can imagine there is a lot of them(1142)rows in six(6) columns.Row one for example 15,22,14,35,55,16, are six(6) players going out together playing as two(2) sets of three(3) and then Five(5) minutes later another set go out 04,18,03,27,19,58,and so on as you can see number 22 has gone out with 15,14,35,55,16, and also in the third set he has also gone out with 44,20,39,24,44, so what I want to be able to achieve an array that tells me how many times each player in the trios has gone out with every other player. So say players 15,22,14 have gone out with players 35,55,16, but how many times over the years has this occured and the same with the rest of the players so my answer would be something like how many times has trio 15,22,14, gone out with all the other trios. I hope this is a lot clearer to you Many Thanks for your time and trouble.

Mike
 
Hi Mike,

Are you looking to find out how many times a given player in a trio has played with other players in the same trio? Or, are you trying to find out how many times a given trio has played with another trio? Or something else?


If possible, can you mock up a result set the way you want it, (similar to how you shared your data set)? That might help us understand exactly what you are expecting. For your mock result set, use the data from your original post. That will allow us to see how the results were derived from the source data.


Regards,

Sajan.
 
Hi Sajan

I am trying to find how many times a (Trio) plays against other (Trios) Example

1,2,3, plays 4,5,6,and then 1,2,3, plays 4,5,7. 1,2,3, plays 4,5,8.and so on. But on reflection I think I had better scrap this idea because the Array table would be absolutely enormous.Thanks for your time and trouble

Mike
 
Hi Luke

Thanks for your assistance but obviously as you agree the Array table would be enormous. Still we all have these scatter brained ideas from time to time it was only when I really thought about it that I realised it was a no-goer, still it would have been cool to actually do it.Anyway thanks for your time and effort. Have a great day and a brilliant life.

Mike
 
You must log in or register to reply here.
Back
Top