I have a 33 column, 2 row array. The first row contains names. The second row
contains values between 0-20. I use HLookup to find a name that matches a
number and return the name. I do this down another column of 33 fields so it
lists every name. The problem is if HLookup finds a value, it will return the
same name multiple times (depending on how often the value appears)

Example:
CD88 - 6
CD89 - 6
CD90 - 5

=HLOOKUP(CD88,$AP$87:$BV$88,2,FALSE)

For instance, Person A and Person B both have values of 6, Person C has a
value of 5. When my HLookup function gets to CD88, it returns person A. The
second row Hlookup looks now for CD89 (which is also 6). Instead of returning
Person B, it returns Person A again.

How can I set up my function to skip over names it has already selected so I
get all 33 unique names.

Thanks,

J,

You are going to need a unique identifier for each person in order to use
HLOOKUP. If Person A & B both have a value of 6, it will only return the
first occurence of 6.

It might be possible to do it adding other functions to your formula, but
that will get a little convoluted.

Is there any way to add a "suffix" to your person identifer.......maybe
something like 6.1 & 6.2 or 6-1 & 6-2.

If you can not change the way things are done, it is a little beyond me to
help you out. Maybe someone more knowlegable can help you.

Sorry,

Conan




"J" wrote in message
...
I have a 33 column, 2 row array. The first row contains names. The second
row
contains values between 0-20. I use HLookup to find a name that matches a
number and return the name. I do this down another column of 33 fields so
it
lists every name. The problem is if HLookup finds a value, it will return
the
same name multiple times (depending on how often the value appears)

Example:
CD88 - 6
CD89 - 6
CD90 - 5

=HLOOKUP(CD88,$AP$87:$BV$88,2,FALSE)

For instance, Person A and Person B both have values of 6, Person C has a
value of 5. When my HLookup function gets to CD88, it returns person A.
The
second row Hlookup looks now for CD89 (which is also 6). Instead of
returning
Person B, it returns Person A again.

How can I set up my function to skip over names it has already selected so
I
get all 33 unique names.

Thanks,

Do you have *all* 33 numbers listed in a column? If so, are they in order?
Ascending? Descending?

--
Biff
Microsoft Excel MVP


"J" wrote in message
...
I have a 33 column, 2 row array. The first row contains names. The second
row
contains values between 0-20. I use HLookup to find a name that matches a
number and return the name. I do this down another column of 33 fields so
it
lists every name. The problem is if HLookup finds a value, it will return
the
same name multiple times (depending on how often the value appears)

Example:
CD88 - 6
CD89 - 6
CD90 - 5

=HLOOKUP(CD88,$AP$87:$BV$88,2,FALSE)

For instance, Person A and Person B both have values of 6, Person C has a
value of 5. When my HLookup function gets to CD88, it returns person A.
The
second row Hlookup looks now for CD89 (which is also 6). Instead of
returning
Person B, it returns Person A again.

How can I set up my function to skip over names it has already selected so
I
get all 33 unique names.

Thanks,

The 33 numbers are listed in a row. They change over time. At the beginning,
many people have the same number identifier. After about 13-14 trials, it's
pretty unlikely any of the numbers would be repeats (these numbers start at 0
and go up to about 20 million). They are in no particular order because they
can change at any point.

Thoughts?

"T. Valko" wrote:

Do you have *all* 33 numbers listed in a column? If so, are they in order?
Ascending? Descending?

--
Biff
Microsoft Excel MVP


"J" wrote in message
...
I have a 33 column, 2 row array. The first row contains names. The second
row
contains values between 0-20. I use HLookup to find a name that matches a
number and return the name. I do this down another column of 33 fields so
it
lists every name. The problem is if HLookup finds a value, it will return
the
same name multiple times (depending on how often the value appears)

Example:
CD88 - 6
CD89 - 6
CD90 - 5

=HLOOKUP(CD88,$AP$87:$BV$88,2,FALSE)

For instance, Person A and Person B both have values of 6, Person C has a
value of 5. When my HLookup function gets to CD88, it returns person A.
The
second row Hlookup looks now for CD89 (which is also 6). Instead of
returning
Person B, it returns Person A again.

How can I set up my function to skip over names it has already selected so
I
get all 33 unique names.

Thanks,

Well here's the thing. I'm also using the LARGE function to order the numbers
in another column. If adding a suffix would mess that up, then I don't think
I can add a suffix

"Conan Kelly" wrote:

J,

You are going to need a unique identifier for each person in order to use
HLOOKUP. If Person A & B both have a value of 6, it will only return the
first occurence of 6.

It might be possible to do it adding other functions to your formula, but
that will get a little convoluted.

Is there any way to add a "suffix" to your person identifer.......maybe
something like 6.1 & 6.2 or 6-1 & 6-2.

If you can not change the way things are done, it is a little beyond me to
help you out. Maybe someone more knowlegable can help you.

Sorry,

Conan




"J" wrote in message
...
I have a 33 column, 2 row array. The first row contains names. The second
row
contains values between 0-20. I use HLookup to find a name that matches a
number and return the name. I do this down another column of 33 fields so
it
lists every name. The problem is if HLookup finds a value, it will return
the
same name multiple times (depending on how often the value appears)

Example:
CD88 - 6
CD89 - 6
CD90 - 5

=HLOOKUP(CD88,$AP$87:$BV$88,2,FALSE)

For instance, Person A and Person B both have values of 6, Person C has a
value of 5. When my HLookup function gets to CD88, it returns person A.
The
second row Hlookup looks now for CD89 (which is also 6). Instead of
returning
Person B, it returns Person A again.

How can I set up my function to skip over names it has already selected so
I
get all 33 unique names.

Thanks,

I believe your description is wrong.
If the *first* row contains names, Hlookup will *not* lookup values in the
second row, and return names from the first row.

Assuming that was a typo, and values are in AP87 to BV87,
With names in AP88 to BV88, try this *array* formula:

=INDEX($AP$88:$BV$88,LARGE(IF($AP$87:$BV$87=CD88,C OLUMN($A:$AG),""),COUNTIF(CD88:$CD$120,CD88)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

*After* the CSE entry, copy down as needed.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"J" wrote in message
...
I have a 33 column, 2 row array. The first row contains names. The second
row
contains values between 0-20. I use HLookup to find a name that matches a
number and return the name. I do this down another column of 33 fields so
it
lists every name. The problem is if HLookup finds a value, it will return
the
same name multiple times (depending on how often the value appears)

Example:
CD88 - 6
CD89 - 6
CD90 - 5

=HLOOKUP(CD88,$AP$87:$BV$88,2,FALSE)

For instance, Person A and Person B both have values of 6, Person C has a
value of 5. When my HLookup function gets to CD88, it returns person A.
The
second row Hlookup looks now for CD89 (which is also 6). Instead of
returning
Person B, it returns Person A again.

How can I set up my function to skip over names it has already selected so
I
get all 33 unique names.

Thanks,

The description was correct. Instead of 1, I tell HLOOKUP to look in the
SECOND column. I'll try that formula thanks

"RagDyer" wrote:

I believe your description is wrong.
If the *first* row contains names, Hlookup will *not* lookup values in the
second row, and return names from the first row.

Assuming that was a typo, and values are in AP87 to BV87,
With names in AP88 to BV88, try this *array* formula:

=INDEX($AP$88:$BV$88,LARGE(IF($AP$87:$BV$87=CD88,C OLUMN($A:$AG),""),COUNTIF(CD88:$CD$120,CD88)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

*After* the CSE entry, copy down as needed.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"J" wrote in message
...
I have a 33 column, 2 row array. The first row contains names. The second
row
contains values between 0-20. I use HLookup to find a name that matches a
number and return the name. I do this down another column of 33 fields so
it
lists every name. The problem is if HLookup finds a value, it will return
the
same name multiple times (depending on how often the value appears)

Example:
CD88 - 6
CD89 - 6
CD90 - 5

=HLOOKUP(CD88,$AP$87:$BV$88,2,FALSE)

For instance, Person A and Person B both have values of 6, Person C has a
value of 5. When my HLookup function gets to CD88, it returns person A.
The
second row Hlookup looks now for CD89 (which is also 6). Instead of
returning
Person B, it returns Person A again.

How can I set up my function to skip over names it has already selected so
I
get all 33 unique names.

Thanks,

I'm also using the LARGE function to order the numbers in another column

Well, now I'm confused!

Let's try this:

AP87:BV87 = names
AP88:BV88 = nums

You have this formula entered in CD88 copied down to CD120 (33 rows):

=LARGE(nums,ROWS(CD$88:CD88))

Enter this array formula** in CE88 and copy down to CE120:

=INDEX(names,SMALL(IF(nums=CD88,COLUMN(names)-MIN(COLUMN(names))+1),COUNTIF(CD$88:CD88,CD88)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"J" wrote in message
...
The 33 numbers are listed in a row. They change over time. At the
beginning,
many people have the same number identifier. After about 13-14 trials,
it's
pretty unlikely any of the numbers would be repeats (these numbers start
at 0
and go up to about 20 million). They are in no particular order because
they
can change at any point.

Thoughts?

"T. Valko" wrote:

Do you have *all* 33 numbers listed in a column? If so, are they in
order?
Ascending? Descending?

--
Biff
Microsoft Excel MVP


"J" wrote in message
...
I have a 33 column, 2 row array. The first row contains names. The
second
row
contains values between 0-20. I use HLookup to find a name that matches
a
number and return the name. I do this down another column of 33 fields
so
it
lists every name. The problem is if HLookup finds a value, it will
return
the
same name multiple times (depending on how often the value appears)

Example:
CD88 - 6
CD89 - 6
CD90 - 5

=HLOOKUP(CD88,$AP$87:$BV$88,2,FALSE)

For instance, Person A and Person B both have values of 6, Person C has
a
value of 5. When my HLookup function gets to CD88, it returns person A.
The
second row Hlookup looks now for CD89 (which is also 6). Instead of
returning
Person B, it returns Person A again.

How can I set up my function to skip over names it has already selected
so
I
get all 33 unique names.

Thanks,