Extracting data based on date value

[Fahad Qamar]

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 is unique

I want to extract data based on date of purchase where the date of purchase should be the oldest one for every customer-id, if there are 3 records for any customer-id it should extract the rows having the two oldest dates

And if any customer-id has 2 records it should fetch the 1 row for the oldest date.

[#2]

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?

[#3]

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
320687056	3009832402	34	13/04/2018
320750458	3009832402	44	22/04/2018
320687149	3009832402	44	13/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

[#4]

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".   

[#5]

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

[#6]

Hi 

It's harder with Power Query as I think you need to write a function or play about with the M code

Attached is my solution with using a Custom Function

I first grouped the data by Customer No and Code,  then added a custom function to remove the latest date record from each of these grouped tables

By default then if there was only 1 record, the function would remove it as that is the latest record.

[#7]

Wyn, just in case, I did that through merging - practically the same question https://techcommunity.microsoft.com/t5/Excel/Extracting-data-in-the-new-sheet/m-p/185699