Intersting challenge...
Posted by Dustin on March 14, 2001 11:52 AM
Good afternoon everyone,
Here is my latest dilemma:
I need to return a unique list of values from one sheet to another based upon several conditions.
Here is a sample of the data:
Sheet 1
-----A----------B----------C------------D-------
1-Req Num------PO#------StartDate-----EndDate---
2-001234----500500-----2/12/01--------3/01/01---
3-004587----500765-----2/13/01--------3/10/01---
4-006574----500987-----3/01/01--------Current---
5-009845----501078-----4/01/01--------Current---
Sheet 2 (1st Quarter/2001)
-----A----------B----------C------------D-------
1---Name------Region------City----------PO#-----
2------------------------------------(_________)
3------------------------------------(_________)
4------------------------------------(_________)
.
.
Sheet 1 has the data that I need to pull over to Sheet 2. The key field is the PO# field. I can figure out how to use INDEX(MATCH)to pull over all of the other data once I get the right PO# values over.
I would like to have a formula that would display the PO#'s in the PO# column on Sheet 2 that fall within the appropriate date range. I would like the list of UNIQUE (no duplicates) PO#'s to begin in cell D2 and run through D? depending upon how many fall in the date range (for sheet 2 it will be any PO#'s where the StartDate is <4/1/01 AND the EndDate is >=1/1/01 OR "Current").
Thanks in advance.
Dustin
Posted by Dave Hawley on March 14, 2001 12:27 PM
Sheet 1 has the data that I need to pull over to Sheet 2. The key field is the PO# field. I can figure out how to use INDEX(MATCH)to pull over all of the other data once I get the right PO# values over. I would like to have a formula that would display the PO#'s in the PO# column on Sheet 2 that fall within the appropriate date range. I would like the list of UNIQUE (no duplicates) PO#'s to begin in cell D2 and run through D? depending upon how many fall in the date range (for sheet 2 it will be any PO#'s where the StartDate is <4/1/01 AND the EndDate is >=1/1/01 OR "Current"). Thanks in advance.
Hi Dustin
If I have understood you correctly this should work.
On sheet2 cell D2 put:
=IF(AND(COUNTIF(Sheet1!$B$2:$B$500,Sheet1!B2)=1,Sheet1!C2<DATEVALUE("4/1/01")+OR(Sheet1!D2="Current"),Sheet1!D2>=DATEVALUE("1/1/01")),Sheet1!B2,NA())
$B$2:$B$500 represents your entire list of "PO#'s" in Column B on sheet1.
Copy this down as far as needed.
Select all of Column D and then Push Ctrl+C
Then go to Edit>PasteSpecial-Values
Push F5, click "Special"
Select "Formulas" and deselect all except "Errors"
Click Ok
Go to Edit>Delete>Entire Row
Dave
OzGrid Business Applications
Posted by Dave Hawley on March 14, 2001 12:31 PM
Oops, only half came out!
: Sheet 1 has the data that I need to pull over to Sheet 2. The key field is the PO# field. I can figure out how to use INDEX(MATCH)to pull over all of the other data once I get the right PO# values over. : I would like to have a formula that would display the PO#'s in the PO# column on Sheet 2 that fall within the appropriate date range. I would like the list of UNIQUE (no duplicates) PO#'s to begin in cell D2 and run through D? depending upon how many fall in the date range (for sheet 2 it will be any PO#'s where the StartDate is <4/1/01 AND the EndDate is >=1/1/01 OR "Current"). : Thanks in advance.
Only pasted half the formula.
=IF(AND(COUNTIF(Sheet1!$B$2:$B$500,Sheet1!B11)=1,Sheet1!C11<DATEVALUE("7/11/2001")+OR(Sheet1!D11="Current"),Sheet1!D11>=DATEVALUE("1/12/2001")),Sheet1!B11,NA())
OzGrid Business Applications
Posted by Dave Hawley on March 14, 2001 12:33 PM
Arrrhhh!!
=IF(AND(COUNTIF(Sheet1!$B$2:$B$500,Sheet1!B11)=1,Sheet1!C11<DATEVALUE("7/11/2001")
+OR(Sheet1!D11="Current"),Sheet1!D11>=DATEVALUE("1/12/2001")),Sheet1!B11,NA())
Join these 2 togetherOzGrid Business Applications
Posted by Dustin on March 14, 2001 1:10 PM
Thanks Dave, but I don't understand how to join the two together...
+OR(Sheet1!D11="Current"),Sheet1!D11>=DATEVALUE("1/12/2001")),Sheet1!B11,NA())
Posted by Dave Hawley on March 14, 2001 1:36 PM
Re: Thanks Dave, but I don't understand how to join the two together...
Dustin, the first 2 times I tried to paste the formula here it would only show half of it, probaly due to the "<" and ">"
The last formula came out as intended, so use it as is.
Dave
OzGrid Business Applications
Posted by Dustin on March 14, 2001 1:44 PM
Re: Thanks Dave I will try it.
, the first 2 times I tried to paste the formula here it would only show half of it, probaly due to the "<" and ">"
Posted by Dustin on March 15, 2001 1:53 PM
Thanks Dave - it worked perfectly!
