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

[gers1978]

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

Thanks!

Also posted here:

http://www.ozgrid.com/forum/showthread.php?t=200192&p=772680#post772680

 

[Mike Szczesny]

Can you add a date field to distinguish a specific entry?

 

[gers1978]

Can you add a date field to distinguish a specific entry?

There's plenty fields I can I use to differentiate (in fact I can use the selling rate itself), my problem is I don't know how/where to make the differentiation.

 

[Mike Szczesny]

If it the rate changes each day, then I would use a day field. If it change during a given day, I would use a time field (hr/min). That should differentiate a specific rate. Any field making it unique will essentially work

 

[gers1978]

If it the rate changes each day, then I would use a day field. If it change during a given day, I would use a time field (hr/min). That should differentiate a specific rate. Any field making it unique will essentially work

Change it WHERE though?

 

[Mike Szczesny]

If the rate changes at a specific point in time use the time function (hr/min. If it changes each date use a regular day. Both each are specifically unique when they are converted to a number format. So use either date or time function for a specific rate occurance (transaction). Hope this helps??

Excel 2012
	A	B	C	D	E
1	0.52084	12:30 PM		42538	6/17/2016
2	0.52154	12:31 PM		42539	6/18/2016
Sheet1

Cell Formulas
Range	Formula
A1	=TIME(12,30,1)
B1	=TIME(12,30,1)