Matrix Help

[Gaffnr]

I need some help to construct a matrix that shows people owing money to each
other. In my simple example below, i have

Rob Rachael £100.00
Rob Dennis £200.00
Rachael Rob -£100.00
Rachael Dennis £300.00
Dennis Rob -£200.00
Dennis Rachael -£300.00

I can easily build a matrix pivot table that shows column A in my rows and
column B in my columns and of course amount in the data area. This will show
that rob owes rob zero, is owed £200 by dennis and £100 by rachael etc. This
will repeat for each name.

However, on a large matrix (say 70 rows and 70 columns) I need to devise a
way to show me that Rob is owed £100 by Rachael on the row for Rob, and
Rachael owes £100 to Rob, thus the net effect is zero. the reality is of
course that I could manually tick them off but surely there is a way to
formulise this? I think the answer may lay in arrays but I've never used
these and wouldnt know where to start.
Thanks indeedy to anybody that could help. I would attach a dummy copy of
data to show where I am but I there is no way to do this on this site that i
know of.
Rob

 

[Pete_UK]

Imagine you have your names starting in cell A2 going down:

Alan
Barry
Colin
David
Eddie
Frank

You can use <copy>, then move cursor to B1 and Edit | Paste Special |
Transpose (check) | OK and <Esc> to get these in row 1 starting from
B1. The names down column A represent people who owe money and the
names across are people who are owed money. Put this formula in B2:

=SUMPRODUCT(($A2=$Q$1:$Q$34)*(B$1=$R$1:$R$34)*($S$1:$S$34))-
SUMPRODUCT((B$1=$Q$1:$Q$34)*($A2=$R$1:$R$34)*($S$1:$S$34))

This can be copied across the row and then the block of formulae
copied down to complete the matrix - it will show the net amount owing/
owed from a list like this:

Alan Barry 100
Alan David 150
Alan Frank 50
Colin David 100
Barry Alan 100

with the first name in column Q, the second name in column R and the
amount in column S. I set up my test over 34 rows, but you should
adjust this in the formula if you have more.

Is this what you meant?

Hope this helps.

Pete

 

[Gaffnr]

Hi Pete
Thanks for the reply. This is not really what im looking for. In my
theory, what Rob owes Pete, say £100, should also show as Pete is owed by Rob
as -£100.
This means each debt relationship should net to zero. I can produce a table
using a pivot (or your method which I didnt know about - thanks) however, i
need a formula that checks that each debt relationship does net to zero. Any
that dont I could then investigate. On your example, I can see Alan vs Barry
and Barry Vs Alan but I cant see anyway the compares the results of these and
shows the net result.
Rob

 

[Gaffnr]

Hi Pete
Ignore me - your formula works a treat. I just hope the formula can cope
with a table that will be upto 70 arrays ??

 

[Pete_UK]

You're welcome, Rob - thanks for feeding back.

It will work for 70 by 70 names, although it might be a bit slow to
calculate initially.

Pete