i REALLY need a VLOOKUP EXPERT

njuneardave · 06-21-2006, 01:10 PM

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

njuneardave · 06-21-2006, 01:30 PM

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

"njuneardave" wrote:

> 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

njuneardave · 06-21-2006, 01:30 PM

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

"njuneardave" wrote:

> (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????
>
>
>
> "njuneardave" wrote:
>
> > 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