Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Vlookup and get the min value (date)

TOP Table is Input, and bottom table is preview for required output.

For Each ID I need to find earliest datetime. I also need other information from other columns (please see image below).

My current solution is: In Cell E2 =A2
Cell E3 drag down =IF(E2<>A3,IF(E1=A3,"",A3),"")

In Cell F2 drag down =IF(E2<>"",MIN(IF($A$2:$A$14=E2,$C$2:$C$14)),"") Ctrl+Shift+Enter

enter image description here

like image 350
Mowgli Avatar asked Feb 28 '13 19:02

Mowgli


1 Answers

One more option without any intermediate calculations:

  1. Select the whole range starting E2 and to the last row where IDs are located - for the sample given it's row 14, so select range E2:E14: =IFERROR(INDEX($A$2:$A$14,SMALL(IF(MATCH($A$2:$A$14,$A$2:$A$14,0)=ROW(INDIRECT("1:"&ROWS($A$2:$A$14))),MATCH($A$2:$A$14,$A$2:$A$14,0),""),ROW(INDIRECT("1:"&ROWS($A$2:$A$14))))),"") and press CTRL+SHIFT+ENTER instead of usual ENTER - this will define a Multicell ARRAY formula and will result in curly {} brackets around it (but do NOT type them manually!).
  2. F2 (ID2): =IF(E2="","",SUMPRODUCT(--(E2=$A$2:$A$14),--(G2=$C$2:$C$14),$B$2:$B$14)) - normal formula.
  3. G2 (Min Date): =IF(E2="","",MIN(IF(E2=$A$2:$A$14,$C$2:$C$14,2^100))) and press CTRL+SHIFT+ENTER instead of usual ENTER - this will define an ARRAY formula and will result in curly {} brackets around it (but do NOT type them manually!).
  4. H2 (InCh): =IF(E2="","",INDEX($D$2:$D$14,SUMPRODUCT(--(E2=$A$2:$A$14),--(F2=$B$2:$B$14),--(G2=$C$2:$C$14),ROW(INDIRECT("1:"&ROWS($D$2:$D$14)))))) - normal formula.

Remarks:

  • To make the solution more compact and easy to read, define named range for ID column, and then reference other data columns using OFFSET.
  • ID2 values may not be unique - as they are on the sample for IDs 1...3.
  • Resulting set for Min Date should be formatted the same way as source Date row.
  • The key formula of the solution - is multicell monster which returns unique IDs without empty rows - as OP requested)

Sample file: https://www.dropbox.com/s/d2098updfh8djnf/MinDateIDs.xlsx

like image 68
Peter L. Avatar answered Sep 23 '22 20:09

Peter L.