Excel: Select Data based on date, and sort multiple instances

[attrofy]

I have a database program that extracts breeding data for animals. However, the data is ugly to say the least, and I need to sort my animals by name based on their latest breed date. This data will be continually updated from the database program, so I need something with relatively simple cut and paste procedures to apply to new CSV export sheets.

The breed data is actually spit out on two seperate files.

Dam Name    Registration       Stud Name              Bred Date            Due Date 
Gwen1           12345           Bob1                   6/20/06              6/01/07 
Gwen1           12345           Fred1                  6/14/06 
Gwen1           12345           Bob1                   6/05/06 
Linda25         67890           Bob1                   6/21/06              6/2/07 
Linda25         67890           Bill398                6/02/06 
Linda891        90123           Bill398                8/25/05              8/13/06 
Linda891        90123           Bill398                5/14/04              5/1/05

Assuming Today's date is around 7/13/06, what I need to produce is a working list of pregnant Dam's (only one instance of each Dam) in alphabetical order, based on their Due Date. As can be seen, some animals have been bred previously to the same studs, and their due dates have come and gone. So I am looking for the most current Due Date - regardless if it has passed or not. It is possible that some females are over their due date - which poses a bit of problem (from a coding standpoint).

In short I am looking for the following:

Dam Name     Registration    Stud Name    Bred Date    Due Date
Gwen1          12345           Bob1        6/20/06      6/01/07
Linda25         67890          Bob1        6/21/06      6/2/07 
Linda891        90123          Bill398     8/25/05      8/13/06

As stated previously, there are two files that are produced, the second file contains the testing info, and methods used to verify pregnancy. That will be the second part of the equation, but a little heads up, there are multiple procedures on the same date. The layout is almost identical with multiple rows of the same animal. But that will be addressed in part two.

I hope that is a clear enough explanation of what I am looking to do. I was getting close with Vlookup and Hlookup, and I recall seeing an example at one point of using the two functions together, making one a variable (array) of the other, but can't find the example.

I havetried a Pivot Table with little to no luck.

Thanks in advance for any help/suggestions.

 

[firefytr]

Hi there,

So are you actually looking to create a new sheet of data that consists only of data that is populated with the Due Date and sorted as such? And in your second example, what happened to the second instance of Linda891? Does that not meet criteria? Can you confirm what you want as an output for this?

Also, will this be the same file everytime? Will the file always be open? Do you want to choose which file to run this on? If so, will it be the same sheet? A named sheet or the same location?

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication.
- Leonardo da Vinci

 

[attrofy]

Hi firefytr,

The second example, Linda891 only shows the most recent date - compared to Today or Now()- which eliminates any older data. So in her case, she was bred to the same stud in 2004 and 2005. Obviously, the 2004 baby has been born, and the 2005 baby will be coming in August of 2006. So both the Bred Date and Due Date do play significant roles. However, the info should correspond so that the most recent Bred Date will match the most recent Due Date. However, there are some instances where the female was not bred on our ranch (purchased already pregnant), and we only have an estimated Due Date.

It will be the same file everytime, but it will be a new instance of it. For ease of use, I can name it the same, and insert the same headers, but the raw data spits out as a CSV file. As stated there will be two files in use - but I will get to the second one based on what I can do with the first one.

Will the file always be open? Hmmm...only when manipulating the data. My end result is to make a useable list sorted alphabetically by Dam name, that shows the Due Date and Bred Date. The database we use puts out a list based on Due Date only - so it shows who is due first. Although this is helpful, it is much harder to find when a particular animal is due. This is handy when doing ultrasounds, shots, etc and the animal is in the examining room.

Do I want to choose which file to run this on? Not sure I understand how it would apply. I assume you are asking becasue I stated the second file in question. The second file contains a similar problem, but it lists all of the medical procedures that have been performed on that animal, including how we derived she was pregnant, method used, date, meds given, etc. Similar function, different criteria (and layout). However, the same multiple listings of the same animal applies. Not trying to confuse the issue, but since you asked, this is what the other file looks like:

Dam Name       ARI	 Last Date	Sire	     Last Procedure	Notes     Dam:Sire Code
GENEVIEVE	812220	6/19/2006	GASTON	     Ultrasound	  YES		  57:214
GENEVIEVE	812220	6/19/2006	GASTON	     PregTone	    YES		  57:214
GENEVIEVE	812220	3/31/2006	GASTON	     PregTone	    YES		  57:214
LA LUCIE	 148306	6/27/2006	BLACKMAGIC     Spit		    WENT DOWN	86:576
LA LUCIE	 148306	6/27/2006	BLACKMAGIC     Bred		    HAND BRED	86:576
LA LUCIE	 148306	4/16/2006	MIDAS TOUCH    PregTone	    YES		  86:56:00
LA LUCIE	 148306	4/10/2006	MIDAS TOUCH    PregTone	    YES		  86:56:00
LA LUCIE	 148306	3/31/2006	MIDAS TOUCH    PregTone	    UNC		  86:56:00
LA LUCIE	 148306	5/20/2005	MIDAS TOUCH    Bred		    HAND BRED	86:56:00
LINDA		161181	6/27/2006	BRUNO	      Spit		    YES		  230:524
LINDA		161181	6/16/2006	BRUNO	      Spit		    YES		  230:524
LINDA		161181	6/12/2006	BRUNO	      Spit		    YES		  230:524
LINDA		161181	5/31/2006	POPEYE	     PregTone	    YES		  230:148
LINDA		161181	10/26/2005   POPEYE	     PregTone	    YES		  230:148
LINDA		161181	10/7/200     POPEYE	     PregTone	    YES		  230:148

BTW, I have no idea what the Dam:Sire code is, it is a number that the database inserts automatically. I believe it is an internal marker that allows the individual animals to stay associated with certain functions (sort of a "Primary Key" or individual ID number - it is Mac logic (File Maker Pro) - so go figure....) But for all intents and purposes, it will probably be eliminated.

Will it be the same sheet or same location? Ideally, I would like to import the second criteria into the same workbook on a different tab. Then any data that I need from the procedures, and from the breeding detail, I can create on the fly as needed.

I hope that answers your questions. Anything else, let me know. Thanks for the quick response. Looking forward to another fabulous bit of coding magic.

 

[firefytr]

LOL! I don't know about magic, but I hope VBA will suffice. Just let me make sure I have this straight before I go writing something that doesn't apply.

[ol A]
[li]You will import data into a spreadsheet from a CSV and it will be lined out as you've posted above (first file).[/li]
[li]The new data shall be put on a new worksheet in the same file that is open at the time of (the procedure) running.[/li]
[li]The new data shall be sorted first by 'Dam name', then 'Bred date' followed by 'Due date'.[/li]
[li]Only the first instance of each item shall be kept and the rest shall be deleted (only from the new data, keeping the original data intact).[/li]
[/ol]

Is that pretty much it? What exactly have I fangled up?

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication.
- Leonardo da Vinci

 

[attrofy]

That is pretty much it - only caveat - item C - sort order would go 'Dam Name', 'Due Date', and bred date is irrelevant - but it could be sorted as the third variable.

Lastly, sort of the next phase would be to incorporate the most recent medical procedure in the same worksheet Date, Sire (maybe redundant), Last Procedure, and Notes. Of course, this is probably pressing my luck, so we will address it at a later date.

Lastly, there are more columns in the CSV output of the first sheet - not sure it will matter. I can get the file reduced down to just the above listed columns in the listed order.

Thanks again.

 

[firefytr]

We can include those columns if you'd like, or we could just copy those columns indicated (I'm assuming those columns are the first one's imported in the structure you posted above, if not, specify). As for the other that you want, it should be possible, but I may need more test data; if it comes to it, you can email it to me.

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a Microsoft MVP?
- Leonardo da Vinci

 

[attrofy]

I would have to rexport the data to verify that these are the first columns. I had edited it to be useable, so it is possible I deleted or shifted some columns. We will give this a go, if it doesn't work like I am hoping, I will email you.

Thanks again for the time and effort.

 

[firefytr]

If you do know what columns they are we can deal with that as well and just copy those columns over to the new worksheet.

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a Microsoft MVP?
- Leonardo da Vinci

 

[attrofy]

ok on the second sheet - ("Breeding_Detail.csv") there is an additional column not listed - it is "Minutes". It falls between 'last procedure' and 'notes'. This field will most likely be irrelevant, as there are only two entries in the entire spreadsheet. So it will be eliminated. Other then that, this sheet is as listed, in the order shown.

The first sheet ("Breeding.csv")

Column     Title
A          Dam Full Name
B          ARI
C          Sire
D          Bred Date
E          Due Date
F          Cria DOB - cria is a baby alpaca
G          Days Pregnant
H          Cria Name
I          Cia Sex
J          Cria Color
K          Dam:Sire code
L          Unknown1 - appears to be a boolean field of 1 or 0 
M          Unknown2 - appears to be a holder for the Cria Name (either "current" - if currently pregnant - or the Cria DOB if a cria has been born)

Since this is mostly trying to find Due Dates, we aren't too concerned about anything past column F. Just trying to sort the list alphabetically, and show the last occurence of each medical procedure. Of course this is opening a can of worms, because inevitably, once this info is in their hands, they will want to modify the data in some way or other, or show another bit of data.

Anyway, thanks again. Let me know if you need anything else or some sample files via email.

 

[firefytr]

You know this should really be in an Access database. Just fyi. And you want columns A:E, yes?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[attrofy]

I know - we have gone down that route. But the problem, all we are trying to do is manipulate a report. It is hard to justify the creation of a an entire database for such limited info. The problem is the way the current database links info, and exports the data. It doesn't normalize the data or the fields, and as such, is not a true relational database (from my understanding). Because of this, we get crap export routines that are almost unusable. We have contemplated several times switching programs, and have tried various ones, but none will work for our needs. That only leaves us the choice of creating a database from scratch. Too time intensive, and too over my head to deal witht he maintenance and upkeep required - not to mention the changes that will abound.

As stated from the outset, the primary goal is to list the dams alphabetically by due date. The other info is extraneous, and can be put off until later, or through a diffrent method - it doesn't have to be included in this spreadsheet. Even if we treat the two spreadsheets seperately, that will suffice.

 

[KenWright]

Just for the hell of it, and for part 1 only

When you said you had tried Pivot tables but with no luck....

Assuming your example data is in A1:E8, create 3 more columns at the end of the data and put in the following data:-

F1: "Due Date2"
F2: =IF(E2="","",SUMPRODUCT(MAX($E$2:$E$8*($A$2:$A$8=A2))))

G1: "Bred Date2"
G2: =IF(F2="","",SUMPRODUCT(--($A$2:$A$8=A2),--($E$2:$E$8=F2),$D$2:$D$8))

H1: "Pregnant"
H2: =IF(E2="","No","Yes")

Now copy dthose formulas down and then throw a Pivot table at it and drag "Pregnant" to the Page fields and select 'Yes' from the Page Field dropdown.

Now drag in order to the ROW fields such that each one is in the ROW fields and just to the right of the preceding one.

Dam Name
Registration
Stud Name
Bred Date2
Due Date2

Then just to tidy up a bit, drag Dam Name into the DATA field as well (Don't need the data but gets rid of the big ugly box and gives you a count of that animal)

Now finally lose all the automatic subtotals on each field (Just right click on each field, choose 'Field settings' and then choose 'none' for subtotals)

Done

Ranges could be expanded automatically using dynamic ranges etc, so all you would have to do is drop in the new data into columns A:E and ensure the formulas in F:H were copied down accordingly.

Regards
Ken..............



----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[attrofy]

Ken,

That worked pretty well - only issue is my Bred to and Due dates both read "(blank)". But when I cklick on the header cell, it shows the entire range of dates, just as the other cell headers do. I have swaped out Due Date with Due Date2 and Bred Date with Bred Date2, but it gives the same result. If I use the "2's" it doesnt say the word Blank, it has nothing in the cell. I am guessing because these values are calculated and not populated in the cell before the Pivot table makes the calculation....but that could be a bad guess. I am not really sure what is gained by repeating the same data (other then to calculate the "Yes/No" of the Pregnant field). In otherwords, why not just use the data that exists? I understand that there will be no data to "pivot" around without the Pregnant column, and that unles there is something calculated, it will just be a blank cell. But like I stated from the outset, I don't really understand pivot tables, so there might be something obvious I am missing.

Count of Dam Full Name					
Dam Full Name	             ARI	   Sire	Bred Date	Due Date	Total
ACERO MARKA’S A LA LUCIE	148306	ATTECUS	(blank)	  (blank)	1
ACERO MARKA’S LINDA	     161181	POPEYE	 (blank)	  (blank)	1
ACERO MARKA’S RENE’	     161266	HCL TYLE   (blank)	  (blank)	2
                                      JACOB      (blank)      (blank)	1
ADREAM STORM	            1046243   (blank)	(blank)	  (blank)	1
ATAHUALPA	               809887	CONE	   (blank)	  (blank)	1

Otherwise, it seems to be working well for the parts that are showing.

 

[KenWright]

The reason to 'duplicate' SOME of the data is that what Due date2 does is to put the latest due date against every record for that animal, and then Bred Date2 puts the Bred date required against every animal. If you don't do this then when you pivot the data based on name, you will end up with a single record for every entry against that animal because it will display a record in the table for each and every different date for that animal. So by creating two new fields that each contain only the right Due Date and only the right Bred date for each animal, you only get the one record.

Data should like this

Dam Name	Reg  	StudNm 	Bred Date	Due Date	Due Date2	Bred date2	Preg
Gwen1   	12345	Bob1   	20/06/2006	01/06/2007	01/06/2007	20/06/2006	Yes
Gwen1   	12345	Fred1  	14/06/2006	        	         	          	No
Gwen1   	12345	Bob1   	05/06/2006	        	         	          	No
Linda25 	67890	Bob1   	21/06/2006	02/06/2007	02/06/2007	21/06/2006	Yes
Linda25 	67890	Bill398	02/06/2006	        	         	          	No
Linda891	90123	Bill398	25/08/2005	13/08/2006	13/08/2006	25/08/2005	Yes
Linda891	90123	Bill398	14/05/2004	01/05/2005	13/08/2006	25/08/2005	Yes

Don't know why you would get blanks, as I just copied and pasted your data, and then copied and pasted my formulas from my post into the table and i get all the correct dates?

Is your data A1:E8 including headers?

Regards
Ken..................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

And if you want an even easier solution, then just select all your original data, sort on Dam name in Ascending order AND Due Date in DESCENDING order. Now just add a final column in column F called Count, put in a formula into F2 of =COUNTIF($A$1:$A2,A2) and copy down as far as needed.

Now just apply Data / Filter / Autofilter to Column F and filter on the 1s. Job done.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[attrofy]

Ken
thanks for the input, I will try this out when I can. Unfortunately, our Server crashed Friday evening, so it has been a long "weekend plus"....

I will get to this in a day or two when the server is back 100% (I hope).

 

[KenWright]

LOL - Good luck with that

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------