Compare expiry dates of items between two sheets

xlnab

New Member
Joined
Sep 16, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
Hello!
At home, I have a seed storage for gardening porpouses.
It so happens that sometimes I recieve seeds with older expiry dates than I already have.
Could someone help me, to some way, compare the seeds expiry dates that I have in storage with the expiry dates of the shipments I recieve in order to use the seed before they expire.
Thank you in advance.
Examples Bellow.
 

Attachments

  • Storage.png
    Storage.png
    21.2 KB · Views: 5
  • Arrivals Sheet.png
    Arrivals Sheet.png
    8.5 KB · Views: 5

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,550
This will add the seeds from Arrival to Storage, clear the arrival data and sort the storage sheet

VBA Code:
Sub addShipment()

Dim arr As Worksheet
Dim sto As Worksheet
Dim LastRow1 As Long
Dim LastRow2 As Long

Set arr = Sheets("Arrivals")
Set sto = Sheets("Storage")

LastRow1 = arr.Cells(arr.Rows.Count, "A").End(xlUp).Row
LastRow2 = sto.Cells(sto.Rows.Count, "A").End(xlUp).Row

arr.Range("A3:C" & LastRow1).Copy sto.Range("A" & LastRow2 + 1)
arr.Range("A3:D" & LastRow1).ClearContents

LastRow2 = sto.Cells(sto.Rows.Count, "A").End(xlUp).Row
sto.Range("A2:C" & LastRow2).Sort key1:=Columns("A"), Order1:=xlAscending, Key2:=Columns("B"), Order2:=xlDescending, Key3:=Columns("C"), Order3:=xlDescending, Header:=xlYes


End Sub
 

xlnab

New Member
Joined
Sep 16, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
This will add the seeds from Arrival to Storage, clear the arrival data and sort the storage sheet

VBA Code:
Sub addShipment()

Dim arr As Worksheet
Dim sto As Worksheet
Dim LastRow1 As Long
Dim LastRow2 As Long

Set arr = Sheets("Arrivals")
Set sto = Sheets("Storage")

LastRow1 = arr.Cells(arr.Rows.Count, "A").End(xlUp).Row
LastRow2 = sto.Cells(sto.Rows.Count, "A").End(xlUp).Row

arr.Range("A3:C" & LastRow1).Copy sto.Range("A" & LastRow2 + 1)
arr.Range("A3:D" & LastRow1).ClearContents

LastRow2 = sto.Cells(sto.Rows.Count, "A").End(xlUp).Row
sto.Range("A2:C" & LastRow2).Sort key1:=Columns("A"), Order1:=xlAscending, Key2:=Columns("B"), Order2:=xlDescending, Key3:=Columns("C"), Order3:=xlDescending, Header:=xlYes


End Sub
My thanks for your effort but that code does not fit my explanation above.
EDIT: I tested the code and it gives an error in the last line "sto.Range("A2:C...". I was hoping for something that would alert me each line. Because I gave a sample with a few items, I have hundreads of seeds, and If there is a little text saying "The seeds that arrived have the closest expiry date" would make all the diference.
 
Last edited:

xlnab

New Member
Joined
Sep 16, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
Considering my last reply, does anyone have any idea how can this be achieved?
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,550
Would say it fits your description quite well

"Could someone help me, to some way, compare the seeds expiry dates that I have in storage with the expiry dates of the shipments I recieve in order to use the seed before they expire."

As far as a message goes, this formula could be used in say column E of your storage sheet.

=IF(C3>MAXIFS(Storage!C:C,Storage!B:B,Arrivals!B3),"The seeds that arrived have the closest expiry date","")

Or if you don't have MAXIFS

=IF(C3>AGGREGATE(14,6,Storage!C:C/(Storage!B:B=Arrivals!B3),1),"The seeds that arrived have the closest expiry date","")
 
Solution

xlnab

New Member
Joined
Sep 16, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
Would say it fits your description quite well

"Could someone help me, to some way, compare the seeds expiry dates that I have in storage with the expiry dates of the shipments I recieve in order to use the seed before they expire."

As far as a message goes, this formula could be used in say column E of your storage sheet.

=IF(C3>MAXIFS(Storage!C:C,Storage!B:B,Arrivals!B3),"The seeds that arrived have the closest expiry date","")

Or if you don't have MAXIFS

=IF(C3>AGGREGATE(14,6,Storage!C:C/(Storage!B:B=Arrivals!B3),1),"The seeds that arrived have the closest expiry date","")
Sorry I took a while to test the formulas AND I never meant to be rude. These two formulas do exactly what I was looking for. Thank you very much! I would never get there without your help!

EDIT: Forgot to tell, I just changed the place where the message is, like this "=IF(C3>AGGREGATE(14,6,Storage!C:C/(Storage!B:B=Arrivals!B3),1),"","The seeds that arrived have the closest expiry date")"... Voilá! Thank you again!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,886
Messages
5,574,834
Members
412,620
Latest member
sharma7s
Top