ExcelBanter - Statement writing problem

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Statement writing problem (https://www.excelbanter.com/excel-worksheet-functions/135034-statement-writing-problem.html)

Statement writing problem

I know to pull the earliest date or latest date from a range of cells I use
the min or max functions, but I need to wirte a more complex statement.
1.) I need to search a range of cells and pull the earliest date and latest
date for all those tools.
2.) The problem is that tools are not in any order, but they are all in one
column.
3.) There is duplicate tools, I just want to find the earliest date and
latest date for that tool.
4.) I also need to group all the blanks together and group them into one tool.
5.) I know it probably needs to be an if statement, i just don't know how to
write it.

(Lets assume the tools are in column A and the dates are in column B through
Z)
Example tools:
EWR - 6.75
ALD - 6.75
IQ

Statement writing problem

First you need a list of tools without duplicates. Select column A:A
ONLY, perform an advanced filter (Data|Filter|Advanced Filter).
Specify: Copy into another location and unique values only. Select a
range in the same sheet and let the filter place there the unique
values list. Then you can move them to another sheet. Say the list is
in Sheet2!A:A. Put there a header for min and for max date.

In SHeet2!B2:
=MIN(IF(Sheet1!$A$2:$A$100=A2,Sheet1!$B$2:$Z$100))

This is an *array* formula, hence you must commit it with Shift+Ctrl
+Enter.
Similarly (also an array formula) in C2:

=MAX(IF(Sheet1!$A$2:$A$100=A2,Sheet1!$B$2:$Z$100))

HTH
Kostis Vezerides

Assuming now that
On Mar 15, 5:19 pm, Clay wrote:
I know to pull the earliest date or latest date from a range of cells I use
the min or max functions, but I need to wirte a more complex statement.
1.) I need to search a range of cells and pull the earliest date and latest
date for all those tools.
2.) The problem is that tools are not in any order, but they are all in one
column.
3.) There is duplicate tools, I just want to find the earliest date and
latest date for that tool.
4.) I also need to group all the blanks together and group them into one tool.
5.) I know it probably needs to be an if statement, i just don't know how to
write it.

(Lets assume the tools are in column A and the dates are in column B through
Z)
Example tools:
EWR - 6.75
ALD - 6.75
IQ

Statement writing problem

A1:A100 = tools
B1:B100 = dates
C1: holds criteria

=MIN(IF(A1:A100=C1,B1:B13))

ctrl+shift+enter, not just enter

"Clay" wrote:

I know to pull the earliest date or latest date from a range of cells I use
the min or max functions, but I need to wirte a more complex statement.
1.) I need to search a range of cells and pull the earliest date and latest
date for all those tools.
2.) The problem is that tools are not in any order, but they are all in one
column.
3.) There is duplicate tools, I just want to find the earliest date and
latest date for that tool.
4.) I also need to group all the blanks together and group them into one tool.
5.) I know it probably needs to be an if statement, i just don't know how to
write it.

(Lets assume the tools are in column A and the dates are in column B through
Z)
Example tools:
EWR - 6.75
ALD - 6.75
IQ

Statement writing problem

Is there a way to perform this with having duplicates. I want to pull the
date per tool. For example I will have multiple EWR tools but I would like
to pull the earliest date and the latest date for all the EWR tools and
remaining tools. I wish I could exclude the duplicates, but I am importing
from Project the Part Numbers roll into the tool and there are many parts per
tool with each a different schedule. Any thoughts.

"vezerid" wrote:

First you need a list of tools without duplicates. Select column A:A
ONLY, perform an advanced filter (Data|Filter|Advanced Filter).
Specify: Copy into another location and unique values only. Select a
range in the same sheet and let the filter place there the unique
values list. Then you can move them to another sheet. Say the list is
in Sheet2!A:A. Put there a header for min and for max date.

In SHeet2!B2:
=MIN(IF(Sheet1!$A$2:$A$100=A2,Sheet1!$B$2:$Z$100))

This is an *array* formula, hence you must commit it with Shift+Ctrl
+Enter.
Similarly (also an array formula) in C2:

=MAX(IF(Sheet1!$A$2:$A$100=A2,Sheet1!$B$2:$Z$100))

HTH
Kostis Vezerides

Assuming now that
On Mar 15, 5:19 pm, Clay wrote:
I know to pull the earliest date or latest date from a range of cells I use
the min or max functions, but I need to wirte a more complex statement.
1.) I need to search a range of cells and pull the earliest date and latest
date for all those tools.
2.) The problem is that tools are not in any order, but they are all in one
column.
3.) There is duplicate tools, I just want to find the earliest date and
latest date for that tool.
4.) I also need to group all the blanks together and group them into one tool.
5.) I know it probably needs to be an if statement, i just don't know how to
write it.

(Lets assume the tools are in column A and the dates are in column B through
Z)
Example tools:
EWR - 6.75
ALD - 6.75
IQ