+ Reply to Thread
Results 1 to 4 of 4

Cross reference summation and offset <- sorry can't think of better way to describe!

  1. #1
    TheWJB@gmail.com
    Guest

    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. #2
    Franz Verga
    Guest

    Re: Cross reference summation and offset <- sorry can't think of better way to describe!

    TheWJB@gmail.com 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



  3. #3
    TheWJB@gmail.com
    Guest

    Re: Cross reference summation and offset <- sorry can't think of better way to describe!

    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:
    > TheWJB@gmail.com 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



  4. #4
    TheWJB@gmail.com
    Guest

    Re: 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


    TheWJB@gmail.com 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:
    > > TheWJB@gmail.com 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1