how to count the days between 2 dates based on the unique number

[Seifeddine87]

hi
how to count the days between 2 dates based on the unique number
Example

Sale sheet

........A................B ........................C
1.....Date.......Item Number........ No Of Days..
2 28/12/16..........3TU55...................23
3 25/12/16..........K55P3...................18
4 24/12/16..........K8Y44...................19
5
6

Stock sheet

........A................B
1.....Date.......Item Number
2 07/12/16..........K55P3
3 05/12/16..........K8Y44
4 05/12/16..........3TU55
5
6
Thank you

 

[Aladin Akyurek]

In C2 of Sale enter and copy down:

=A2-LOOKUP(9.99999999999999E+307,1/(stock!$B$2:$B$4=B2),stock!$A$2:$A$4)

Format the formula cells as appropriate.

 

[JLGWhiz]

Assuming that the item number will only appear once on each sheet:
Macro.

Sub daysgone()
Dim c As Range, fn As Range
With Sheets("Sale") 'Edit sheet name
    For Each c In .Range("B2", .Cells(Rows.Count, 2).End(xlUp))
        Set fn = Sheets("Stock").Range("B:B").Find(c.Value, , xlValues, xlWhole) 'Edit sheet name
            If Not fn Is Nothing Then
                c.Offset(, 1) = fn.Offset(, -1).Value - c.Offset(, -1).Value
            End If
    Next
End With
End Sub

 

[Seifeddine87]

sorry but why 9.99999999999999E+307,1 just i want to understand it thank you for your reply

 

[Aladin Akyurek]

sorry but why 9.99999999999999E+307,1 just i want to understand it thank you for your reply

The LOOKUP bit picks out the last date (number/numeric value) associated with an item. To that end, a limit indicating constant of Excel is used as the look up value.

See:
http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html
http://www.mrexcel.com/forum/excel-questions/99621-lookup-value-unsorted-data.html#post492425
http://www.mrexcel.com/forum/excel-...tiple-matches-match-returned.html#post1523998

 

[T. Valko]

sorry but why 9.99999999999999E+307

You can replace that long number with the number 2.

 

[Seifeddine87]

In C2 of Sale enter and copy down:

=A2-LOOKUP(9.99999999999999E+307,1/(stock!$B$2:$B$4=B2),stock!$A$2:$A$4)

Format the formula cells as appropriate.

hi its work perfect thank you
could you tell me why 9.99999999999999E+307,1/ just to understand it

 

[Aladin Akyurek]

hi its work perfect thank you
could you tell me why 9.99999999999999E+307,1/ just to understand it

The following link explains it: http://www.mrexcel.com/forum/excel-questions/99621-lookup-value-unsorted-data.html#post492425. Hope this helps.