Culling duplicate values from a spreadsheet

Topic

New Reply

I need to add the last purchase date to a list of products. I will refer to our purchase list via vlookup but need to prepare the purchase list by removing multiple order dates.

Some products will have only one purchase date, others many dates but only the most recent is relevant. How do I remove the older dates for those with multiple dates.

Any assistance is greatly appreciated.

Reply | Quote

Replies

You can add a helper cell to use in a filter to copy the data to a new location.
Add this to row 3 and below – it doesn’t work on row 2 because of the date comparison. If row 2 is to be retained, copy A2 to the helper cell.
=IF(C3=C4 AND(A3>A2),"",A3)

Otherwise it’s a UDF to delete the data followed by a sort to remove the blanks.

cheers, Paul

Reply | Quote

Paul, thank you but when typing it in I am getting error messages regarding a missing apostrophe. Using copy/paste I only get a straight paste of the text but nothing happens.

Also what would be the UDF to delete the data?

Reply | Quote

There are no apostrophes in the formula – 2 double quotes only. It pastes ok for me.
Try pasting it into Notepad to check, then copy and paste into Excel.

I haven’t written the UDF because I don’t have Excel so can’t test it.

cheers, Paul

Reply | Quote

Paul,  I am sure it’s my mistake but using paste either directly or via notepad I am getting the formula in the cell but no action. I was expecting some “n/a”  or a date

Reply | Quote

Nothing in the cell may be correct. This is the value when the entry is not the latest.
Add an X between the double quotes to confirm. You will get either the date or X.

cheers, Paul

Reply | Quote