Look up item, return earliest date and item

jonathan92591

Board Regular
Joined
Oct 27, 2011
Messages
65
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
EF
1DateEmail
21/1/2010jonathan@gmail.com
32/2/2005jonathan@gmail.com
46/3/2004jonathan@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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

Board Regular
Joined
Oct 27, 2011
Messages
65
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

Board Regular
Joined
Oct 27, 2011
Messages
65
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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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?
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

Board Regular
Joined
Oct 27, 2011
Messages
65
I went ahead and did some less sophisticated sorting and counting formulas. Thank you for your time and effort.

JT
 

Forum statistics

Threads
1,081,728
Messages
5,360,925
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top