Forum Discussion

Fahad Qamar's avatar
Fahad Qamar
Copper Contributor
Apr 22, 2018

Extracting data based on date value

I have a table with multiple columns For eg customer-id, order-id, reciept-no and date of purchase Where customers-id, order-id and date of purchase might have duplicate values however reciept-no ...
excel
Macros and VBA
Wyn Hopkins's avatar
Wyn Hopkins
MVP
Apr 23, 2018

Hi

 

Could you attach a mocked up file to demonstrate your source data and how you want the results to appear.

 

Can I ask why you want the oldest 2 records when there are 3 or more but only the 1 oldest when there are 2 records?

  • Fahad Qamar's avatar
    Fahad Qamar
    Copper Contributor
    Apr 23, 2018

    a little clarification

    i need to extract all the oldest records and ignore the most recent one
    no matter whatever the case is

    ================

    Can I ask why you want the oldest 2 records when there are 3 or more but only the 1 oldest when there are 2 records?

    ================

    if there are 4 records i need the oldest 3

    if there are 3 records i need the oldest 2 

    and so on

     

    just to add more clarification 

    we i need the data for the cases where the Customer No & Code match are repeating themselves in the table, and all the records where the Creation date is older then then recent (created on )date 

    for example

    for customer no 3009832402

    Reciept no

    customer no

    code

    created on

    32068705630098324023413/04/2018
    32075045830098324024422/04/2018
    32068714930098324024413/04/2018

     

    the result should only extract the row in bold and italic

    and ignore all code which are not similar for the  customer no 3009832402

    and ignore the row with the latest creation date for that code

    • Wyn Hopkins's avatar
      Wyn Hopkins
      MVP
      Apr 23, 2018

      I've attached a mock up of potential formula you could use (3 new columns) to then provide you with a column you can filter by called "Is oldest duplicate".   

      • Fahad Qamar's avatar
        Fahad Qamar
        Copper Contributor
        Apr 24, 2018

        THANK YOU VERY MUCH HOWEVER CAN I WRITE THE FARMULAE FOR "DUPLICATE NUMBER"

        AND "IS OLDEST VALUE"IN POWER QUERY CAN YOU HELP ME ON THAT AS WELL

         

         

         

Resources