Formula: Count of Unique 'Afhaalpunt' for this 'Artikel_Nr'

[Roy_Excel_Island_Apps]

Hi, can someone help me to find the formula that I can fill out in column C to count all unique Shop_ID's per Article_ID?

	A	B	C
1	Shop_ID	Article_ID	Count of Unique Shop_ID's for the linked Article_ID
2	205	15789	Result has to be '4'
3	55	43187
4	206	15789
5	205	15789	(This is a double one with the first record, so don't count it)
6	55	43187
7	44	43187
8	55	15789
9	44	43187
10	95	15789

<tbody>
</tbody>

Thanks !

Roy

 

[Aladin Akyurek]

In C2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($B$2:$B$10=$B2,$A$2:$A$10),$A$2:$A$10),1))

 

[Roy_Excel_Island_Apps]

In C2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($B$2:$B$10=$B2,$A$2:$A$10),$A$2:$A$10),1))

Thanks Aladin! Sheet is calculating Keep you posted!

 

[Aladin Akyurek]

Thanks Aladin! Sheet is calculating Keep you posted!

Do you have very large rages to calculate?

 

[Roy_Excel_Island_Apps]

Do you have very large rages to calculate?

Yes, Aladin, that's why I changed your formula to: {=SUM(IF(FREQUENCY(IF([Article_ID]=$L2;[Shop_ID]);[Shop_ID]);1))}
This seems to be working but needs a lot of time to calculate and recalculate on sheet change.

Anyway thanks for the great help!

Regards, Roy

 

[Aladin Akyurek]

Yes, Aladin, that's why I changed your formula to: {=SUM(IF(FREQUENCY(IF([Article_ID]=$L2;[Shop_ID]);[Shop_ID]);1))}
This seems to be working but needs a lot of time to calculate and recalculate on sheet change.

Anyway thanks for the great help!

Regards, Roy

How big is the range of Article_ID? Is $L2 part of it?

 

[Roy_Excel_Island_Apps]

How big is the range of Article_ID? Is $L2 part of it?

The range is like 30.000 records. $L2 is part of the article ID, yes.

 

[Aladin Akyurek]

The range is like 30.000 records. $L2 is part of the article ID, yes.

Using the earlier layout, we could also have the following: In C2 control+shift+enter, not just enter, and copy down...

=IF(ISNUMBER(MATCH($B2,$B$1:B1,0)),VLOOKUP($B2,$B$1:C1,2,0),SUM(IF(FREQUENCY(IF($B$2:$B$10=$B2,$A$2:$A$10),$A$2:$A$10),1)))

Is this faster?

 

[Roy_Excel_Island_Apps]

Using the earlier layout, we could also have the following: In C2 control+shift+enter, not just enter, and copy down...

=IF(ISNUMBER(MATCH($B2,$B$1:B1,0)),VLOOKUP($B2,$B$1:C1,2,0),SUM(IF(FREQUENCY(IF($B$2:$B$10=$B2,$A$2:$A$10),$A$2:$A$10),1)))

Is this faster?

I replaced all ranges by the column ranges and executed the formula but the file crashes. Maybe I should add a procedure it to my VBA coding...

 

[Aladin Akyurek]

I replaced all ranges by the column ranges and executed the formula but the file crashes. Maybe I should add a procedure it to my VBA coding...

We should avoid whole column references for reasonsof efficiency. Perhaps also avoid distinct countinting of shops for the same article many times. What follows is a suggestion in that direction.

Book1
	A	B	C	D	E	F
1	Shop_ID	Article_ID	0		2
2	205	15789	1		article	unique shop count
3	55	43187	2		15789	4
4	206	15789			43187	2
5	205	15789
6	55	43187
7	44	43187
8	55	15789
9	44	43187
10	95	15789
11
Sheet1

1. Define bignum in the Name Manager as referring to:

=9.99999999999999E+307

2. Define Shop_ID in the Name Manager as referring to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(bignum,Sheet1!$A:$A))

3. Define Article_ID in the Name Manager as referring to:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,MATCH(bignum,Sheet1!$A:$A))

Adjust the sheet name to suit in the foregoing formulas.

4. In C1 enter a 0 (mandatory).

5. In C2 just enter and copy down:

=IF(ISNUMBER(MATCH(B2,$B$1:B1,0)),"",LOOKUP(bignum,$C$1:C1)+1)

6. In E1 just enter:

=LOOKUP(bignum,C:C)

7. In E3 just enter and copy down:

=IF(ROWS($E$3:E3)>$E$1,"",LOOKUP(ROWS($E$3:E3),C:C,B:B))

8. Finally, in F3 control+shift+enter, not just enter, and copy down:

=IF($E3="","",SUM(IF(FREQUENCY(IF(Article_ID=$E3,Shop_ID),Shop_ID),1 )))

I expect this set up to be much faster.