Find items with conditions
Posted by Andonny on January 05, 2001 10:03 PM
Hi,
I like to find all Items (Culumn A) which have a date before Jan 99 but not after Jan 99 and it should tell me somewhere "obsolete". If the same Item has dates before and after Jan 99 it should tell me "No modification Required".
All Items will be listed more than once but with different or same dates.
Sample:
........A...........B.............C
1....Apple.........DEC 99.......obsolete
2....Kiwi..........Feb 99.......No modification Required
3....Apple.........Jul 99.......obsolete
4....Wine..........Nov 99.......obsolete
5....Banana........Mar 00.......No modification Required
6....Wine..........Jul 99.......obsolete
7....Kiwi..........Feb 00.......No modification Required
8....Champagne.....DEC 99.......obsolete
Your help is always very much appreciated and I have been learning heaps and on top of it it has been making my life a lot easier by having all these macros doing the work for me.
Andonny
Posted by Andonny on January 05, 2001 10:15 PM
Sorry I meant Jan 00 instead of Jan 99
Posted by Aladin Akyurek on January 06, 2001 1:31 AM
Select the range C1:C8 and array-enter in C1
=IF($B$1:$B$8<VALUE("Jan-00"),"obsolete","No Modification Required")
Aladin
Posted by Aladin Akyurek on January 06, 2001 1:37 AM
Select c1:c8 and array-enter in c1:
=IF($B$1:$B$8 LessThan VALUE("Jan-00"),"obsolete","No Modification Required")
Note: Substitute Excel's symbol for LessThan!
Aladin
Posted by Andonny on January 06, 2001 3:23 PM
Hi,
Your Formula works fine to look at each item individually. The only thing which it doesn't do is when you look at Kiwi at A2 and A7 (listed twice) they have been purchased in both periods which would need to make them both "no modification required". What I like to do is list all items which have not been purchased since Jan 00. If they have been purchased since then they should not become obsolete even though they have been purchased before Jan 00. This is what makes it specially difficult.
Thanks for you help
Andonny
Posted by Aladin Akyurek on January 06, 2001 5:12 PM
Hi,
I'm not sure whether the following would be the way out.
C1 =IF(B2<VALUE("Jan-00"),IF(ISNA(VLOOKUP(A2,A3:$B$11,2,0)),"obsolete",IF(VLOOKUP(A2,A3:$B$11,2,0)>VALUE("Jan-00"),"No Modification Required","obsolete")),"No Modification Required")
Copy down as far as needed.
Aladin Hi,
Posted by Aladin Akyurek on January 06, 2001 5:12 PM
Hi,
I'm not sure whether the following would be the way out.
C1 =IF(B2<VALUE("Jan-00"),IF(ISNA(VLOOKUP(A2,A3:$B$11,2,0)),"obsolete",IF(VLOOKUP(A2,A3:$B$11,2,0)>VALUE("Jan-00"),"No Modification Required","obsolete")),"No Modification Required")
Copy down as far as needed.
Aladin Hi,
Posted by Aladin Akyurek on January 06, 2001 5:19 PM
Hi,
I still keep making that LessThan mistake!
Anyway, here is a formula which could help us out.
C1=IF(B3 LessThan VALUE("Jan-00"),IF(ISNA(VLOOKUP(A3,A4:$B$11,2,0)),"obsolete",IF(VLOOKUP(A3,A4:$B$11,2,0) GreaterThan VALUE("Jan-00"),"No Modification Required","obsolete")),"No Modification Required")
Aladin Hi,
Posted by Andonny on January 06, 2001 7:00 PM
Hi Aladin,
Thank you so much for your kind help.
I still seem to be missing something. Sorry about that.
I placed a sample at http://www.multiline.com.au/~wje/sample.html with the formulas and the required results. The once in blue and red are the critical once as the are purchased before and after jan 00 and should read "No modificatication required"
Thanks once again
Andonny
Posted by Aladin Akyurek on January 06, 2001 7:29 PM
I get exactly the results under the "should be" column of the sample. The ranges that you used in the formula seem to be incorrect. You'll get the workbook thru e-mail.
Aladin
Posted by Andonny on January 08, 2001 2:58 AM
Hi Aladin,
Thanks a million for your help. Now I will be able to reduce our database by a lot.
Your formula works perfectly. Below is your last response to me.
Sorting was a good idea along with more repeats of the same item.
This exposed the inappropriateness of using VLOOKUP here.
VLOOKUP finds the second date in the list that occurs wtr an item and
the result is based on the evaluation of this value together with the
value of the first date. Later dates concerning this item may
contradict the result based on the the first and second dates.
What we need is, now entirely obvious, a formula that looks at all
dates concerning an item. I think I found one that does exactly this.
It is an array formula that must be entered in the first empty cell
corresponding to the row of the first item then copied down.
=IF(MAX(IF($A$1:$A$21=$A1,1,0)*($B$1:$B$21),1)>=VALUE("Jan-00"),"No
Modification Required","obsolete")
Let me please know how it behaves on (sorted and unsorted data).
Cheers.
Aladin
