Findling last date a product was sold in Excel

I have a spreadsheet with about 500K lines that has customer number, product number, invoice date, price, etc. for all products that our company has sold in the past 2 years.  Some products have only been sold one time to a customer but most have been sold many times.

Here is an example:

``````Cust#	Product#  	Date Sold	Price
5672	100WX202	05/01/13	\$155.20
234123	550MP200X20	06/16/12	\$12.60
2356	663405T12	01/04/13	\$45.60
51190	100WX202	07/11/13	\$124.70
5672	100WX202	03/01/14	\$142.10
234123	550MP200X20	06/16/14	\$12.60
``````
I need a new spreadsheet showing what the last price each customer paid for each product they purchased from us.  Here is what I would need from the above example:

``````Cust#	Product#  	Date Sold	Price
5672	100WX202	03/01/14	\$142.10
234123	550MP200X20	06/16/14	\$12.60
51190	100WX202	07/11/13	\$124.70
``````

Any help would be appreciated.

Mark
Who is Participating?

x

Commented:
In the attached file, I'm taking your data, copying columns A and B to another part of the sheet (or another sheet), use Date\Remove duplicates to only get unique values, then use two array formulas (entered with Ctrl+Shift+Enter instead of Enter) as follows:

for last date
=MAX((\$A\$2:\$A\$7=H2)*(\$B\$2:\$B\$7=I2)*\$C\$2:\$C\$7)

for price
=INDEX(\$D\$2:\$D\$7,MATCH(H2&I2&J2,\$A\$2:\$A\$7&\$B\$2:\$B\$7&\$C\$2:\$C\$7,0))

For 500klines, it's going to get slow though, maybe going through MS Query or a form of database might be a faster bet.
ee-match.xlsx
0

Commented:
Another option, that might compute faster is to put the following formula in an adjacent column and filter the True values (which are your maximums) to copy to another sheet.

=COUNTIFS(\$A\$2:\$A\$7,A2,\$B\$2:\$B\$7,B2,\$C\$2:\$C\$7,">"&C2)=0

Thomas
0

Commented:
I used a pivot table to get this view of the max dates
``````Max of Date Sold
Cust#	Product#  	Total
2356	663405T12	1/4/2013
5672	100WX202	3/1/2014
51190	100WX202	7/11/2013
234123	550MP200X20	6/16/2014
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.