One to many to many formulae

[pierre robinson]

Afternoon all.

The question I have relates to what I believe is a many to many formula - and I accept that Excel may not be able to cope with this.

What I have is a column of addresses, which include single homes and flats. They are unique.
A column of Com identifiers - these are council issued identifiers - they arent entirely unique
A column of water meter numbers - again, not unique.

What I need is a formula that counts the number of meters, per COM number, per address.
Any thoughts greatly appreciated as 3 coffees down & I havent solved it yet...

Address	COM	Meter
20 LYNDALE PL	COM10001	A10A642062
1/27 ALLENBY RD	COM10055	X16A654364
2/27 ALLENBY RD	COM10055	X16A654364
3/27 ALLENBY RD	COM10055	X16A654364
1/29 KINGS RD	COM10056	A07A001002
2/29 KINGS RD	COM10056	A07A001131
3/29 KINGS RD	COM10056	A07A017287
1A KAPUNI RD	COM10057	A88014011
1B KAPUNI RD	COM10057	A88014012
21 CAEN RD	COM10058	A06A145484

<tbody>
[TD="class: xl66"]Example[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: center"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][TABLE="width: 425"]
<tbody>[TR]

</tbody>

[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]TIA[/TD]
[/TR]
</tbody>[/TABLE]

 

[BrianJN1]

Ultimately then, do you want to create a report such as:

Meters COM Addresses
1...... COM10001 ......1
1...... COM10055...... 3
3...... COM10056...... 3
2 ...... COM10057......2
1...... COM10058...... 1...... ?

This would seem to require VBA but I'm not sure where to start.

 

[inactiveuserps07]

Pierre, what would the desired result set look like for the sample data given above? Was BrianJN1's understanding correct?

 

[pierre robinson]

OK, so my colleague has posed the problem to me a different way: he is struggling with the formula to calculate the number of meters, apparently. He wants to include this in a larger formula. Hopefully the below makes sense?

Meters	COM	Address	# of meters
X123	COMAB	A	2
X567	COMAB	B	2
X123	COMAB	C	2
X123	COMAB	D	2
X345	COMBZ	E	3
X345	COMBZ	F	3
X789	COMBZ	G	3
X901	COMBZ	H	3
X565	COMJA	I	1

<tbody>
</tbody>

 

[konew1]

Hi Pierre
Posting #4 is not very helpful because you have used different data to the initial posting.
Using the data layout form your first posting and assuming it is located from A1, and (as I understand it) you want to count the number of different meters that exist at each COM reference
then, in D2
= COUNTIFS($B$2:$B$11,B2,$C$2:$C$11,"<>"&C2)+1
and copy down.

 

[pierre robinson]

Kia ora konew1.

You have to make allowances for me being a Dorklander. I'll bounce your response off my colleague - I'm just the messenger here - and see what he thinks. He's an accountant. Nuff said...