Build a table or array based on unqiue values in 2 columns

  • I have some code that creates a pivot table based on a worksheet.

    One of the columns is a person's selling rate, another has their name, another has hours worked. The pivot table end up grouping them by name, with their selling rate multiplied by their hours.

    Problem is, sometimes someone's selling rate has changed during the year. My problem is then that the pivot table still sees this as one person, and either sums, averages or takes a max of their selling rate.

    What I would like to do is perhaps append " (old rate)" to their name if there's a change in selling rate so that when the pivot table is created it sees this as two people and doesn't average/sum/max their selling rate.

    As an example, say my source sheet has:

    Smith, John | 10 | £10.00
    Smith, John | 20 | £10.00

    The pivot table would correctly group this to:

    Smith, John | 30 | £300.00

    However if John Smith's selling rate changed to £15 during the year, my source sheet might say:

    Smith, John | 10 | £15.00
    Smith, John | 20 | £10.00

    In which case, currently, my pivot table (incorrectly) end s up with:

    Smith, John | 30 | £350.00

    What I'd like to see is:

    Smith, John | 10 | £150
    Smith, John (old rate) | 20 | £200


    Also posted here:…-columns.html#post4553823

  • Re: Build a table or array based on unqiue values in 2 columns

    You have the chance in this forum to attach a file which seriously to prepare answer: Can you do !!
    When you will want to merge the data, after 1 year...?
    Why not to declare the new rate for a new user alias Smith John

    Triumph without peril brings no glory: Just try

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!