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

Brad B

New Member
Joined
Jul 27, 2016
Messages
17
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 DateRelease No.Part No.Delivery DateDelivery Qty
12/10
1A1/110
12/101A1/315
12/10
17B1/120
12/112A1/115
12/11
2A1/315
12/1118B1/520
12/117C1/35
12/12
3A1/315
12/123A1/515

<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 DateRelease No.Part No.Delivery DateDelivery Qty
12/1118B1/520
12/117C1/35
12/12
3A1/315
12/123A1/515

<tbody>
</tbody>








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

Thanks
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
17,017
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,545
Messages
5,832,380
Members
430,128
Latest member
ojl987

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top