Index IsNumber Data Anaysis

[dunmore83]

Hoping you might be able to provide some assistance on another part of the same workbook?!! If only you were in Australia I could attempt to send you some fresh milk

Anyways if your willing the 1st part I need you to look at is the formula in column D of the 1st worksheet... the problem here is that I don't want the rows with no data. Is it possible somehow to have only have rows when the formula returns a numerical value ??

https://www.dropbox.com/s/75bv2uwr7nbhtsh/Gaz-AI-Analysis.xlsx?dl=0

 

[dunmore83]

Do you mean in the Alpro-Data worksheet ?

 

[gaz_chops]

Yes in AI-Alrpo-Data

 

[dunmore83]

Ok do you need me to give each column a heading? One will be a hidden column when finished

 

[dunmore83]

I have another worksheet which needs some more formulas. I owe you something!!!

In PD-01-Dates I need formulas in the cells highlighted green as follows;

G10
- the number of times "E" is found between the dates in G8 & G9 inclusive. These dates are in column S
G11
- the number of times "P" is found between the dates in G8 & G9 inclusive. These dates are in column S
G13
- sum the number in column G for the dates between G8 & G9 inclusive. These dates are in column S
S8/S9
- For any number found in column D between the dates in G8 & G9 that has either a "E" or "P" in column V then I need the total number of inseminations from the AI-Dates worksheet BUT only those that match the entries in P8 & P9 i.e. Farm West is found in column M of the AI-Dates worksheet
V8/V9
- if the cow was inseminated by the entries in P8/P9 (found from column M in the AI-Dates worksheet) & has a ""P in column V then this qualifies for this formula
Y8/Y9
- if the cow was inseminated by the entries in P8/P9 (found from column M in the AI-Dates worksheet) & has a ""P in column V then this qualifies for this formula

Clear as mud?!!!

https://www.dropbox.com/s/jcftf4a6vzlmnu8/PD-Dates.xlsx?dl=0

Thanks!!!!!!

 

[gaz_chops]

This is the data to the right, no formulas just data!

There are 2 empty columns between Breeding Status & Cow No.

[TABLE="width: 418"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]21-Jul-15[/TD]
[/TR]
[TR]
[TD]Breeding Status[/TD]
[TD][/TD]
[TD][/TD]
[TD]Cow No.[/TD]
[TD]Milk Time Yest 1[/TD]
[TD]Milk Time Yest 2[/TD]
[/TR]
[TR]
[TD]Fresh[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]335[/TD]
[TD="align: right"]8:21:33 AM[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Fresh[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]568[/TD]
[TD="align: right"]8:20:54 AM[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Pregnant[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]770[/TD]
[TD="align: right"]8:20:46 AM[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Pregnant, Dry[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3024[/TD]
[TD="align: right"]8:20:37 AM[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]

I'll look at the new sheet once i finish on this one.

 

[gaz_chops]

I've added 2 buttons on the Analysis sheet, Sort A-Z & Sort Z-A, I also added 2 column headers Exclude & Date as they where blank.

Let me know if this is what you wanted.

https://www.dropbox.com/s/cgfddedu5zqigeu/Gaz-AI-Analysis-AtoZ.xlsm?dl=0

 

[gaz_chops]

Should have said, the data to the right is ignored by the data sort, take it thats OK?

I've done the 1st 3 formulas on the new sheet.
To do the last 3 formulas it needs a new column added to PD-01-Dates sheet for the "Tech" name, then I can lookup the Tech.

https://www.dropbox.com/s/54yved1r10wilx5/PD-Dates.xlsx?dl=0

 

[dunmore83]

This one gets a little tricky as when there are more than 1 inseminations it should be the tech name of the most recent insemination date

No worries to add a column for tech name if you can't incorporate directly into the formulas

 

[dunmore83]

The straws used needs only to sumif a E or P is found in column V i.e. needs to exclude summing anything else such as Recheck or cull

 

[gaz_chops]

All done, double check it looks ok.

Did you manage to check the Analysis sheet sort, does it work OK?

https://www.dropbox.com/s/54yved1r10wilx5/PD-Dates.xlsx?dl=0