Forum Discussion

Nick_Orem's avatar
Nick_Orem
Copper Contributor
Jan 19, 2023

Last product purchased

I have a file of customers and products they have purchased with the date for each purchase.  Some customers have many purchases, some have only one.  I want to create a new file that shows only the last product purchased for each customer.  Here's a sample of the data:

dateuser_nameuser_idproduct_idproduct_name
1/17/2022 22:58Kate13845Individual (Under 40) 2022
2/17/2021 19:41Kate11249Individual 2020
1/10/2022 18:02Douglas23843Couple 1 - 2022
3/4/2021 0:00Douglas21228Couple 1 - 2021
2/16/2021 22:29Douglas21242Couple 1 - 2020
2/16/2021 22:29Susan31246Couple 2
1/10/2022 7:52Mariann43846Individual 2022
1/10/2022 7:49Mariann41237Individual 2021
2/16/2021 22:29Mariann41249Individual 2020
2/16/2021 22:29Henrik51233Honorary
1/13/2022 14:38Chace63843Couple 1 - 2022
2/16/2021 22:29Chace61242Couple 1 - 2020
2/12/2021 0:00Chace61228Couple 1 - 2021
2/16/2021 22:29Josie71246Couple 2
1/8/2022 15:25Karl83846Individual 2022
11/19/2021 21:22Karl81237Individual 2021
2/16/2021 22:29Karl81249Individual 2020
1/19/2022 16:04Carter93846Individual 2022
2/16/2021 22:29Carter91249Individual 2020
2/11/2021 0:00Carter91237Individual 2021
Macros and VBA
  • mathetes's avatar
    mathetes
    Silver Contributor
    Jan 20, 2023

    Nick_Orem 

     

    See attached. You'll need a very recent version of Excel for these functions to work. There are two formulas.

    =UNIQUE(B2:B21)   produces a list of unique customer names

    then

    =FILTER($D$2:$D$21,

    ($B$2:$B$21=H3)*($A$2:$A$21=MAX(FILTER($A$2:$A$21,B$2:$B$21=H3))))

    gets the most recent (MAX) order date & time and then matches that against products ordered by that customer.

     

Resources