Help with an excel formula

Oakdene

Hi All

I'm in work, trying to sort through some figures for our head office & I have a dataset which lists all the sales of our parts for the year. There are multiple entries for each part as they have been sold one more than one occasion. The dataset consists around 2500 rows...

HO want a report which shows the last selling date for each part. I have (in another worksheet) taken all the parts & removed duplicates, but what I now need to do is run a lookup (or something similar) to shift through the original data to find the last selling date, is this possible?

Thanks

Debbie_Savard

Are the separate dates of sale spread over columns B, C, D etc or all concatenated together in one cell in column B (assume part # is in column A)?

Oakdene

Debbie_Savard wrote: »

Are the separate dates of sale spread over columns B, C, D etc or all concatenated together in one cell in column B (assume part # is in column A)?

They are in one column, e.g.:

Part Selling Date
ABC 01/10/17
DEF 02/05/17
ABC 30/10/17
ABC 29/04/17
DEF 04/04/17

So basically I would like to run it so that it would show
ABC 30/10/17
DEF 02/05/17

Tom99

can you sort the column by date then use vlookup to find the 1st entry?

WaywardDriver

Insert a PivotTable, define Rows as Part and Values as Maximum Selling Date, format latter as date.

Twopints

Probably need to split the data into 2 columns first - use Text to columns (on data tab) to split it

Oakdene

WaywardDriver wrote: »

Insert a PivotTable, define Rows as Part and Values as Maximum Selling Date, format latter as date.

Star man, didn't think of that!! Thank you all.

Oakdene

Ok, so now I need a little bit more help & I dont think the pivot table is going to let me do this...

I now need to show the last purchase price of the parts:


Part Selling Date Price
ABC 01/10/17 £25.01
DEF 02/05/17 £17.64
ABC 30/10/17 £24.99
ABC 29/04/17 £26.42
DEF 04/04/17 £18.01

So basically I would like to run it so that it would show
ABC 30/10/17 £24.99
DEF 02/05/17 £17.64

The trouble is the price count on the pivot table is set to max (as per the date count) but I need it to be the price on the last date if that makes sense.

WaywardDriver

Use Data-Text to Columns to have Part in column A, Selling Date in B, Price in C.
In D2 enter formula =A2&B2 and copy down column.
Insert PivotTable as before in F1 say.
In H2 enter formula =INDIRECT("C" & MATCH(F2&G2,D:D,0)) and copy down column.