i REALLY need a VLOOKUP EXPERT

[Guest]

Okay, if anyone can solve this, I will be beyond impressed. Who is the excel
guru who can figure this one out??

here's my problem: i have a name in Sheet1....call it Dog. Sheet1 has
multiple rows of Dog. depending on the outcome of another program, the
number of Dog instances changes....sometimes 3....up to 10. so its a
variable. Each Dog type has a subtype: herding, hound, non sporting,
sporting, terrier, toy, working. If there are multiple instances of the same
type (say, 3 toy), the listing in the sheet goes: Toy, Toy1, Toy2...there
are NO duplicate types. Also, I have other attributes in Sheet 1 depending
on the type: avg size, avg weight, and avgLifeSpan.

I also have a name called Cat. Sheet1 has multiple rows of Cat. depending
on the outcome of another program, the number of Cat instances
changes....sometimes 3....up to 10. so its a variable. Each Cat type has a
subtype: Established, Natural, Mutation, or Hybrid. If there are multiple
instances of the same type (say, 3 Natural), the listing in the sheet goes:
Natural, Natural1, Natural2...there are NO duplicate types. Also, I have
other attributes in Sheet 1 depending on the type: avg size, avg weight, and
avgLifeSpan.

This is what it looks like:

Name size weight life type
--------------------------------------
cat 12 23 13 estab
cat 15 28 11 nat
cat 8 14 8 mut
cat 18 31 10 hybrid
dog 15 40 9 herding
dog 10 21 12 hound
dog 21 55 9 nonsp
dog 25 63 13 sport
dog 12 15 15 terrier
dog 10 9 14 toy
dog 13 12 12 toy1
dog 14 15 11 toy2



Sheet1 is a running update sheet.....Sheet2 is an old version sheet. Any
changes made by the program affect Sheet1. So, Sheet1 will occasionally
change, but Sheet2 (old) will not change. I want Sheet3 to track the changes
on Sheet1. When a new name is added, I will also populate Sheet3 with all of
the other columns for that new name. When a new type (like, alien) is added,
I will also populate Sheet3 with all of the other columns for that new type.
If any other column changes, for example: weight, or size, or weight and
size, or life....whatever changes, I want those changes to show up on Sheet3
in the respective spot.

Most of the time (80%), the number of dogs and cats will not change. I want
to go through every Name and make sure that no new name has been added.
Okay, easy enough, I will use VLOOKUP(). If a new name is added, i can
easily track it and update all of the information.

Here's the hard part: I want to make sure that both sheets STILL have the
same NUMBER of instances. Like, what if a new dog is created.....I will now
have 9 dogs instead of 8.....how do I track to make sure that no new dogs
were added?

Also, I want to ensure that the type did not change....suppose I had 8 dogs
on both sheets, but instead, there was a Working instead of Toy2, how would I
be able to track that? I would first have to check to ensure that Name was
still there and was the same before I check for type.

Also, if neither the name nor the type change, I want to monitor the other
columns still to ensure that they do not change. If they change (without the
name or type changing), I want to record those.


Okay, phew, I hope that is all the info you need. Now, I need to know how
to do it... there are going to be 3 different equations (one for the name
check, type check, and other column checks) that will solve this i am almost
cetain.


thanks ahead of time for helping me out

 

[Guest]

(sorry if this double posts)

So, here is an example of how a new vs an old version looks:

OLD:
Name size weight life type
--------------------------------------

cat 12 23 13 estab
cat 15 28 11 nat
cat 8 14 8 mut
cat 18 31 10 hybrid
dog 15 40 9 herd
dog 10 21 12 hound
dog 21 55 9 nonsp
dog 25 63 13 sport
dog 12 15 15 terrier
dog 10 9 14 toy
dog 13 12 12 toy1
dog 14 15 11 toy2

NEW:
Name size weight life type
--------------------------------------

cat 12 23 13 estab
cat 15 28 11 nat
cat 8 14 8 mut
cat 18 31 10 hybrid
cat 90 90 100 WORK
dog 15 40 9 herd
dog 10 21 12 hound
dog 21 55 9 nonsp
dog 25 63 13 sport
dog 12 15 15 terrier
dog 10 9 14 toy
dog 13 12 12 WORK
dog 14 15 11 toy1

Now, NOTICE: toy2 is gone....WORK has been added. BUT ALSO NOTICE: a new
CAT type has been added called WORK also. How do I associate the WORK type
with the DOG name instead of the CAT name????

 

[Guest]

I guess my question to the last post is this: i will have to dynamically
change the range of my VLOOKUP. can I set the range of VLOOKUP using the
cell value name...like instead of:

VLOOKUP(ATable!B2,BTable!$B$2:$B$9000,1,FALSE)

how can I change that to be something like:

VLOOKUP(ATable!B2,BTable!$B(FIRST INSTANCE OF DOG):$B(SECOND INSTANCE OF
DOG),1,FALSE)

where it can serach based on those params.....knowing which cells they are in