• Culling duplicate values from a spreadsheet

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Culling duplicate values from a spreadsheet

    • This topic has 5 replies, 2 voices, and was last updated 5 months ago.

    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.

    Viewing 3 reply threads
    • #2490821

      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

    • #2491065

      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?

    • #2491158

      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

      • #2491232

        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

    • #2491283

      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

    Viewing 3 reply threads
    Reply To: Culling duplicate values from a spreadsheet

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: