Excel Brain Teaser - Complex multiple condition array formula to select data from a matrix

[Brad B]

Hello,

I've learned a lot from people smarter than myself on this forum, so I'm here to learn again.

Preface, I will try to be clear and concise, but there are several conditions and complications here:

I have a table (~70 columns, 500 rows and counting) that is exported from a system-generated CSV.
Unfortunately, the data in the table is both new and old (newest release, and older releases), so I would like to write a formula that will select all of the records (unique delivery date, delivery qty) for each part number, from ONLY the newest release (and delete / ignore all other records from older releases) From the trimmed/new data, I'll create a pivot table and be on my merry way. I could get the desired result manually by filtering so I see each part number one at a time, finding the highest number release, and deleting the all rows that don't have the highest release number. That's essentially where I am trying to get to. The tricky part is that the release numbers are different for each part number, so I can't just find one highest release number, I have to find the latest release number for EACH part number.

The relevant columns I need are Part Number, Delivery Date, Delivery Quantity, of which, I only want the rows from the latest release.
The table below shows a sample of the data I would find in the csv. I believe I have included enough data to show the variables that can occur.

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Release Date[/TD]
[TD]Release No.[/TD]
[TD]Part No.[/TD]
[TD]Delivery Date[/TD]
[TD]Delivery Qty[/TD]
[/TR]
[TR]
[TD]12/10
[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]1/1[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]12/10[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]1/3[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]12/10
[/TD]
[TD]17[/TD]
[TD]B[/TD]
[TD]1/1[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]12/11[/TD]
[TD]2[/TD]
[TD]A[/TD]
[TD]1/1[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]12/11
[/TD]
[TD]2[/TD]
[TD]A[/TD]
[TD]1/3[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]12/11[/TD]
[TD]18[/TD]
[TD]B[/TD]
[TD]1/5[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]12/11[/TD]
[TD]7[/TD]
[TD]C[/TD]
[TD]1/3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]12/12
[/TD]
[TD]3[/TD]
[TD]A[/TD]
[TD]1/3[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]12/12[/TD]
[TD]3[/TD]
[TD]A[/TD]
[TD]1/5[/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]
















Note: not every part has a new release every time I run the csv output, but I need to include all the data rows from whatever the most recent release was, not just the releases created today. I believe this is already accounted for by finding the highest release number for each part.

This would be the desired result from the table above:

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Release Date[/TD]
[TD]Release No.[/TD]
[TD]Part No.[/TD]
[TD]Delivery Date[/TD]
[TD]Delivery Qty[/TD]
[/TR]
[TR]
[TD]12/11[/TD]
[TD]18[/TD]
[TD]B[/TD]
[TD]1/5[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]12/11[/TD]
[TD]7[/TD]
[TD]C[/TD]
[TD]1/3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]12/12
[/TD]
[TD]3[/TD]
[TD]A[/TD]
[TD]1/3[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]12/12[/TD]
[TD]3[/TD]
[TD]A[/TD]
[TD]1/5[/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]








I hope this makes sense, and I sure hope someone can help!

Thanks

 

[Marcelo Branco]

Maybe using Advanced Filter with a Formula as Criteria

Something like this (desired result --> grey area)


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[TD="bgcolor: #DCE6F1"]

K​

[/TD]
[TD="bgcolor: #DCE6F1"]

L​

[/TD]
[TD="bgcolor: #DCE6F1"]

M​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Release Date​

[/TD]
[TD]

Release No.​

[/TD]
[TD]

Part No.​

[/TD]
[TD]

Delivery Date​

[/TD]
[TD]

Delivery Qty​

[/TD]
[TD][/TD]
[TD]

Formula​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

Release Date​

[/TD]
[TD="bgcolor: #D9D9D9"]

Release No.​

[/TD]
[TD="bgcolor: #D9D9D9"]

Part No.​

[/TD]
[TD="bgcolor: #D9D9D9"]

Delivery Date​

[/TD]
[TD="bgcolor: #D9D9D9"]

Delivery Qty​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

12/10​

[/TD]
[TD]

1​

[/TD]
[TD]

A​

[/TD]
[TD]

1/1​

[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD]

FALSE​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

12/11​

[/TD]
[TD="bgcolor: #D9D9D9"]

18​

[/TD]
[TD="bgcolor: #D9D9D9"]

B​

[/TD]
[TD="bgcolor: #D9D9D9"]

1/5​

[/TD]
[TD="bgcolor: #D9D9D9"]

20​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

12/10​

[/TD]
[TD]

1​

[/TD]
[TD]

A​

[/TD]
[TD]

1/3​

[/TD]
[TD]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

12/11​

[/TD]
[TD="bgcolor: #D9D9D9"]

7​

[/TD]
[TD="bgcolor: #D9D9D9"]

C​

[/TD]
[TD="bgcolor: #D9D9D9"]

1/3​

[/TD]
[TD="bgcolor: #D9D9D9"]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

12/10​

[/TD]
[TD]

17​

[/TD]
[TD]

B​

[/TD]
[TD]

1/1​

[/TD]
[TD]

20​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

12/12​

[/TD]
[TD="bgcolor: #D9D9D9"]

3​

[/TD]
[TD="bgcolor: #D9D9D9"]

A​

[/TD]
[TD="bgcolor: #D9D9D9"]

1/3​

[/TD]
[TD="bgcolor: #D9D9D9"]

15​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

12/11​

[/TD]
[TD]

2​

[/TD]
[TD]

A​

[/TD]
[TD]

1/1​

[/TD]
[TD]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

12/12​

[/TD]
[TD="bgcolor: #D9D9D9"]

3​

[/TD]
[TD="bgcolor: #D9D9D9"]

A​

[/TD]
[TD="bgcolor: #D9D9D9"]

1/5​

[/TD]
[TD="bgcolor: #D9D9D9"]

15​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

12/11​

[/TD]
[TD]

2​

[/TD]
[TD]

A​

[/TD]
[TD]

1/3​

[/TD]
[TD]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

12/11​

[/TD]
[TD]

18​

[/TD]
[TD]

B​

[/TD]
[TD]

1/5​

[/TD]
[TD]

20​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

12/11​

[/TD]
[TD]

7​

[/TD]
[TD]

C​

[/TD]
[TD]

1/3​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

12/12​

[/TD]
[TD]

3​

[/TD]
[TD]

A​

[/TD]
[TD]

1/3​

[/TD]
[TD]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

12/12​

[/TD]
[TD]

3​

[/TD]
[TD]

A​

[/TD]
[TD]

1/5​

[/TD]
[TD]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in G2
=A2=MAX(INDEX(($C$2:$C$10=C2)*$A$2:$A$10,))

Select data range (A1:E10 in the sample above)
and go to Data > Advanced Filter

check --> Copy to another location
List Range: $A$1:$E$10
Criteria Range: $G$1:$G$2
Copy to: $I$1
OK

Hope this helps

M.