Big Excel Problem.....could REALLY use some help

[njuneardave]

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.


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????



i think 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 search based on those params.....knowing which cells they
are in



************************************************************************************************
Basically, not only does the VLOOKUP() have to find the value in the
chart....but it has to ENSURE that the value goes with the CORRECT
column name (e.g.: WORK -> Dog instead of WORK -> Cat)
************************************************************************************************


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

 

[RWS]

Not sure i fully understand the full story, but in answer to the basic
summary at the bottom, why not add a column with formula
=A1&E1 and copy it down (assumes A has Name and E has Type). This gives you
unique reference i.e CatWork orDogToy to do your look up with.

 

[njuneardave]

thanks for the reply,

that is a good idea, but i figured it out using INDEX and MATCH. thank
you for your help