Hi all,
I am doing an Economics project at college and have come to a complete dead end on a matrix that I am trying to create in Excel 2010. Hopefullysomeone can help me out. The hardest part is probably going to be to describeclearly what I am looking for, I’ll try my best but if anyone needs any moreinfo I’d be very happy to provide.
I’m looking at a theoretical problem where I have a numberof customers and a number of products. Some own a certain product which theyare willing to lend and some need the product and would like to borrow it. Someproducts that borrowers are looking for will be held by multiple clients, someby only one, some by no one at all. There may be no one looking to borrow a productbut there may be many potential borrowers.
If multiple customers hold a product, they would lend inproportion to how much they advertised as having to lend. Ie, if one lender has10 of product ‘aaa’ and the other has 100 but other customers only want toborrow 50, then the first lender would lend 5 and the second would lend 45.
If alternatively, one lender has 10 of product ‘bbb’ and theother has 100 but another customer wants to borrow 500, then both lenders wouldlend all of their supply, but the borrower would only be able to borrow 110 outof the 500 they had demand for.
I want to create a Matrix which shows how much (totalledacross all the products) each customer is lending to and borrowing from eachother customer (I have called my customers, ‘ABC’, ‘DEF’ ‘GHI’ etc). Im lookingfor something like the below. To add some numbers for illustration, let’s saythat in total, customer ABC lent 2 of product ‘aaa’ to DEF and 3 of ‘aaa’ toGHI. Customer ABC also lent 5 of product bbb to DEF (so in total they werelending DEF 7 across the two products). DEF lends 10 of product ccc to ABC. GHI lent4 of ‘aaa’ to DEF. GHI doesn’t lend ABC anything and DEF doesn’t lend GHI anything.
<tbody>
</tbody>
Here is some sample data that shows the sort of scenariosand how I have got the data presented to me.
<tbody>
</tbody><strike></strike>
My full data set will be quite large (probably around 1,000 productsand 50 customers). I am happy to use formulas or VBA code dependent on whatworks. It looks like this should be solvable using pivot tables but I have not beenable to crack it. I’m hoping someone here is more successful than me!
Many thanks
I am doing an Economics project at college and have come to a complete dead end on a matrix that I am trying to create in Excel 2010. Hopefullysomeone can help me out. The hardest part is probably going to be to describeclearly what I am looking for, I’ll try my best but if anyone needs any moreinfo I’d be very happy to provide.
I’m looking at a theoretical problem where I have a numberof customers and a number of products. Some own a certain product which theyare willing to lend and some need the product and would like to borrow it. Someproducts that borrowers are looking for will be held by multiple clients, someby only one, some by no one at all. There may be no one looking to borrow a productbut there may be many potential borrowers.
If multiple customers hold a product, they would lend inproportion to how much they advertised as having to lend. Ie, if one lender has10 of product ‘aaa’ and the other has 100 but other customers only want toborrow 50, then the first lender would lend 5 and the second would lend 45.
If alternatively, one lender has 10 of product ‘bbb’ and theother has 100 but another customer wants to borrow 500, then both lenders wouldlend all of their supply, but the borrower would only be able to borrow 110 outof the 500 they had demand for.
I want to create a Matrix which shows how much (totalledacross all the products) each customer is lending to and borrowing from eachother customer (I have called my customers, ‘ABC’, ‘DEF’ ‘GHI’ etc). Im lookingfor something like the below. To add some numbers for illustration, let’s saythat in total, customer ABC lent 2 of product ‘aaa’ to DEF and 3 of ‘aaa’ toGHI. Customer ABC also lent 5 of product bbb to DEF (so in total they werelending DEF 7 across the two products). DEF lends 10 of product ccc to ABC. GHI lent4 of ‘aaa’ to DEF. GHI doesn’t lend ABC anything and DEF doesn’t lend GHI anything.
| Lends | ||||
| ABC | DEF | GHI | ||
| Borrows | ABC | x | 10 | 0 |
| DEF | 7 | x | 4 | |
| GHI | 3 | 0 | x |
<tbody>
</tbody>
Here is some sample data that shows the sort of scenariosand how I have got the data presented to me.
<tbody> </tbody> |
<tbody> </tbody> | =IFERROR(IF(E3>-F3,(F3*H3),(C3)),"") | =IFERROR(IF(E3>-F3,(D3),(E3*I3)),"") | |||||||||
| Product | Customer | Supply to lend | Demand to Borrow | Total to lend by product | Total needed by product | Lenders > borrowers | Proportion of supply lent | Proportion of demand met | Amount lent | Amount borrowed | ||
| aaa | ABC | 850,000 | 850,000 | - | TRUE | 0.00 | - | - | ||||
| bbb | GHI | 10 | 110 | - 50 | TRUE | 0.09 | - | - 5 | - | |||
| bbb | DEF | 100 | 110 | - 50 | TRUE | 0.91 | - | - 45 | - | |||
| bbb | ABC | 50 | 110 | - 50 | TRUE | - | - 1.00 | - | 50 | |||
| ccc | GHI | 450,000 | 450,000 | - 15,000 | TRUE | 1.00 | - | - 15,000 | - | |||
| ccc | ABC | 15,000 | 450,000 | - 15,000 | TRUE | - | - 1.00 | - | 15,000 | |||
| ddd | GHI | 100,000 | - 100,000 | FALSE | - 0.00 | - | - | |||||
| eee | ABC | 1,000,000 | 1,000,000 | - 2,000,000 | FALSE | 1.00 | - | 1,000,000 | - | |||
| eee | DEF | 2,000,000 | 1,000,000 | - 2,000,000 | FALSE | - | - 1.00 | - | - 1,000,000 | |||
| fff | ABC | 10,000 | 210,000 | - | TRUE | 0.00 | - | - | ||||
| fff | MNO | 200,000 | 210,000 | - | TRUE | 0.00 | - | - | ||||
| ggg | DEF | 100,000 | - 600,000 | FALSE | - 0.17 | - | - | |||||
| ggg | GHI | 500,000 | - 600,000 | FALSE | - 0.83 | - | - | |||||
| hhh | DEF | 10,000 | 160,000 | - 3,000,000 | FALSE | 0.06 | - | 10,000 | - | |||
| hhh | ABC | 3,000,000 | 160,000 | - 3,000,000 | FALSE | - | - 1.00 | - | - 160,000 | |||
| hhh | GHI | 150,000 | 160,000 | - 3,000,000 | FALSE | 0.94 | - | 150,000 | - | |||
| iii | ABC | 75,000 | 550,000 | - 1,375,000 | FALSE | - | - 0.05 | - | - 30,000 | |||
| iii | DEF | 50,000 | 550,000 | - 1,375,000 | FALSE | 0.09 | - | 50,000 | - | |||
| iii | GHI | 1,000,000 | 550,000 | - 1,375,000 | FALSE | - | - 0.73 | - | - 400,000 | |||
| iii | JKL | 500,000 | 550,000 | - 1,375,000 | FALSE | 0.91 | - | 500,000 | - | |||
| iii | MNO | 300,000 | 550,000 | - 1,375,000 | FALSE | - | - 0.22 | - | - 120,000 |
<tbody>
</tbody>
My full data set will be quite large (probably around 1,000 productsand 50 customers). I am happy to use formulas or VBA code dependent on whatworks. It looks like this should be solvable using pivot tables but I have not beenable to crack it. I’m hoping someone here is more successful than me!
Many thanks
