Return the oldest date when cell does not contain an *(Asterix)

[C J W]

Hi All

I have a set of data where I need to find the oldest entry based on two criteria. The data set includes an * and this is what I need to be able to find (and not find) and return the oldest date. I have been able to find a formula which will allow me to find the oldest date where an * is present:

Please Login or Register  to view this content.

but I am having no luck being able to amend it to find the oldest date where * is not present. I have attached a very simple workbook with my problem. Unfortunately I am aunable to remove the * from the data.

Can anyone help?

Thanks

[Pete_UK]

You can use this array* formula in G5:

=MAX(IF(B1:B10="Type3",C1:C10))

*Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual < Enter >.

Hope this helps.

Pete

[C J W]

thanks for the reply, that helps me with my original question, I now have a follow up!
If I add another column of data in column D and this cells are either blank or contain an *, how can I find the oldest based on whether column D is blank or contains an *? (as well as matching the type3 in column B?

[Pete_UK]

I'm not sure if you still have asterisks in column B or not. Assuming you have, then you can use this array* formula:

=MAX(IF((ISNUMBER(SEARCH("Type3",B1:B10)))*(ISNUMBER(SEARCH("*",D1:D10))),C1:C10))

This looks for "Type 3" in column B (but it could be "*Type3" or "Type3*") and looks for an asterisk anywhere in column D cells. If column D can only contain either an asterisk or a blank, then you can use this array* formula:

=MAX(IF((ISNUMBER(SEARCH("Type3",B1:B10)))*(D1:D10="*"),C1:C10))

If column B only contains "Typex", i.e. no asterisks, then you could use this array* formula:

=MAX(IF((B1:B10="Type3")*(D1:D10="*"),C1:C10))

and if you want to look for blanks in column D then you can use this*:

=MAX(IF((B1:B10="Type3")*(D1:D10=""),C1:C10))

*Don't forget to commit using Ctrl-Shift-Enter, as previously advised.

Hope this helps.

Pete

[Pete_UK]

If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[xlnitwit]

Technically this
SEARCH("*",D1:D10)
searches for any text, not an asterisk. Since the only options are blank cell or *, it should work for the purposes of the OP though.

FIND might be a safer option as it does not accept wildcards.

[C J W]

thanks for the help!

[Pete_UK]

You're welcome - thanks for the rep.

Pete