Look up item, return earliest date and item

[jonathan92591]

Hello everyone,

Look up item, return earliest date and item

--

Excel or Access version: Excel 2010

Computer operating system: Windows XP

Sample data: "

Excel Workbook
	E	F
1	Date	Email
2	1/1/2010	jonathan@gmail.com
3	2/2/2005	jonathan@gmail.com
4	6/3/2004	jonathan@gmail.com
Combined

"

Formula(s) right now: N/A

Current result(s): N/A

My goal: To look up the email address, if there are duplicates, pick the earliest date, display email address and date.

Error message: No error message

How error occurred: No error message

Generated in: Excel

Thank you.

JT

 

[T. Valko]

Hello everyone,

Look up item, return earliest date and item

--

Excel or Access version: Excel 2010

Computer operating system: Windows XP

Sample data: "Combined

<TABLE cellSpacing=0 cellPadding=0 border=1 ?? padding-right:2pt; padding-left:2pt; background-color:#ffffff; font-size:12pt; font-family:Calibri,Arial; 0?? 1??><COLGROUP><COL ?? width:30px; font-weight:bold;><COL width:72px;??><COL width:147px;??></COLGROUP><TBODY><TR ?? font-weight:bold; font-size:8pt; text-align:center; background-color:#cacaca;><TD> </TD><TD>E</TD><TD>F</TD></TR><TR ;?? height:18px><TD ?? font-size:8pt; text-align:center; background-color:#cacaca;>1</TD><TD>Date</TD><TD>Email</TD></TR><TR ;?? height:18px><TD ?? font-size:8pt; text-align:center; background-color:#cacaca;>2</TD><TD ?? text-align:right;>1/1/2010</TD><TD ?? text-decoration:underline; color:#0000ff;>jonathan@gmail.com</TD></TR><TR ;?? height:18px><TD ?? font-size:8pt; text-align:center; background-color:#cacaca;>3</TD><TD ?? text-align:right;>2/2/2005</TD><TD ?? text-decoration:underline; color:#0000ff;>jonathan@gmail.com</TD></TR><TR ;?? height:18px><TD ?? font-size:8pt; text-align:center; background-color:#cacaca;>4</TD><TD ?? text-align:right;>6/3/2004</TD><TD ?? text-decoration:underline; color:#0000ff;>jonathan@gmail.com</TD></TR></TBODY></TABLE>

Excel tables to the web >> Excel Jeanie HTML 4

"

Formula(s) right now: N/A

Current result(s): N/A

My goal: To look up the email address, if there are duplicates, pick the earliest date, display email address and date.

Error message: No error message

How error occurred: No error message

Generated in: Excel

Thank you.

JT

Try this...

A1 = the email address to lookup

This array formula** entered in B1:

=IF(COUNTIF(F2:F10,A1),MIN(IF(F2:F10=A1,E2:E10)),"Not Found")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format as Date

 

[jonathan92591]

Thank you for your reply. I am at home now. The file I need to access is at work. I will try this on Monday. Thank you!

JT

 

[jonathan92591]

Hello,

I tried the formula but I have almost 1M records and Excel stated that there were not enough resources to run this formula.

Thank you.

JT

 

[T. Valko]

Hello,

I tried the formula but I have almost 1M records and Excel stated that there were not enough resources to run this formula.

Thank you.

JT

Is your data sorted or grouped together by the email address?

 

[jonathan92591]

No it is not.

 

[T. Valko]

No it is not.

Ok, well, if it was then we could redo the formula so that it only looks at a smaller range of cells that should help improve performance.

You can try this slight tweak but it probably won't make much of a difference.

Still array entered:

=IF(COUNT(MATCH(A1,F2:F10,0)),MIN(IF(F2:F10=A1,E2:E10)),"Not Found")

 

[jonathan92591]

I went ahead and did some less sophisticated sorting and counting formulas. Thank you for your time and effort.

JT

 

[T. Valko]

I went ahead and did some less sophisticated sorting and counting formulas. Thank you for your time and effort.

JT

Good deal. Thanks for the feedback!

 

[jonathan92591]

No, thank you!

JT