How calculate these combinations ?

erik.van.geit

MrExcel MVP

Joined

Feb 1, 2003

Messages

17,832

• Nov 13, 2005

• #2

hello, The Wraith,

Now ...how can i calculate how much times off the 3000 lines these kind of combinations exists..

-- combination of 2 numbers
-- combi of 3 and 4 numbers Hello,
can yo clarify a bit please

Click to expand...

can you provide an example ?
or would this do ?

Code:

                            Find
3   4   6   7   8   10      8   9
3   4   6   8   9   11      RESULT
4   6   8   9   10  12      2
4   7   8   10  11  12

kind regards,
Erik

 

Upvote 0

T

The Wraith

New Member

Joined

Nov 12, 2005

Messages

12

• Nov 19, 2005

• #3

This is a good idee , but how to start to make vba

it looks ok , but where do i start to create the vba or code to realize the count...

something that analyse each row ,number by number and count if each nr together with another nr exist more as 1 time in the total list of 3000 rows...even when 3 same numbers exists more times together...and 4 and 5....it is maybe to complex for all thes countings ?

greetings...

 

Upvote 0

erik.van.geit

MrExcel MVP

Joined

Feb 1, 2003

Messages

17,832

• Nov 19, 2005

• #4

No, not to complex using a sumproductformula

F12: =SUMPRODUCT(--($A$3:$G$8=$A$12),--($B$3:$H$8=$B$12))
F13: =SUMPRODUCT(--($A$3:$F$8=$A$13),--($B$3:$G$8=$B$13),--($C$3:$H$8=$C$13))
F14: =SUMPRODUCT(--($A$3:$E$8=$A$14),--($B$3:$F$8=$B$14),--($C$3:$G$8=$C$14),--($D$3:$H$8=$D$14))

when you use a named range the formulas are more "readable" and easier to check for typos
H12: =SUMPRODUCT(--(data=$A$12),--(OFFSET(data,0,1)=$B$12))
H13: =SUMPRODUCT(--(data=$A$13),--(OFFSET(data,0,1)=$B$13),--(OFFSET(data,0,2)=$C$13))
H14: =SUMPRODUCT(--(data=$A$14),--(OFFSET(data,0,1)=$B$14),--(OFFSET(data,0,2)=$C$14),--(OFFSET(data,0,3)=$D$14))
BUT you can get wrong results if you leave blank one criteria with the "data"formula
therefore you can fill in some "impossible" data as I did "|@|" or define your named range according to the formulas ...

perhaps difficult to understand what I mean here, just try out you will see!

of course these formulas won't tell you where you can find those items
surely that can be done to...

the wraith.xls
	A	B	C	D	E	F	G	H	I	J	K
2	DATA
3	aa	bb	c	AA	BB	CC	AA	BB	|@|	|@|	|@|
4	a	b	c	d	8	9	10	AA	|@|	|@|	|@|
5	AA	BB	9	10	11	AA	BB	CC	|@|	|@|	|@|
6	a	b	8	9	10	11	AA	BB	|@|	|@|	|@|
7	c	d	8	9	10	11	a	a	|@|	|@|	|@|
8	c	d	8	9	10	11	a	a	|@|	|@|	|@|
9
10
11	criteria					RESULT		RESULT
12	AA	BB				6		6
13	AA	BB				0		0
14	8	9	10	11		3		3
the wraith

kind regards,
Erik

 

Upvote 0

T

The Wraith

New Member

Joined

Nov 12, 2005

Messages

12

• Nov 19, 2005

• #5

It's a good solution

Ok thx

I go to try it out and see if it it working within my list..

greetings

 

Upvote 0

T

The Wraith

New Member

Joined

Nov 12, 2005

Messages

12

• Nov 21, 2005

• #6

Is not working perfect.. but was good tip

i tried it out , but offset was not really working in my excel 2000..

But it does not give me all the couples of 2 and 3 and 4 and 5 by them self when i want to use these code formules...

I still have to put the couples manually in it , so that this can count them after..gives me to much typing work i guess

but what i search is a kind of vba maybe that analyzes my list nr by nr and count how many times together with the other nrs they exists in the rows..

any tip to put a nice vba together is welcom..

Greetz

 

Upvote 0

erik.van.geit

MrExcel MVP

Joined

Feb 1, 2003

Messages

17,832

• Nov 21, 2005

• #7

But it does not give me all the couples of 2 and 3 and 4 and 5 by them self when i want to use these code formules...

Click to expand...

that's not what you asked

Now ...how can i calculate how much times off the 3000 lines these kind of combinations exists..

Click to expand...

just no time left now ... I'll come back ASAP

 

Upvote 0

erik.van.geit

MrExcel MVP

Joined

Feb 1, 2003

Messages

17,832

• Nov 22, 2005

• #8

but what i search is a kind of vba maybe that analyzes my list nr by nr and count how many times together with the other nrs they exists in the rows..

Click to expand...

seems you want to get a list of all numbers in a table and find how many times they are combined with others ...

to be sure what you really would like to get
let us see what would be the output if this is the table to start with

Map1
	A	B	C	D
1	1	2	3	4
2	1	3	4	5
3	1	2	4	5
4	2	3	4	5
Blad1

"let us see" means:
please post a sheet with results
use Colo's HTML Maker utility for displaying your Excel Worksheet on the board

On the "Excel Questions" main page there is a link to HTML Maker FAQ. You'll find out how to post correctly.
On the main page of the Forum there is a link to a Test here forum and a link to About this Board - to ask questions about HTLMMaker ...

best regards,
Erik

 

Upvote 0

T

The Wraith

New Member

Joined

Nov 12, 2005

Messages

12

• Nov 22, 2005

• #9

something like this

[HTML removed by Von Pookie]

 

Upvote 0

T

The Wraith

New Member

Joined

Nov 12, 2005

Messages

12

• Nov 22, 2005

• #10

sorry it came over this bad...

but if you look good u see ne next items..

-- L/H u see my list down to 3000 with all the rows off 6 nr's
-- Then a list of 2 colums (where the VBa has to put his calculation of all combinations for 2 nr's in and (X times counted in my list)

same for 3 nr combins -- 4 nrs combins and -- 5 ...

so a VBA that can count the combinations for all 1 to 42 nrs in there combinations with eachother by 2 and 3 and 4 and 5 and then count how many times they apears in my list...

It seems so complex to me that iself can't find a good solution...

i hope you see what i mean...

greetz

 

Upvote 0