compare data
 

hi, please help

i have a database something like the below, (but 36,000+ rows/animals)
and i need to run a script to verify that Dams are female (and Sires are male)
i need column 5(E) (here) to index row A and match with row D - similar to
=IF(X2="","",INDEX(Y:Y,MATCH(X2,Z:Z,0)))
but only return her name if her sex in B = Dog (or simply say ERROR)

i.e. search the Dam's name cloumn 4 (D)
find her name in column 1 (A) -
compare column 2 (B) and report back in colum 5(E) if she is not a "bitch"
example "Le Grange Tessa" is a dam - but is a dog

Name Sex Sire Dam
Bob At Quovadis Dog Roodebult Simba Le Grange Tessa
Roodebult Simba Dog Byrne Bobby Sebastiaan Tessa
Byrne Bobby Dog Mizpah Thor Slamat Lyla
Mizpah Thor Dog
Slamat Lyla Bitch
Sebastiaan Tessa Bitch Sebastiaan Monty Hansom Sophfie
Sebastiaan Monty Dog
Hansom Sophfie Bitch
Le Grange Tessa Dog Ferrou Bruno Jess Cloe


regards
EngelseBoer

compare data
 

Yuo can use a conditional format in column C & D with a simple Vlookup

in C1 conditional formating
=IF(VLOOKUP(D1,A1:B100,2)="Bitch",TRUE,FALSE)

in D1 conditional formating
=IF(VLOOKUP(D1,A1:B100,2)="Dog",TRUE,FALSE)

copy C1 and D1 down the columns using Copy, then pastespecial with Formt
Option. Change the bottom row of formula A1:B100 to match the last row of
your data.


"EngelseBoer" wrote:


hi, please help

i have a database something like the below, (but 36,000+ rows/animals)
and i need to run a script to verify that Dams are female (and Sires are male)
i need column 5(E) (here) to index row A and match with row D - similar to
=IF(X2="","",INDEX(Y:Y,MATCH(X2,Z:Z,0)))
but only return her name if her sex in B = Dog (or simply say ERROR)

i.e. search the Dam's name cloumn 4 (D)
find her name in column 1 (A) -
compare column 2 (B) and report back in colum 5(E) if she is not a "bitch"
example "Le Grange Tessa" is a dam - but is a dog

Name Sex Sire Dam
Bob At Quovadis Dog Roodebult Simba Le Grange Tessa
Roodebult Simba Dog Byrne Bobby Sebastiaan Tessa
Byrne Bobby Dog Mizpah Thor Slamat Lyla
Mizpah Thor Dog
Slamat Lyla Bitch
Sebastiaan Tessa Bitch Sebastiaan Monty Hansom Sophfie
Sebastiaan Monty Dog
Hansom Sophfie Bitch
Le Grange Tessa Dog Ferrou Bruno Jess Cloe


regards
EngelseBoer

compare data
 

nevermind -- i figured it out

"EngelseBoer" wrote:


hi, please help

i have a database something like the below, (but 36,000+ rows/animals)
and i need to run a script to verify that Dams are female (and Sires are male)
i need column 5(E) (here) to index row A and match with row D - similar to
=IF(X2="","",INDEX(Y:Y,MATCH(X2,Z:Z,0)))
but only return her name if her sex in B = Dog (or simply say ERROR)

i.e. search the Dam's name cloumn 4 (D)
find her name in column 1 (A) -
compare column 2 (B) and report back in colum 5(E) if she is not a "bitch"
example "Le Grange Tessa" is a dam - but is a dog

Name Sex Sire Dam
Bob At Quovadis Dog Roodebult Simba Le Grange Tessa
Roodebult Simba Dog Byrne Bobby Sebastiaan Tessa
Byrne Bobby Dog Mizpah Thor Slamat Lyla
Mizpah Thor Dog
Slamat Lyla Bitch
Sebastiaan Tessa Bitch Sebastiaan Monty Hansom Sophfie
Sebastiaan Monty Dog
Hansom Sophfie Bitch
Le Grange Tessa Dog Ferrou Bruno Jess Cloe


regards
EngelseBoer

compare data
 

thanks Joel,
in my application i used
=IF(C2="Dog","",IF(E2="","",INDEX(D:D,MATCH(A2,D:D ,0))))

where
column C = Dog/Bitch
column E = Sire's reg #
Column D = Sire's Name
Column A = Dog's Name

"Joel" wrote:

Yuo can use a conditional format in column C & D with a simple Vlookup

in C1 conditional formating
=IF(VLOOKUP(D1,A1:B100,2)="Bitch",TRUE,FALSE)

in D1 conditional formating
=IF(VLOOKUP(D1,A1:B100,2)="Dog",TRUE,FALSE)

copy C1 and D1 down the columns using Copy, then pastespecial with Formt
Option. Change the bottom row of formula A1:B100 to match the last row of
your data.


"EngelseBoer" wrote:


hi, please help

i have a database something like the below, (but 36,000+ rows/animals)
and i need to run a script to verify that Dams are female (and Sires are male)
i need column 5(E) (here) to index row A and match with row D - similar to
=IF(X2="","",INDEX(Y:Y,MATCH(X2,Z:Z,0)))
but only return her name if her sex in B = Dog (or simply say ERROR)

i.e. search the Dam's name cloumn 4 (D)
find her name in column 1 (A) -
compare column 2 (B) and report back in colum 5(E) if she is not a "bitch"
example "Le Grange Tessa" is a dam - but is a dog

Name Sex Sire Dam
Bob At Quovadis Dog Roodebult Simba Le Grange Tessa
Roodebult Simba Dog Byrne Bobby Sebastiaan Tessa
Byrne Bobby Dog Mizpah Thor Slamat Lyla
Mizpah Thor Dog
Slamat Lyla Bitch
Sebastiaan Tessa Bitch Sebastiaan Monty Hansom Sophfie
Sebastiaan Monty Dog
Hansom Sophfie Bitch
Le Grange Tessa Dog Ferrou Bruno Jess Cloe


regards
EngelseBoer