MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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!