| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find the next date for a product
Hello,
I have an excel document with two sheets, sheet1 contains a list of product codes in column A and a date next to each in column B. Sheet2 is almost the same but with different dates and some products aren't on the list. For each product code - In column C on sheet one I want to find the same product code from sheet2 and pick up the date next to it if it is greater than the date in column B. If there is no match I was going to leave the cell blank. I've tried index/match with if statements and lookups but with no success Any ideas.... Thanks in advance |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the next date for a product
Try this:
=IF(COUNTIF(Sheet2!A$2:A$15,A2),IF(VLOOKUP(A2,Shee t2!A$2:B$15,2,0)B2,VLOOKUP(A2,Sheet2!A$2:B$15,2,0 ),""),"") -- Biff Microsoft Excel MVP "Algeraist" wrote in message ... Hello, I have an excel document with two sheets, sheet1 contains a list of product codes in column A and a date next to each in column B. Sheet2 is almost the same but with different dates and some products aren't on the list. For each product code - In column C on sheet one I want to find the same product code from sheet2 and pick up the date next to it if it is greater than the date in column B. If there is no match I was going to leave the cell blank. I've tried index/match with if statements and lookups but with no success Any ideas.... Thanks in advance |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the next date for a product
No it didn't work -
Hope I entered it correctly I've pasted the format I tested it on the the function =IF(COUNTIF(Sheet2!A1:A13,A1),IF(VLOOKUP(A1,Sheet2 !A:B,2,0)B1,VLOOKUP(A1,Sheet2!A:B,2,0),""),"") Test data look like this Sheet 1 A B C 1 Item 1 3/03/2007 2 Item 2 4/03/2007 3 Item 3 3/06/2007 4/03/2008 4 Item 4 4/03/2007 5 Item 5 5/03/2007 6 Item 6 6/03/2007 7 Item 7 7/03/2007 Sheet 2 A B 1 item 1 1/03/2007 2 item 1 4/03/2007 3 item 1 5/03/2007 4 item 2 1/03/2007 5 item 2 3/03/2008 6 item 2 3/03/2007 7 item 2 1/03/2008 8 item 3 4/03/2008 9 item 3 1/03/2008 10 item 3 2/03/2008 11 item 3 1/07/2008 12 item 3 23/03/2008 13 item 3 1/03/2008 "T. Valko" wrote: Try this: =IF(COUNTIF(Sheet2!A$2:A$15,A2),IF(VLOOKUP(A2,Shee t2!A$2:B$15,2,0)B2,VLOOKUP(A2,Sheet2!A$2:B$15,2,0 ),""),"") -- Biff Microsoft Excel MVP "Algeraist" wrote in message ... Hello, I have an excel document with two sheets, sheet1 contains a list of product codes in column A and a date next to each in column B. Sheet2 is almost the same but with different dates and some products aren't on the list. For each product code - In column C on sheet one I want to find the same product code from sheet2 and pick up the date next to it if it is greater than the date in column B. If there is no match I was going to leave the cell blank. I've tried index/match with if statements and lookups but with no success Any ideas.... Thanks in advance |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the next date for a product
OK, it didn't work because there are duplicate item numbers on sheet2. You
didn't mention that in your post. So: Sheet 1 A B C 1 Item 1 3/03/2007 Sheet 2 A B 1 item 1 1/03/2007 2 item 1 4/03/2007 3 item 1 5/03/2007 Which date should be returned for item 1? Both row 2 and 3 are greater than the date on sheet1. -- Biff Microsoft Excel MVP "Algeraist" wrote in message ... No it didn't work - Hope I entered it correctly I've pasted the format I tested it on the the function =IF(COUNTIF(Sheet2!A1:A13,A1),IF(VLOOKUP(A1,Sheet2 !A:B,2,0)B1,VLOOKUP(A1,Sheet2!A:B,2,0),""),"") Test data look like this Sheet 1 A B C 1 Item 1 3/03/2007 2 Item 2 4/03/2007 3 Item 3 3/06/2007 4/03/2008 4 Item 4 4/03/2007 5 Item 5 5/03/2007 6 Item 6 6/03/2007 7 Item 7 7/03/2007 Sheet 2 A B 1 item 1 1/03/2007 2 item 1 4/03/2007 3 item 1 5/03/2007 4 item 2 1/03/2007 5 item 2 3/03/2008 6 item 2 3/03/2007 7 item 2 1/03/2008 8 item 3 4/03/2008 9 item 3 1/03/2008 10 item 3 2/03/2008 11 item 3 1/07/2008 12 item 3 23/03/2008 13 item 3 1/03/2008 "T. Valko" wrote: Try this: =IF(COUNTIF(Sheet2!A$2:A$15,A2),IF(VLOOKUP(A2,Shee t2!A$2:B$15,2,0)B2,VLOOKUP(A2,Sheet2!A$2:B$15,2,0 ),""),"") -- Biff Microsoft Excel MVP "Algeraist" wrote in message ... Hello, I have an excel document with two sheets, sheet1 contains a list of product codes in column A and a date next to each in column B. Sheet2 is almost the same but with different dates and some products aren't on the list. For each product code - In column C on sheet one I want to find the same product code from sheet2 and pick up the date next to it if it is greater than the date in column B. If there is no match I was going to leave the cell blank. I've tried index/match with if statements and lookups but with no success Any ideas.... Thanks in advance |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the next date for a product
try this
put this formula in sheet 1 - Cell C2 =IF(B2=MAX(--(IF((sheet2!$A$2:$A$13=sheet1!A2),(sheet2!$B$2:$B $13)))),"",MAX(--(IF((sheet2!$A$2:$A$13=sheet1!A2),(sheet2!$B$2:$B $13))))) use Ctrl + Shift + Enter On Nov 19, 11:20*am, Algeraist wrote: No it didn't work - Hope I entered it correctly I've pasted the format I tested it on the the function =IF(COUNTIF(Sheet2!A1:A13,A1),IF(VLOOKUP(A1,Sheet2 !A:B,2,0)B1,VLOOKUP(A1,S*heet2!A:B,2,0),""),"") Test data look like this Sheet 1 * * * * A * * * B * * * C 1 * * * Item 1 *3/03/2007 * * * 2 * * * Item 2 *4/03/2007 * * * 3 * * * Item 3 *3/06/2007 * * * 4/03/2008 4 * * * Item 4 *4/03/2007 * * * 5 * * * Item 5 *5/03/2007 * * * 6 * * * Item 6 *6/03/2007 * * * 7 * * * Item 7 *7/03/2007 * * * Sheet 2 * * * * A * * * B 1 * * * item 1 *1/03/2007 2 * * * item 1 *4/03/2007 3 * * * item 1 *5/03/2007 4 * * * item 2 *1/03/2007 5 * * * item 2 *3/03/2008 6 * * * item 2 *3/03/2007 7 * * * item 2 *1/03/2008 8 * * * item 3 *4/03/2008 9 * * * item 3 *1/03/2008 10 * * *item 3 *2/03/2008 11 * * *item 3 *1/07/2008 12 * * *item 3 *23/03/2008 13 * * *item 3 *1/03/2008 "T. Valko" wrote: Try this: =IF(COUNTIF(Sheet2!A$2:A$15,A2),IF(VLOOKUP(A2,Shee t2!A$2:B$15,2,0)B2,VLOOK*UP(A2,Sheet2!A$2:B$15,2, 0),""),"") -- Biff Microsoft Excel MVP "Algeraist" wrote in message ... Hello, I have an excel document with two sheets, sheet1 contains a list of product codes in column A and a date next to each in column B. Sheet2 is almost the same but with different dates and some products aren't on the list. For each product code - In column C on sheet one I want to find the same product code from sheet2 and pick up the date next to it if it is greater than the date in column B. If there is no match I was going to leave the cell blank. I've tried index/match with if statements and lookups but with no success Any ideas.... Thanks in advance- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the next date for a product
No need for the double unary.
=IF(B2=MAX(IF(Sheet2!$A$2:$A$13=Sheet1!A2,Sheet2! $B$2:$B$13)),"",MAX(IF(Sheet2!$A$2:$A$13=Sheet1!A2 ,Sheet2!$B$2:$B$13))) -- Biff Microsoft Excel MVP "muddan madhu" wrote in message ... try this put this formula in sheet 1 - Cell C2 =IF(B2=MAX(--(IF((sheet2!$A$2:$A$13=sheet1!A2),(sheet2!$B$2:$B $13)))),"",MAX(--(IF((sheet2!$A$2:$A$13=sheet1!A2),(sheet2!$B$2:$B $13))))) use Ctrl + Shift + Enter On Nov 19, 11:20 am, Algeraist wrote: No it didn't work - Hope I entered it correctly I've pasted the format I tested it on the the function =IF(COUNTIF(Sheet2!A1:A13,A1),IF(VLOOKUP(A1,Sheet2 !A:B,2,0)B1,VLOOKUP(A1,S*heet2!A:B,2,0),""),"") Test data look like this Sheet 1 A B C 1 Item 1 3/03/2007 2 Item 2 4/03/2007 3 Item 3 3/06/2007 4/03/2008 4 Item 4 4/03/2007 5 Item 5 5/03/2007 6 Item 6 6/03/2007 7 Item 7 7/03/2007 Sheet 2 A B 1 item 1 1/03/2007 2 item 1 4/03/2007 3 item 1 5/03/2007 4 item 2 1/03/2007 5 item 2 3/03/2008 6 item 2 3/03/2007 7 item 2 1/03/2008 8 item 3 4/03/2008 9 item 3 1/03/2008 10 item 3 2/03/2008 11 item 3 1/07/2008 12 item 3 23/03/2008 13 item 3 1/03/2008 "T. Valko" wrote: Try this: =IF(COUNTIF(Sheet2!A$2:A$15,A2),IF(VLOOKUP(A2,Shee t2!A$2:B$15,2,0)B2,VLOOK*UP(A2,Sheet2!A$2:B$15,2, 0),""),"") -- Biff Microsoft Excel MVP "Algeraist" wrote in message ... Hello, I have an excel document with two sheets, sheet1 contains a list of product codes in column A and a date next to each in column B. Sheet2 is almost the same but with different dates and some products aren't on the list. For each product code - In column C on sheet one I want to find the same product code from sheet2 and pick up the date next to it if it is greater than the date in column B. If there is no match I was going to leave the cell blank. I've tried index/match with if statements and lookups but with no success Any ideas.... Thanks in advance- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the next date for a product
Also, if the formula is entered on sheet1 there's no need for the sheet1
name: =IF(B2=MAX(IF(Sheet2!A$2:A$13=A2,Sheet2!B$2:B$13) ),"",MAX(IF(Sheet2!A$2:A$13=A2,Sheet2!B$2:B$13)) ) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... No need for the double unary. =IF(B2=MAX(IF(Sheet2!$A$2:$A$13=Sheet1!A2,Sheet2! $B$2:$B$13)),"",MAX(IF(Sheet2!$A$2:$A$13=Sheet1!A2 ,Sheet2!$B$2:$B$13))) -- Biff Microsoft Excel MVP "muddan madhu" wrote in message ... try this put this formula in sheet 1 - Cell C2 =IF(B2=MAX(--(IF((sheet2!$A$2:$A$13=sheet1!A2),(sheet2!$B$2:$B $13)))),"",MAX(--(IF((sheet2!$A$2:$A$13=sheet1!A2),(sheet2!$B$2:$B $13))))) use Ctrl + Shift + Enter On Nov 19, 11:20 am, Algeraist wrote: No it didn't work - Hope I entered it correctly I've pasted the format I tested it on the the function =IF(COUNTIF(Sheet2!A1:A13,A1),IF(VLOOKUP(A1,Sheet2 !A:B,2,0)B1,VLOOKUP(A1,S*heet2!A:B,2,0),""),"") Test data look like this Sheet 1 A B C 1 Item 1 3/03/2007 2 Item 2 4/03/2007 3 Item 3 3/06/2007 4/03/2008 4 Item 4 4/03/2007 5 Item 5 5/03/2007 6 Item 6 6/03/2007 7 Item 7 7/03/2007 Sheet 2 A B 1 item 1 1/03/2007 2 item 1 4/03/2007 3 item 1 5/03/2007 4 item 2 1/03/2007 5 item 2 3/03/2008 6 item 2 3/03/2007 7 item 2 1/03/2008 8 item 3 4/03/2008 9 item 3 1/03/2008 10 item 3 2/03/2008 11 item 3 1/07/2008 12 item 3 23/03/2008 13 item 3 1/03/2008 "T. Valko" wrote: Try this: =IF(COUNTIF(Sheet2!A$2:A$15,A2),IF(VLOOKUP(A2,Shee t2!A$2:B$15,2,0)B2,VLOOK*UP(A2,Sheet2!A$2:B$15,2, 0),""),"") -- Biff Microsoft Excel MVP "Algeraist" wrote in message ... Hello, I have an excel document with two sheets, sheet1 contains a list of product codes in column A and a date next to each in column B. Sheet2 is almost the same but with different dates and some products aren't on the list. For each product code - In column C on sheet one I want to find the same product code from sheet2 and pick up the date next to it if it is greater than the date in column B. If there is no match I was going to leave the cell blank. I've tried index/match with if statements and lookups but with no success Any ideas.... Thanks in advance- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the next date for a product
"T. Valko" wrote: Also, if the formula is entered on sheet1 there's no need for the sheet1 name: =IF(B2=MAX(IF(Sheet2!A$2:A$13=A2,Sheet2!B$2:B$13) ),"",MAX(IF(Sheet2!A$2:A$13=A2,Sheet2!B$2:B$13)) ) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... No need for the double unary. =IF(B2=MAX(IF(Sheet2!$A$2:$A$13=Sheet1!A2,Sheet2! $B$2:$B$13)),"",MAX(IF(Sheet2!$A$2:$A$13=Sheet1!A2 ,Sheet2!$B$2:$B$13))) -- Biff Microsoft Excel MVP "muddan madhu" wrote in message ... try this put this formula in sheet 1 - Cell C2 =IF(B2=MAX(--(IF((sheet2!$A$2:$A$13=sheet1!A2),(sheet2!$B$2:$B $13)))),"",MAX(--(IF((sheet2!$A$2:$A$13=sheet1!A2),(sheet2!$B$2:$B $13))))) use Ctrl + Shift + Enter On Nov 19, 11:20 am, Algeraist wrote: No it didn't work - Hope I entered it correctly I've pasted the format I tested it on the the function =IF(COUNTIF(Sheet2!A1:A13,A1),IF(VLOOKUP(A1,Sheet2 !A:B,2,0)B1,VLOOKUP(A1,SÂ*heet2!A:B,2,0),""),"") Test data look like this Sheet 1 A B C 1 Item 1 3/03/2007 2 Item 2 4/03/2007 3 Item 3 3/06/2007 4/03/2008 4 Item 4 4/03/2007 5 Item 5 5/03/2007 6 Item 6 6/03/2007 7 Item 7 7/03/2007 Sheet 2 A B 1 item 1 1/03/2007 2 item 1 4/03/2007 3 item 1 5/03/2007 4 item 2 1/03/2007 5 item 2 3/03/2008 6 item 2 3/03/2007 7 item 2 1/03/2008 8 item 3 4/03/2008 9 item 3 1/03/2008 10 item 3 2/03/2008 11 item 3 1/07/2008 12 item 3 23/03/2008 13 item 3 1/03/2008 "T. Valko" wrote: Try this: =IF(COUNTIF(Sheet2!A$2:A$15,A2),IF(VLOOKUP(A2,Shee t2!A$2:B$15,2,0)B2,VLOOKÂ*UP(A2,Sheet2!A$2:B$15,2 ,0),""),"") -- Biff Microsoft Excel MVP "Algeraist" wrote in message ... Hello, I have an excel document with two sheets, sheet1 contains a list of product codes in column A and a date next to each in column B. Sheet2 is almost the same but with different dates and some products aren't on the list. For each product code - In column C on sheet one I want to find the same product code from sheet2 and pick up the date next to it if it is greater than the date in column B. If there is no match I was going to leave the cell blank. I've tried index/match with if statements and lookups but with no success Any ideas.... Thanks in advance- Hide quoted text - - Show quoted text - These seem to return the max date but I'm after the date that is = to the one on the first sheet. no one seems to be able to solve this - i've had countless pps looking at it |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the next date for a product
Well, if you can explain it in a way that I'll understand it, I'll be able
to solve it! So, you don't want the max date? You want the *next* date that is greater than the date on sheet1 if there is one? For example: sheet1B2 = 1/1/2008 (m/d/y) sheet2 = (m/d/y) 1/1/2008 1/3/2008 1/7/2008 So, the correct result for this example would be 1/3/2008 ? -- Biff Microsoft Excel MVP "Algeraist" wrote in message ... "T. Valko" wrote: Also, if the formula is entered on sheet1 there's no need for the sheet1 name: =IF(B2=MAX(IF(Sheet2!A$2:A$13=A2,Sheet2!B$2:B$13) ),"",MAX(IF(Sheet2!A$2:A$13=A2,Sheet2!B$2:B$13)) ) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... No need for the double unary. =IF(B2=MAX(IF(Sheet2!$A$2:$A$13=Sheet1!A2,Sheet2! $B$2:$B$13)),"",MAX(IF(Sheet2!$A$2:$A$13=Sheet1!A2 ,Sheet2!$B$2:$B$13))) -- Biff Microsoft Excel MVP "muddan madhu" wrote in message ... try this put this formula in sheet 1 - Cell C2 =IF(B2=MAX(--(IF((sheet2!$A$2:$A$13=sheet1!A2),(sheet2!$B$2:$B $13)))),"",MAX(--(IF((sheet2!$A$2:$A$13=sheet1!A2),(sheet2!$B$2:$B $13))))) use Ctrl + Shift + Enter On Nov 19, 11:20 am, Algeraist wrote: No it didn't work - Hope I entered it correctly I've pasted the format I tested it on the the function =IF(COUNTIF(Sheet2!A1:A13,A1),IF(VLOOKUP(A1,Sheet2 !A:B,2,0)B1,VLOOKUP(A1,S*heet2!A:B,2,0),""),"") Test data look like this Sheet 1 A B C 1 Item 1 3/03/2007 2 Item 2 4/03/2007 3 Item 3 3/06/2007 4/03/2008 4 Item 4 4/03/2007 5 Item 5 5/03/2007 6 Item 6 6/03/2007 7 Item 7 7/03/2007 Sheet 2 A B 1 item 1 1/03/2007 2 item 1 4/03/2007 3 item 1 5/03/2007 4 item 2 1/03/2007 5 item 2 3/03/2008 6 item 2 3/03/2007 7 item 2 1/03/2008 8 item 3 4/03/2008 9 item 3 1/03/2008 10 item 3 2/03/2008 11 item 3 1/07/2008 12 item 3 23/03/2008 13 item 3 1/03/2008 "T. Valko" wrote: Try this: =IF(COUNTIF(Sheet2!A$2:A$15,A2),IF(VLOOKUP(A2,Shee t2!A$2:B$15,2,0)B2,VLOOK*UP(A2,Sheet2!A$2:B$15,2, 0),""),"") -- Biff Microsoft Excel MVP "Algeraist" wrote in message ... Hello, I have an excel document with two sheets, sheet1 contains a list of product codes in column A and a date next to each in column B. Sheet2 is almost the same but with different dates and some products aren't on the list. For each product code - In column C on sheet one I want to find the same product code from sheet2 and pick up the date next to it if it is greater than the date in column B. If there is no match I was going to leave the cell blank. I've tried index/match with if statements and lookups but with no success Any ideas.... Thanks in advance- Hide quoted text - - Show quoted text - These seem to return the max date but I'm after the date that is = to the one on the first sheet. no one seems to be able to solve this - i've had countless pps looking at it |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Where can I find a multiple product worksheet for a sales route? | New Users to Excel | |||
| Where to find Product Key?(preinstalled) | Setting up and Configuration of Excel | |||
| Using VLOOKUP to find product from 3 worksheets to put on one shee | Excel Worksheet Functions | |||
| a map to show others where to find product on shelves | Excel Discussion (Misc queries) | |||
| How to find the largest product of an array of values? | Excel Worksheet Functions | |||
Linear Mode
