vlookup duplicate data?

[pleasehelpme]

If I have 2 columns as follows:

20 cat
39 dog
40 cow
39 fox
50 rat
39 mouse

Is there a way I can lookup "39" and return the first 39=dog and the next 39=fox and the next 39=mouse etc..?

[Mark W.]

Change the reference of VLOOKUP's table_array to exclude the previously found item.

[pleasehelpme]

I was hoping I could keep the table array constant. Is there another way around this problem?

[hollifd]

I am not sure if this helps you, but you may be able to use "AutoFilter" under the menu...

Data
Filter
Autofilter

This would allow you to show only the items that have "39" in the first column.

The only other idea that I have is to write some VBA code to ask the user for a value like "39" and then have the VBA code find all cells in column A that have "39" and write the results in some other cells. This should not be too dificult.

I hope this helps at least a little.

David

[Mark W.]

On 2002-04-08 09:21, pleasehelpme wrote:
I was hoping I could keep the table array constant. Is there another way around this problem?

You could setup your table array like...

{20,"cat",1
;39,"dog",2
;40,"cow",3
;39,"fox",4
;50,"rat",5
;39,"mouse",6}

...and enter the array formula...

{=VLOOKUP(39,OFFSET(table_array,F1,),{2,3},0)}

...into cells E2:F2 (leaving F1 blank) and copying down to cells E4:F4.

Note 1: Substitute an absolute cell reference "table_array"

Note 2: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

[ This Message was edited by: Mark W. on 2002-04-08 09:33 ]

[Aladin Akyurek]

On 2002-04-08 09:03, pleasehelpme wrote:
If I have 2 columns as follows:

20 cat
39 dog
40 cow
39 fox
50 rat
39 mouse

Is there a way I can lookup "39" and return the first 39=dog and the next 39=fox and the next 39=mouse etc..?

Lets say that A1:B7 houses the sample data you provided, with labels added:

{"Field1","Field2";
20,"cat";
39,"dog";
40,"cow";
39,"fox";
50,"rat";
39,"mouse"}

In D1 enter:

=MATCH(9.99999999999999E+307,Sheet1!A:A)-ROW(1:1)

This formula computes the number of actual data records in the data range dynamically.

In D2 enter: 39 [ which is your lookup value ]

In E2 enter:

=IF($D$2,VLOOKUP($D$2,OFFSET($A$2,0,0,$D$2,2),2,0),"")

In E3 enter and copy down until no more retrieval occurs, associated with the value in D2:

=IF(AND($D$2,COUNTIF(OFFSET($A$2,0,0,$D$1,1),$D$2)>COUNTA($E$2:E2)),INDEX(OFFSET($B$2,MATCH(E2,OFFSET($B$2,0,0,$D$1,1),0),0,$D$1,1),MATCH($D$2,OFFSET($A$2,MATCH(E2,OFFSET( $B$2,0,0,$D$1,1),0),0,$D$1,1),0)),"")

This is what you're going to see in the results area:

{6,"";
39,"dog";
"","fox";
"","mouse"}


Aladin

[pleasehelpme]

Thanks for that Aladin,
I am most impressed, however there are 2 points that may be of use:

Point 1/ correct me if I am wrong, but should part of your last message have read:

=IF($D$2,VLOOKUP($D$2,OFFSET($A$2,0,0,$D$1,2),2,0),"")

rather than:

=IF($D$2,VLOOKUP($D$2,OFFSET($A$2,0,0,$D$2,2),2,0),"")


Point 2/Is it possible to possible to overcome the problem of having duplicate data in "Field 2". For example, if we add on to the bottom of the existing data:

39, "rat"
39, "fox"

The results do NOT give:
{6,"";
39,"dog";
"","fox";
"","mouse";
"","rat";
"","fox"}

but instead give:
{6,"";
39,"dog";
"","fox";
"","mouse";
"","rat";
"","mouse"}

Many thanks
Nick

[Dave Hawley]

Hi


If you have a look here: http://www.ozgrid.com/VBA/TwoColLkUp.htm you will find this UDF with full instructions.

Function FindNth(Table As Range, Val1 As Variant,Val1Occrnce As Integer, _
                  Val2 As Variant,Val2Col As Integer, ResultCol As Integer)

'''''''''''''''''''''''''''''''''''''''
'Written by OzGrid Business Applications
'www.ozgrid.com

'Finds the N'th value in the first Column of a table that has a stated _
 value on the same row in another Column.
'''''''''''''''''''''''''''''''''''''''

Dim i As Integer
Dim iCount As Integer
Dim rCol As Range

	For i = 1 To Table.Rows.Count
		If Table.Cells(i, 1) = Val1 And _
			Table.Cells(i, Val2Col) = Val2 Then
			iCount = iCount + 1
		End If

		If iCount = Val1Occrnce Then
			FindNth = Table.Cells(i, ResultCol)
			Exit For
		End If
	Next i
End Function

[Derek]

Hi There

There is a very simple solution to this type of problem.

There are many elaborations you can make but the basic idea is this:
You insert a column with a formula which sequences the item you want to list.
You then do a vlookup on this column. Try this on your example

Put your numbers in Column B starting with B2 (keep A1 & A2 blank)
Put your names (cat, dog etc) in Column C starting with C2
In A2 paste this formula and scroll down =IF(B2=$E$1,1+COUNT($A$1:A1),"")
In E2 paste this formula and scroll down =VLOOKUP(ROW(E1),A:C,3,FALSE)
Now type 39 in cell E1 and the names associated with 39 will list below

If you want you can modify the formula so that #N/A does not show. Just change the formula in E2 to
=IF(COUNT(A:A)>=ROW(E1),VLOOKUP(ROW(E1),A:C,3,FALSE),"")

It is also possible, with modification, to obtain the answers from data in another sheet

Regards

Derek


[ This Message was edited by: Derek on 2002-04-09 07:39 ]

[Aladin Akyurek]

Thanks for that Aladin,
I am most impressed,

Thanks. It's mutual. Happy to see that you understood the system.

however there are 2 points that may be of use:

Point 1/ correct me if I am wrong, but should part of your last message have read:

=IF($D$2,VLOOKUP($D$2,OFFSET($A$2,0,0,$D$1,2),2,0),"")

rather than:

=IF($D$2,VLOOKUP($D$2,OFFSET($A$2,0,0,$D$2,2),2,0),"")

Yep. Just visually confounded D1 & D2. I should have put that MATCH formula in C2 to avoid that visual trap..


Point 2/Is it possible to possible to overcome the problem of having duplicate data in "Field 2". For example, if we add on to the bottom of the existing data:

39, "rat"
39, "fox"

The results do NOT give:
{6,"";
39,"dog";
"","fox";
"","mouse";
"","rat";
"","fox"}

but instead give:
{6,"";
39,"dog";
"","fox";
"","mouse";
"","rat";
"","mouse"}

That's right. The formulas assume the uniqueness of values in column B. The addition boils down to having duplicate records. You could eliminate such duplicate records using Advanced Filter.

Aladin