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
--
Rob Gaffney

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



On Mar 10, 9:33*pm, Gaffnr wrote:
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
--
Rob Gaffney

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
--
Rob Gaffney


"Pete_UK" wrote:

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



On Mar 10, 9:33 pm, Gaffnr wrote:
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
--
Rob Gaffney

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 ??
--
Rob Gaffney


"Pete_UK" wrote:

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



On Mar 10, 9:33 pm, Gaffnr wrote:
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
--
Rob Gaffney

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

On Mar 11, 8:03*am, Gaffnr wrote:
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 ??
--
Rob Gaffney



"Pete_UK" wrote:
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

On Mar 10, 9:33 pm, Gaffnr wrote:
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
--
Rob Gaffney- Hide quoted text -

- Show quoted text -