Extract Unique Records Based on Date Instances

[legalhustler]

Hi,

I'm looking for a dynamic spilled array formula (and/or even a Power Query solution) to extract unique ID records based on the Date column.

The first two records for ID 123 would be considered one unique record because they ordered milk on 2/5/24 and received it on 2/7/24 (the subsequent date is always considered the received date), a complete order. The last two records for ID 123 would be considered another unique record because ID 123 ordered milk again on 2/10/24 and received it on 2/11/24. Eggs and Apple for ID 123 and 777, respectively, were ordered but not received so they would also be considered unique records (just not a complete order).

Date	ID	Amount	Food
2/5/24	123	$100	Milk
2/7/24	123	$100	Milk
2/5/24	123	$100	Eggs
2/5/24	888	$50	Cookies
2/6/24	888	$50	Cookies
2/12/24	777	$100	Apple
2/10/24	123	$100	Milk
2/11/24	123	$100	Milk

Here is what the result should look like:

ID	Amount	Food
123	$100	Milk
123	$100	Eggs
888	$50	Cookies
123	$100	Milk
777	$75	Apple

 

[Vogateer]

Is the order date always listed before the received date?

It seems like you could create a new column that combines the ID and Food into one value (I'd call it ID_Food), , then use a UNIQUE formula to get the complete list of ID_Food values, then use some INDEX-MATCH or XLOOKUP formulas to get the rest.

I'll use table references below, since I don't know what columns or rows you might be using. You'd either need to make that original info a table and adjust the Table1 reference below, or use other cell references instead.

ID_FOOD column:

=[@ID] & "_" & [@FOOD]

Unique Formula:

=UNIQUE(Table1[ID_FOOD])

Then INDEX MATCH example:

=INDEX(Table1[ID], MATCH({Cell of your Unique formula}, Table1[ID_Food], 0))

 

[hagia_sofia]

If it is true that the "order date always listed before the received date" then you could try this (not an elegant formula but might work):

=TEXTSPLIT(TEXTJOIN("|";;LET(array;A1:C8;join;SCAN("";BYROW(array;LAMBDA(a;TEXTJOIN(";";2;a)));LAMBDA(a;b;IF(a=b;"";b)));FILTER(join;join<>"")));";";"|") where you replace A1:C8 with your array...

 

[hagia_sofia]

Sorry, one more with commas:

=TEXTSPLIT(TEXTJOIN("|",,LET(array,F22:H29,join,SCAN("",BYROW(array,LAMBDA(a,TEXTJOIN(";",1,a))),LAMBDA(a,b,IF(a=b,"",b)));FILTER(join,join<>""))),";","|")

 

[legalhustler]

Sorry, one more with commas:

=TEXTSPLIT(TEXTJOIN("|",,LET(array,F22:H29,join,SCAN("",BYROW(array,LAMBDA(a,TEXTJOIN(";",1,a))),LAMBDA(a,b,IF(a=b,"",b)));FILTER(join,join<>""))),";","|")

Order date is always listed before the received date.

What columns does F22:H29 refer to? What is the complete formula? I didn't understand the comma part.

Assume the data I have are from A1:D9

 

[legalhustler]

Also important to note - the Date column is in sequential order that way you know if something is ordered and/or received. In the table above, for ID 777 the date should have been 2/8/24 and not 2/12/24

 

[hagia_sofia]

The complete formula is =TEXTSPLIT(TEXTJOIN("|",,LET(array,F22:H29,join,SCAN("",BYROW(array,LAMBDA(a,TEXTJOIN(";",1,a))),LAMBDA(a,b,IF(a=b,"",b)));FILTER(join,join<>""))),";","|")

As order date is always listed before the received date, you may ignore column with dates completely, i.e. replace F22:H29 with B1:D9.

 

[legalhustler]

The complete formula is =TEXTSPLIT(TEXTJOIN("|",,LET(array,F22:H29,join,SCAN("",BYROW(array,LAMBDA(a,TEXTJOIN(";",1,a))),LAMBDA(a,b,IF(a=b,"",b)));FILTER(join,join<>""))),";","|")

As order date is always listed before the received date, you may ignore column with dates completely, i.e. replace F22:H29 with B1:D9.

I did as you stated but it doesn't even recognize it as a formula (see below). I even included the headers (row 1) and without the headers (row 2) but it can't recognize it as a formula still.

=TEXTSPLIT(TEXTJOIN("|",,LET(array,B1:D9,join,SCAN("",BYROW(array,LAMBDA(a,TEXTJOIN(";",1,a))),LAMBDA(a,b,IF(a=b,"",b)));FILTER(join,join<>""))),";","|")

FYI - I have all those functions in my version of Excel too

 

[Vogateer]

Hagia_Sofia's formula still has one semicolon in it that needs to be switched to a comma:

=TEXTSPLIT(TEXTJOIN("|",TRUE,LET(array,A1:D9,join,SCAN("",BYROW(array,LAMBDA(a,TEXTJOIN(";",1,a))),LAMBDA(a,b,IF(a=b,"",b))),FILTER(join,join<>""))),";","|")

But I don't think this will give you what you want, since it's not filtering out the received dates. Also, I don't think you can keep the date formatting with this formula, but I could be wrong.

 

[hagia_sofia]

Hagia_Sofia's formula still has one semicolon in it that needs to be switched to a comma:

=TEXTSPLIT(TEXTJOIN("|",TRUE,LET(array,A1:D9,join,SCAN("",BYROW(array,LAMBDA(a,TEXTJOIN(";",1,a))),LAMBDA(a,b,IF(a=b,"",b))),FILTER(join,join<>""))),";","|")

But I don't think this will give you what you want, since it's not filtering out the received dates. Also, I don't think you can keep the date formatting with this formula, but I could be wrong.

It is true both in relation to the semicolon (try: TEXTSPLIT(TEXTJOIN("|",,LET(array,B2:D9,join,SCAN("",BYROW(array,LAMBDA(a,TEXTJOIN(";",1,a))),LAMBDA(a,b,IF(a=b,"",b))),FILTER(join,join<>""))),";","|")) and in the latter part as it does not return dates - but was it not the original intention?