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

[email protected] 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

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:
> [email protected] 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

Anybody have a couple of minutes to help me out on this, bit of a
beginner over here


[email protected] 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:
> > [email protected] 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