Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL Macro problem
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL Macro problem
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL Macro problem
Hi Gary,
Thanks for your response to my post on microsofts 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL Macro problem
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL Macro problem
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL Macro problem
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL Macro problem
Thank you so much. Could you also post a brief description of how this works?
"Herbert Seidenberg" wrote: Excel 2007 PivotTable Ditto Gary http://c0718892.cdn.cloudfiles.racks.../04_13_10.xlsx . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL Macro problem
I am not sure what this formula is doing, and how this range [G] is defined.
IF(COUNTIFS([G],[T],[T],[G],[CHA],[CHA],[AC],[AC],[CHB],[CHB])*(LEFT([G])="T"),"G"&RIGHT([G]),[G]) "Herbert Seidenberg" wrote: Excel 2007 PivotTable Ditto Gary http://c0718892.cdn.cloudfiles.racks.../04_13_10.xlsx . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL Macro problem
Excel 2007 Pivot Table
Added help page and notes. http://c0718892.cdn.cloudfiles.racks.../04_13_10.xlsx Pdf preview: http://c0718892.cdn.cloudfiles.racks...m/04_13_10.pdf |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL Macro problem
I think that I got some of the mechanics of it.
What you created is a TABLE in Excel 2007. And you used the data to create three more columns in the TABLE, namely GS, TS, and AmtS. What I did not get is the logic of creating GS and TS. IF(COUNTIFS([G],[T],[T],[G],[CHA],[CHA],[AC],[AC],[CHB],[CHB])*(LEFT([G])="T"),"G"&RIGHT([G]),[G]) What is this part of the formula doing? COUNTIFS([G],[T],[T],[G],[CHA],[CHA],[AC],[AC],[CHB],[CHB]) This is my interpretation of it. Taking any particular row, if - [G],[T] = the value in G is the same as in T [T],[G] = the value in T is the same as in G [CHA], [CHA] = the value in CHA is the same as in CHA [AC], [AC] = the value in AC is the same as in AC [CHB], [CHB] = the value in CHB is the same as in CHB This interpretation cannot be right, because if the first statement is true, then the second must also be true. And statements 3, 4, and 5 are self-evidently true. Lastly, this part of the formula yields a number 0, 1, 2 ¦ The second part of this expression - *(LEFT([G])="T") yields a yes or no. What is their product? And how does that drive the rest of the formula? I am just trying to understand the conceptual logic. Thank you. "Herbert Seidenberg" wrote: Excel 2007 Pivot Table Added help page and notes. http://c0718892.cdn.cloudfiles.racks.../04_13_10.xlsx Pdf preview: http://c0718892.cdn.cloudfiles.racks...m/04_13_10.pdf . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL Macro problem
Excel 2007
All your questions were addressed in my updated files. In case you had trouble downloading from the cloud, here is an alternate site: http://www.mediafire.com/file/zzai4thczat/04_13_10.xlsx http://www.mediafire.com/file/knzkwwz2izn/04_13_10.pdf |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro problem in Excel | Excel Discussion (Misc queries) | |||
Problem in excel macro | Excel Programming | |||
excel macro problem | Excel Programming | |||
excel macro problem | Excel Programming | |||
Excel 2k3 Macro Problem | Excel Programming |