Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

Solved

Findling last date a product was sold in Excel

Posted on 2014-03-28

1 solution

Medium Priority

293 Views

Last Modified: 2014-04-08

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

Open in new window

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

Open in new window

Any help would be appreciated.

Mark

Comment

Question by:9thTee

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

3 Comments

LVL 39

Accepted Solution

by:nutsch

nutsch earned 2000 total points

ID: 399629372014-03-28

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

LVL 39

Expert Comment

by:nutsch

ID: 399629712014-03-28

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

LVL 46

Expert Comment

by:aikimark

ID: 399642282014-03-29

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

Open in new window

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert a grid inside a picture into Microsoft Excel

Article by: Wilder1626

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.

Way to Open Word, Excel, Powerpoint file without Microsoft Office.

Article by: joey

: Microsoft Office Collaborate for free and online versions of Microsoft Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by Micr…

Building Probability Models in Excel Part 5: Modeling an Investment Using Discrete Random Variables

Video by: Toby

The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

How to use the IF function in Excel

Video by: Bob

Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…