Apr 22 2018 12:44 AM
Apr 22 2018 06:28 PM
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?
Apr 22 2018 09:49 PM
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
Apr 23 2018 05:11 AM
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".
Apr 23 2018 05:26 PM
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
Apr 24 2018 05:01 AM
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.
Apr 24 2018 05:39 AM
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