minif ?

it's an excel world

Board Regular
Joined
Sep 17, 2008
Messages
126
Hello all,

I tried searching for this answer but couldn't find the exact question.

I have two columns of data
Column A are my order numbers that are repeated several time down the column. Column B are my order line dates. There may be several different dates corresponding to the same order number in column A, based on what date the line was added to the order.

I wanted to add a formula to column C that looks at the order number in column A, then identifies the earliest date in column B that has the same order number in column A. So when I copy this formula down the file, I will end up with one dater per order number (the earliest date).

Thanks in advance!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hello all,

I tried searching for this answer but couldn't find the exact question.

I have two columns of data
Column A are my order numbers that are repeated several time down the column. Column B are my order line dates. There may be several different dates corresponding to the same order number in column A, based on what date the line was added to the order.

I wanted to add a formula to column C that looks at the order number in column A, then identifies the earliest date in column B that has the same order number in column A. So when I copy this formula down the file, I will end up with one dater per order number (the earliest date).

Thanks in advance!
Is the data sorted or grouped together based on order numbers? Like this:

Book1
AB
1OrderDate
214/27/2002
3112/5/2005
413/16/2005
519/22/2002
6111/4/2011
719/23/2004
816/15/2003
926/18/2006
1039/14/2013
1142/14/2005
12410/6/2007
Sheet2

Or, is the data random?
 
Upvote 0
The data is random, but can always be resorted by order number if necessary.

Thanks!
Ok, I thought you wanted something like this:

Book1
ABC
1OrderDateMin Date
214/27/20024/27/2002
3112/5/2005
413/16/2005
519/22/2002
6111/4/2011
719/23/2004
816/15/2003
926/18/20066/18/2006
1039/14/20139/14/2013
1142/14/20052/14/2005
12410/6/2007
Sheet2

But, it looks like you have a solution!
 
Upvote 0
T. Valko,

You are correct, this is actually what I was looking for. If you could provide the formula, I would GREATLY appreciate it.


Thank you,
 
Upvote 0
Try this in C2:
(Array formula confirm CTRL+SHIFT+ENTER)

=IF(COUNTIF($A$2:A2,A2)>1,"",MIN(IF($A$2:$A$12=A2,$B$2:$B$12)))
 
Upvote 0
Robert,

When I tried this, it worked for the first row, but the remaining rows returned a blank cell.


Thanks,
Robert's formula works OK for me:

Book1
ABC
1OrderDateMin Date
214/27/20024/27/2002
3112/5/2005_
413/16/2005_
519/22/2002_
6111/4/2011_
719/23/2004_
816/15/2003_
926/18/20066/18/2006
1039/14/20139/14/2013
1142/14/20052/14/2005
12410/6/2007_
Sheet1

Did you enter it as an array formula?
 
Upvote 0

Forum statistics

Threads
1,224,503
Messages
6,179,134
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top