Remove Duplicates

[bumfart66]

Hi, not as easy for me as the title suggests

Each box has a number of items that were delivered on the end date, I want to remove the duplicate box reference and only be left with a single reference, the one I want to be left with is the entry with the last end date,

8129​	Company 04	03 February 2023​	18 July 2023​
8129​	Company 04	03 February 2023​	18 July 2023​
8129​	Company 04	03 February 2023​	18 July 2023​
8129​	Company 04	03 February 2023​	20 July 2023​
8129​	Company 04	03 February 2023​	20 July 2023​
8129​	Company 04	03 February 2023​	20 July 2023​

For example with box 8129 i only want to be left with

8129​

Company 04

03 February 2023​

20 July 2023​

Not all boxes have different end delivery dates

Box	Company	Start	End
8124​	Company 01	03 February 2023​	31 March 2023​
8124​	Company 01	03 February 2023​	31 March 2023​
8124​	Company 01	03 February 2023​	31 March 2023​
8124​	Company 01	03 February 2023​	31 March 2023​
8124​	Company 01	03 February 2023​	31 March 2023​
8124​	Company 01	03 February 2023​	31 March 2023​
8125​	Company 01	03 February 2023​	03 March 2023​
8125​	Company 01	03 February 2023​	03 March 2023​
8125​	Company 01	03 February 2023​	03 March 2023​
8125​	Company 01	03 February 2023​	03 March 2023​
8125​	Company 01	03 February 2023​	03 March 2023​
8125​	Company 01	03 February 2023​	03 March 2023​
8127​	Company 02	03 February 2023​	13 June 2023​
8127​	Company 02	03 February 2023​	13 June 2023​
8127​	Company 02	03 February 2023​	13 June 2023​
8127​	Company 02	03 February 2023​	13 June 2023​
8127​	Company 02	03 February 2023​	13 June 2023​
8127​	Company 02	03 February 2023​	13 June 2023​
8128​	Company 03	03 February 2023​	25 April 2023​
8128​	Company 03	03 February 2023​	25 April 2023​
8128​	Company 03	03 February 2023​	25 April 2023​
8128​	Company 03	03 February 2023​	25 April 2023​
8128​	Company 03	03 February 2023​	25 April 2023​
8128​	Company 03	03 February 2023​	25 April 2023​
8128​	Company 03	03 February 2023​	25 April 2023​
8128​	Company 03	03 February 2023​	25 April 2023​
8128​	Company 03	03 February 2023​	25 April 2023​
8129​	Company 04	03 February 2023​	18 July 2023​
8129​	Company 04	03 February 2023​	18 July 2023​
8129​	Company 04	03 February 2023​	18 July 2023​
8129​	Company 04	03 February 2023​	20 July 2023​
8129​	Company 04	03 February 2023​	20 July 2023​
8129​	Company 04	03 February 2023​	20 July 2023​
8131​	Company 05	03 February 2023​	01 September 2023​
8131​	Company 05	03 February 2023​	01 September 2023​
8131​	Company 05	03 February 2023​	01 September 2023​
8131​	Company 05	03 February 2023​	04 September 2023​
8131​	Company 05	03 February 2023​	04 September 2023​
8131​	Company 05	03 February 2023​	04 September 2023​

any help is appreciated.

 

[hagia_sofia]

Hello, if there is only a single company and a single start date per box and last date means the latest, then:

=LET(
a,UNIQUE(A2:C40),
b,MAXIFS(D2:D40,A2:A40,CHOOSECOLS(a,1)),
HSTACK(a,b))

 

[bumfart66]

Hello, if there is only a single company and a single start date per box and last date means the latest, then:

=LET(
a,UNIQUE(A2:C40),
b,MAXIFS(D2:D40,A2:A40,CHOOSECOLS(a,1)),
HSTACK(a,b))

Thank you very much, this works, if you don't mind can you confirm my understanding of what is happening?

The LET function assigns a value of a to the unique values in A2:C40, and assigns b to the maximum value in D2:D40

HSTACK present a + b

Its the CHOOSCOLS thats confusing me

 

[hagia_sofia]

Sure. LET allows you to represent parts of a formula; e.g. in this particular "a" represents UNIQUE(A2:C40), so instead of UNIQUE(A2:C40) you can use "a", instead of HSTACK(UNIQUE(A2:C40),MAXIFS(D2:D40,A2:A40,CHOOSECOLS(a,1)))), it is HSTACK(a,b)). HSTACK allows you to stack arrays, in this case "a" and "b". As far as CHOOSECOLS is concerned, it allows to you pick columns with which you want to work (it could be replaced by INDEX here). Since "a" here returns three columns (i.e. Box - Company - Start) and you want to find MAX value only based on the Box column, CHOOSECOLS picks this particular column out of three as an input for MAXIFS.