Show date value where the company name matches and date is not null

[geoffgarcia]

I've been toiling with this one for a few hours and have decided to come ask the experts for help.
I'm trying to capture the start date associated with a particular company (in sheet 1) from a list of many stages that each company goes through (sheet 2)

Sheet 1: unique list of company names, and the "start" date I'm hoping to capture for each company.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Company
[/TD]
[TD]Start Date
[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GHI[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2: Each company represented multiple times, but only a single instance for each company to have a start date.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Company
[/TD]
[TD]Start Date
[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]5/20/2019[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD]2/5/2018[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GHI[/TD]
[TD]1/12/2019[/TD]
[/TR]
[TR]
[TD]GHI[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GHI[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GHI[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Desired Outcome for sheet 1:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Company
[/TD]
[TD]Start Date
[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]5/20/2019[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD]2/5/2018[/TD]
[/TR]
[TR]
[TD]GHI[/TD]
[TD]1/12/2019[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Welcome to the Board!

How about just using Filters to filter out all records where Start Date is blank?

 

[geoffgarcia]

I have thousands of records that I'm dealing with and this is one step of several to use this data for a report. Unfortunately filtering won't be sufficient for this.

 

[DanteAmor]

I give you 3 options

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet1</b></td></tr></table>
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:122.61px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Company</td><td >Start Date</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >ABC</td><td style="text-align:right; ">20/05/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >DEF</td><td style="text-align:right; ">05/02/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >GHI</td><td style="text-align:right; ">12/01/2019</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=SUMPRODUCT(MAX((Sheet2!A2:A12=A2)*(Sheet2!B2:B12)))</td></tr><tr><td >B3</td><td >{=MAX((Sheet2!A2:A12=A3)*(Sheet2!B2:B12))}</td></tr><tr><td >B4</td><td >{=MAX(IF(Sheet2!A2:A12=A4,Sheet2!B2:B12))}</td></tr></table></td></tr></table>

---
Cells B3 and B4 are array formulas.

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

---

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet2</b></td></tr></table>
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:142.57px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Company</td><td >Start Date</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >ABC</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >ABC</td><td style="text-align:right; ">20/05/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >ABC</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >ABC</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >DEF</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >DEF</td><td style="text-align:right; ">05/02/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >DEF</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >GHI</td><td style="text-align:right; ">12/01/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >GHI</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >GHI</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >GHI</td><td > </td></tr></table>