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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,405
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

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