Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cross reference summation and offset <- sorry can't think of better way to describe!
Hi - its kind of hard to explain this one in a subject.
Basically I need some help to write a macro to perform the following pls: Data is in following format: Account Ref Cost CrossRef Comment ------- --- ---- -------- -------- 1 QTIM 100 Football 1 QTIM 100 Football 2 Football -200 QTIM 1 QPHIL 2000 Chocolate 2 Chocolate -1000 QPHIL 2 Chocolate -2000 QPHIL 1 QPETE -500 Horse 1 QPETE 500 Horse What I am looking to do is to identify where a unique ref's total cost (e.g. QTIM's total 200) is offset by corresponding equal and opposite entry that matches its crossRef and is on account other than its own (there are only 2 accounts). For example QTIM has total cost of 200 with crossref Football (all crossrefs for QTIM will always be Football- it is unique to the ref). There is a football entry on account 2 that offsets the 200 with -200 making it zero. I'd like to indentify this and copy something similar to another sheet and add a constant comment (e.g. "Clear") to the original worksheet. Acc Ref TotalCost OffsetAcc OffsetRef OffsetCost 1 QTIM 200 2 Football -200 Not entirely sure the best practice to get this done efficiently. I have made a script that so far sorts the data in Ref order so that all refs are next to each other. Any help, direction would be very much appreciated. Thanks Will |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cross reference summation and offset <- sorry can't think of better way to describe!
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cross reference summation and offset <- sorry can't think of better way to describe!
Anybody have a couple of minutes to help me out on this, bit of a
beginner over here :) wrote: Thanks - so I get to the stage where I have two dynamically created worksheets each with entries for each account. How do I go about using SUMIF to get each ref's total? Franz Verga wrote: wrote: Hi - its kind of hard to explain this one in a subject. Basically I need some help to write a macro to perform the following pls: Data is in following format: Account Ref Cost CrossRef Comment ------- --- ---- -------- -------- 1 QTIM 100 Football 1 QTIM 100 Football 2 Football -200 QTIM 1 QPHIL 2000 Chocolate 2 Chocolate -1000 QPHIL 2 Chocolate -2000 QPHIL 1 QPETE -500 Horse 1 QPETE 500 Horse What I am looking to do is to identify where a unique ref's total cost (e.g. QTIM's total 200) is offset by corresponding equal and opposite entry that matches its crossRef and is on account other than its own (there are only 2 accounts). For example QTIM has total cost of 200 with crossref Football (all crossrefs for QTIM will always be Football- it is unique to the ref). There is a football entry on account 2 that offsets the 200 with -200 making it zero. I'd like to indentify this and copy something similar to another sheet and add a constant comment (e.g. "Clear") to the original worksheet. Acc Ref TotalCost OffsetAcc OffsetRef OffsetCost 1 QTIM 200 2 Football -200 Not entirely sure the best practice to get this done efficiently. I have made a script that so far sorts the data in Ref order so that all refs are next to each other. Any help, direction would be very much appreciated. Thanks Will Hi Will, I think the best way should be split the rows of the two accounts on to two separate sheets, then with SUMIF you can sum on the two sheets and then with VLOOKUP you can check the crossreferences and their sums... -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cross Reference | Excel Worksheet Functions | |||
cross reference | New Users to Excel | |||
Cross reference | Excel Discussion (Misc queries) | |||
cross reference two | New Users to Excel | |||
Cross reference | Excel Worksheet Functions |