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.

Release Date	Release No.	Part No.	Delivery Date	Delivery Qty
12/10	1	A	1/1	10
12/10	1	A	1/3	15
12/10	17	B	1/1	20
12/11	2	A	1/1	15
12/11	2	A	1/3	15
12/11	18	B	1/5	20
12/11	7	C	1/3	5
12/12	3	A	1/3	15
12/12	3	A	1/5	15

<tbody>
</tbody>

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:

Release Date	Release No.	Part No.	Delivery Date	Delivery Qty
12/11	18	B	1/5	20
12/11	7	C	1/3	5
12/12	3	A	1/3	15
12/12	3	A	1/5	15

<tbody>
</tbody>

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)

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

1​

Release Date​

Release No.​

Part No.​

Delivery Date​

Delivery Qty​

Formula​

Release Date​

Release No.​

Part No.​

Delivery Date​

Delivery Qty​

2​

12/10​

1​

A​

1/1​

10​

FALSE​

12/11​

18​

B​

1/5​

20​

3​

12/10​

1​

A​

1/3​

15​

12/11​

7​

C​

1/3​

5​

4​

12/10​

17​

B​

1/1​

20​

12/12​

3​

A​

1/3​

15​

5​

12/11​

2​

A​

1/1​

15​

12/12​

3​

A​

1/5​

15​

6​

12/11​

2​

A​

1/3​

15​

7​

12/11​

18​

B​

1/5​

20​

8​

12/11​

7​

C​

1/3​

5​

9​

12/12​

3​

A​

1/3​

15​

10​

12/12​

3​

A​

1/5​

15​

<tbody>
</tbody>

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.