ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Find the next date for a product (https://www.excelbanter.com/excel-discussion-misc-queries/210851-find-next-date-product.html)

Algeraist
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

T. Valko
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



Algeraist
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




T. Valko
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

muddan madhu
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 -

T. Valko
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 -

T. Valko
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 -

Algeraist
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

T. Valko
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




All times are GMT +1. The time now is 07:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com