Prev: Excel 2007 dynamic chart range
Next: If conditions for Data Entry Form
From: amitexcel on 13 Apr 2010 12:59
I need to implement the following logic in excel vba. I will appreciate any
ideas.

Thanks

Problem description -

There are 5 dimensions along which a transfer of goods happens.

Giver (G), giver channel (CH), account number (AC), taker (T), taker channel
(CH)

All of these can and do assume unique values.
Examples – G1, G2, G3 etc, CH1, CH2, CH3 etc and so forth

There can be any combination of these along which a transfer might take place.
Example – A transfer might look like

From G1, through CH3, to T4, through CH2, in account number AC5
So it looks like
G1 – CH3 – AC5 – CH2 – T4 = 100 where 100 is the amount that was transferred
in this transaction

I need to determine what total amount was transferred for each such
combination above. The problem is simple enough if the combinations are all
unique. But when the giver becomes the taker and taker becomes the giver, BUT
the channels and account number remain the same, then the transfer amount
needs to be netted.

Example:
G1 – CH3 – AC5 – CH2 – T4 = 100
T4 – CH3 – AC5 – CH2 – G1 = 10
Then this should be netted as
G1 – CH3 – AC5 – CH2 – T4 = 90


So, I need to identify such unique combinations as
G - CH – AC – CH - T
And sum (net) them.

If there were 5 G, CH, AC and T each, there would be 5x5x5x5x5 = 3125
possible combinations along which a transaction could take place. I need to
identify all those that are the same, AND ALSO, all those where everything
else if the same except the giver and taker have switched places.




From: Gary Brown on 13 Apr 2010 14:03
Instead of VBA, here's a way to do what you want.
First of all, since this stuff looks like intercompany transfer type
transactions, you really want both sides of the transaction.

Assumptions:
- Col A has a combination of 'Acct-Giver Channel-Taker Channel'
ex: AC5-CH3-CH2
- Col B has GIVER
ex: G1
- Col C has TAKER
ex: T4
- Col D has Amount
ex: 100

Somewhere below your 3,125th row, have formulas related to the above data,
such as...
- Col A = A2
- Col B = C2
- Col C = B2
- Col D = -D2

Now you've got both sides of the transaction.

- Create a Pivot Table with a range of A:D
- Row Heading Field(s):
- A-GC-TC
- GIVER
- TAKER
- Data Field(s) -
- Sum of AMT

That will give you a positive 90 for G1-CH3-AC5-CH2-T4 and a negative 90 for
T4-CH3-AC5-CH2-G1.

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"amitexcel" wrote:

> I need to implement the following logic in excel vba. I will appreciate any
> ideas.
>
> Thanks
>
> Problem description -
>
> There are 5 dimensions along which a transfer of goods happens.
>
> Giver (G), giver channel (CH), account number (AC), taker (T), taker channel
> (CH)
>
> All of these can and do assume unique values.
> Examples – G1, G2, G3 etc, CH1, CH2, CH3 etc and so forth
>
> There can be any combination of these along which a transfer might take place.
> Example – A transfer might look like
>
> From G1, through CH3, to T4, through CH2, in account number AC5
> So it looks like
> G1 – CH3 – AC5 – CH2 – T4 = 100 where 100 is the amount that was transferred
> in this transaction
>
> I need to determine what total amount was transferred for each such
> combination above. The problem is simple enough if the combinations are all
> unique. But when the giver becomes the taker and taker becomes the giver, BUT
> the channels and account number remain the same, then the transfer amount
> needs to be netted.
>
> Example:
> G1 – CH3 – AC5 – CH2 – T4 = 100
> T4 – CH3 – AC5 – CH2 – G1 = 10
> Then this should be netted as
> G1 – CH3 – AC5 – CH2 – T4 = 90
>
>
> So, I need to identify such unique combinations as
> G - CH – AC – CH - T
> And sum (net) them.
>
> If there were 5 G, CH, AC and T each, there would be 5x5x5x5x5 = 3125
> possible combinations along which a transaction could take place. I need to
> identify all those that are the same, AND ALSO, all those where everything
> else if the same except the giver and taker have switched places.
>
>
>
>
From: amitexcel on 13 Apr 2010 15:07
Hi Gary,

Thanks for your response to my post on microsoft's VBA forum. The problem is
a bit difficult to articulate without including an excel sheet.

The pivot table you suggested does provide the sums as you indicated. But it
does not help with identifying which giver entities and taker entities are
exchanging roles using the same channel-account-channel combination. Those
entries need to be identified, summed and subtracted. For this every entry
needs to be compared with every other entry to see if of the same
channel-account-channel combinations, any entries have giver and taker in
reversed roles.

Amit

"Gary Brown" wrote:

> Instead of VBA, here's a way to do what you want.
> First of all, since this stuff looks like intercompany transfer type
> transactions, you really want both sides of the transaction.
>
> Assumptions:
> - Col A has a combination of 'Acct-Giver Channel-Taker Channel'
> ex: AC5-CH3-CH2
> - Col B has GIVER
> ex: G1
> - Col C has TAKER
> ex: T4
> - Col D has Amount
> ex: 100
>
> Somewhere below your 3,125th row, have formulas related to the above data,
> such as...
> - Col A = A2
> - Col B = C2
> - Col C = B2
> - Col D = -D2
>
> Now you've got both sides of the transaction.
>
> - Create a Pivot Table with a range of A:D
> - Row Heading Field(s):
> - A-GC-TC
> - GIVER
> - TAKER
> - Data Field(s) -
> - Sum of AMT
>
> That will give you a positive 90 for G1-CH3-AC5-CH2-T4 and a negative 90 for
> T4-CH3-AC5-CH2-G1.
>
> --
> Hope this helps.
> If it does, please click the Yes button.
> Thanks in advance for your feedback.
> Gary Brown
>
>
>
> "amitexcel" wrote:
>
> > I need to implement the following logic in excel vba. I will appreciate any
> > ideas.
> >
> > Thanks
> >
> > Problem description -
> >
> > There are 5 dimensions along which a transfer of goods happens.
> >
> > Giver (G), giver channel (CH), account number (AC), taker (T), taker channel
> > (CH)
> >
> > All of these can and do assume unique values.
> > Examples – G1, G2, G3 etc, CH1, CH2, CH3 etc and so forth
> >
> > There can be any combination of these along which a transfer might take place.
> > Example – A transfer might look like
> >
> > From G1, through CH3, to T4, through CH2, in account number AC5
> > So it looks like
> > G1 – CH3 – AC5 – CH2 – T4 = 100 where 100 is the amount that was transferred
> > in this transaction
> >
> > I need to determine what total amount was transferred for each such
> > combination above. The problem is simple enough if the combinations are all
> > unique. But when the giver becomes the taker and taker becomes the giver, BUT
> > the channels and account number remain the same, then the transfer amount
> > needs to be netted.
> >
> > Example:
> > G1 – CH3 – AC5 – CH2 – T4 = 100
> > T4 – CH3 – AC5 – CH2 – G1 = 10
> > Then this should be netted as
> > G1 – CH3 – AC5 – CH2 – T4 = 90
> >
> >
> > So, I need to identify such unique combinations as
> > G - CH – AC – CH - T
> > And sum (net) them.
> >
> > If there were 5 G, CH, AC and T each, there would be 5x5x5x5x5 = 3125
> > possible combinations along which a transaction could take place. I need to
> > identify all those that are the same, AND ALSO, all those where everything
> > else if the same except the giver and taker have switched places.
> >
> >
> >
> >
From: JLatham on 13 Apr 2010 15:43
My assumptions:
Column A = Giver ID
Column B = Giver Channel
Column C = Account
Column D = Taker Channel
Column E = Taker ID
Column F = Amount
Entries start on row 2 and go down to row 5000, and I use 5000 below, but
you can make that a larger number to keep from having to constantly modify
the formulas at the cost of a tiny bit of time added to perform the
calculations.

On the same sheet (could be set up on a different sheet) you have the 5
column labels repeated beginning at I1 and will put an entry related to the
label below them on row 2
Then you can use a SUMPRODUCT() formula to get both sides of the value of a
transfer
These are going to get long, just remember that when you actually enter
them, they go into a cell as one continuous entry:
=SUMPRODUCT(--(A2:A5000=I2),--(B2:B5000=J2),--(C2:C5000=K2),--(D2:D5000=L2),--(E2:E5000=M2),(F2:F5000))
given G1-CH3-AC5-CH2-T4 then that formula gives you your 100 result.

To go the other way:
=SUMPRODUCT(--(A2:A5000=M2),--(B2:B5000=J2),--(C2:C5000=K2),--(D2:D5000=L2),--(E2:E5000=I2),(F2:F5000))
and that should give you the 10 result.

If those two formulas were in I3 and I4 then in I5 you could put:
=I3-I4 to get the net.

Or even combine them up in one cell as:
=SUMPRODUCT(--(A2:A5000=I2),--(B2:B5000=J2),--(C2:C5000=K2),--(D2:D5000=L2),--(E2:E5000=M2),(F2:F5000))
-
SUMPRODUCT(--(A2:A5000=M2),--(B2:B5000=J2),--(C2:C5000=K2),--(D2:D5000=L2),--(E2:E5000=I2),(F2:F5000))



"amitexcel" wrote:

> I need to implement the following logic in excel vba. I will appreciate any
> ideas.
>
> Thanks
>
> Problem description -
>
> There are 5 dimensions along which a transfer of goods happens.
>
> Giver (G), giver channel (CH), account number (AC), taker (T), taker channel
> (CH)
>
> All of these can and do assume unique values.
> Examples – G1, G2, G3 etc, CH1, CH2, CH3 etc and so forth
>
> There can be any combination of these along which a transfer might take place.
> Example – A transfer might look like
>
> From G1, through CH3, to T4, through CH2, in account number AC5
> So it looks like
> G1 – CH3 – AC5 – CH2 – T4 = 100 where 100 is the amount that was transferred
> in this transaction
>
> I need to determine what total amount was transferred for each such
> combination above. The problem is simple enough if the combinations are all
> unique. But when the giver becomes the taker and taker becomes the giver, BUT
> the channels and account number remain the same, then the transfer amount
> needs to be netted.
>
> Example:
> G1 – CH3 – AC5 – CH2 – T4 = 100
> T4 – CH3 – AC5 – CH2 – G1 = 10
> Then this should be netted as
> G1 – CH3 – AC5 – CH2 – T4 = 90
>
>
> So, I need to identify such unique combinations as
> G - CH – AC – CH - T
> And sum (net) them.
>
> If there were 5 G, CH, AC and T each, there would be 5x5x5x5x5 = 3125
> possible combinations along which a transaction could take place. I need to
> identify all those that are the same, AND ALSO, all those where everything
> else if the same except the giver and taker have switched places.
>
>
>
>
From: amitexcel on 13 Apr 2010 16:50
Hi J.

Thank you for your response. It must be something simple that I am missing,
but I am not very clear on how to implement this.

• The data goes in columns A through F. In cells I1 thru M1, data labels are
inserted. What goes in I2 (J2, …)
• Since none but the amount column have any numbers in them, how will the
SUMPRODUCT formula be implemented, or calculate anything?
• I have not seen the SUMPRODUCT formula written this way before. I might
just be inexperienced, but if you can clarify, I will appreciate.

Thanks


"JLatham" wrote:

> My assumptions:
> Column A = Giver ID
> Column B = Giver Channel
> Column C = Account
> Column D = Taker Channel
> Column E = Taker ID
> Column F = Amount
> Entries start on row 2 and go down to row 5000, and I use 5000 below, but
> you can make that a larger number to keep from having to constantly modify
> the formulas at the cost of a tiny bit of time added to perform the
> calculations.
>
> On the same sheet (could be set up on a different sheet) you have the 5
> column labels repeated beginning at I1 and will put an entry related to the
> label below them on row 2
> Then you can use a SUMPRODUCT() formula to get both sides of the value of a
> transfer
> These are going to get long, just remember that when you actually enter
> them, they go into a cell as one continuous entry:
> =SUMPRODUCT(--(A2:A5000=I2),--(B2:B5000=J2),--(C2:C5000=K2),--(D2:D5000=L2),--(E2:E5000=M2),(F2:F5000))
> given G1-CH3-AC5-CH2-T4 then that formula gives you your 100 result.
>
> To go the other way:
> =SUMPRODUCT(--(A2:A5000=M2),--(B2:B5000=J2),--(C2:C5000=K2),--(D2:D5000=L2),--(E2:E5000=I2),(F2:F5000))
> and that should give you the 10 result.
>
> If those two formulas were in I3 and I4 then in I5 you could put:
> =I3-I4 to get the net.
>
> Or even combine them up in one cell as:
> =SUMPRODUCT(--(A2:A5000=I2),--(B2:B5000=J2),--(C2:C5000=K2),--(D2:D5000=L2),--(E2:E5000=M2),(F2:F5000))
> -
> SUMPRODUCT(--(A2:A5000=M2),--(B2:B5000=J2),--(C2:C5000=K2),--(D2:D5000=L2),--(E2:E5000=I2),(F2:F5000))
>
>
>
> "amitexcel" wrote:
>
> > I need to implement the following logic in excel vba. I will appreciate any
> > ideas.
> >
> > Thanks
> >
> > Problem description -
> >
> > There are 5 dimensions along which a transfer of goods happens.
> >
> > Giver (G), giver channel (CH), account number (AC), taker (T), taker channel
> > (CH)
> >
> > All of these can and do assume unique values.
> > Examples – G1, G2, G3 etc, CH1, CH2, CH3 etc and so forth
> >
> > There can be any combination of these along which a transfer might take place.
> > Example – A transfer might look like
> >
> > From G1, through CH3, to T4, through CH2, in account number AC5
> > So it looks like
> > G1 – CH3 – AC5 – CH2 – T4 = 100 where 100 is the amount that was transferred
> > in this transaction
> >
> > I need to determine what total amount was transferred for each such
> > combination above. The problem is simple enough if the combinations are all
> > unique. But when the giver becomes the taker and taker becomes the giver, BUT
> > the channels and account number remain the same, then the transfer amount
> > needs to be netted.
> >
> > Example:
> > G1 – CH3 – AC5 – CH2 – T4 = 100
> > T4 – CH3 – AC5 – CH2 – G1 = 10
> > Then this should be netted as
> > G1 – CH3 – AC5 – CH2 – T4 = 90
> >
> >
> > So, I need to identify such unique combinations as
> > G - CH – AC – CH - T
> > And sum (net) them.
> >
> > If there were 5 G, CH, AC and T each, there would be 5x5x5x5x5 = 3125
> > possible combinations along which a transaction could take place. I need to
> > identify all those that are the same, AND ALSO, all those where everything
> > else if the same except the giver and taker have switched places.
> >
> >
> >
> >
 |  Next  |  Last
Pages: 1 2 3
Prev: Excel 2007 dynamic chart range
Next: If conditions for Data Entry Form