Filter dynamic array to keep rows which include conditional minimum values

Hi,

I have a dynamic array, like this:

14.11.2018	A	B	C
15.11.2018	A	B	C
16.11.2018	D	E	F
17.11.2018	D	E	F
20.11.2018	G	H	I

The table contains duplicates relative to columns B, C and D, but the dates in column A for those duplicates will differ. For each set of duplicate rows with regards to columns B, C and D, I need to filter out duplicates and keep only the row within each duplicate set which contain the lowest value in column A for the set.

The resulting table will then look something like this:

14.11.2018	A	B	C
16.11.2018	D	E	F
20.11.2018	G	H	I

Does anybody have a dynamic array solution to this? Please see attached workbook.

Best regards,
Marbleking

Hi, AliGW

Thank you for the formula. I am trying to use a modified version of it that looks like this:

=FILTER($A$3:$D$7,$A$3:$A$7=MINIFS(INDEX($A$3:$D$7,,1),INDEX($A$3:$D$7,,2),INDEX($A$3:$D$7,,2),INDEX($A$3:$D$7,,3),INDEX($A$3:$D$7,,3),INDEX($A$3:$D$7,,4),INDEX($A$3:$D$7,,4)),"")

This formula works fine for the data set that was provided. However, when I try to apply this solution to the actual data that I am working on, which becomes a bit more complex, the formula gives a #VALUE! error:

=FILTER(SORT(UNIQUE(CHOOSE({1\2\3\4},SAP[Created on],SAP[Batch],SAP[Material],SAP[Material Description])),{1\2}),INDEX(SORT(UNIQUE(CHOOSE({1\2\3\4},SAP[Created on],SAP[Batch],SAP[Material],SAP[Material Description])),{1\2}),,1)=MINIFS(INDEX(SORT(UNIQUE(CHOOSE({1\2\3\4},SAP[Created on],SAP[Batch],SAP[Material],SAP[Material Description])),{1\2}),,1),INDEX(SORT(UNIQUE(CHOOSE({1\2\3\4},SAP[Created on],SAP[Batch],SAP[Material],SAP[Material Description])),{1\2}),,2),INDEX(SORT(UNIQUE(CHOOSE({1\2\3\4},SAP[Created on],SAP[Batch],SAP[Material],SAP[Material Description])),{1\2}),,2)),"")

Can you see any reason for this failing? (PS: This last formula only uses the second column of the data as criterion, but that's intended).

Regards,
Marbleking

Hi,

Please see attached workbook. I'd like to get the formula in K23 right. Currently it yields #VALUE!. The formula uses CHOOSE to select columns to work with and then INDEX to separate singular columns from this selection.

Regards,
Marbleking