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

Example
		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> </tbody>
		TIA

<tbody>
</tbody>

 

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