lookup in MATRIX

F

Forumchanin

Hi everybody.
Recently have come to the following task:

There is a table of data like this:

Piter 10 20 30
Ann 15 23 45 54 56
John 1 2 3 4 5 6

Which is treated as the values which can belong to only one person.

The task is, given the table in this awful form, get the name o
person, which possess a given number.

Suppose each person can have up to 255 values and there are 6500
people, thus, you can't rearrange table:).

The question is - how to lookup the value in the whole matrix?
Applying vlookup 255 times will not do

thanx in advanc
 
B

Bruno Campanini

"Forumchanin" <[email protected]>
wrote in message
Hi everybody.
Recently have come to the following task:

There is a table of data like this:

Piter 10 20 30
Ann 15 23 45 54 56
John 1 2 3 4 5 6

Which is treated as the values which can belong to only one person.

The task is, given the table in this awful form, get the name of
person, which possess a given number.

Suppose each person can have up to 255 values and there are 65000
people, thus, you can't rearrange table:).

The question is - how to lookup the value in the whole matrix?
Click to expand...

With a few lines of VBA code:
==============================
Dim Ra1 As Range, CellFound As Range, FirstAddress As String
Dim j As Long

Set Ra1 = [Sheet10!A1:IV65000]
With Ra1
Set CellFound = .Find(What:= YourNumber, _
After:= Ra1(Ra1.Count), _
MatchCase:= False, _
SearchOrder:= xlByColumns, _
SearchDirection:= xlNext, _
LookAt:= xlPart, _
LokkIn:= xlValues)
If Not CellFound Is Nothing Then
FirstAddress = CellFound.Address
Do
j = j +1
CellFound.Select
Msgbox "Found: " & j
Set CellFound = .FindNext(CellFound)
Loop While CellFound.Address <> FirstAddress
End If
End With
================================

Ciao
Bruno
 
T

topola

I would only add InputBox and Value for the name of the person. The
whole procedure would be as follow:


Sub Seek_Value()

Dim Ra1 As Range, CellFound As Range, FirstAddress As String
Dim j As Long

YourNumber = InputBox("Your number")

Set Ra1 = [Sheet1!A1:IV65000]
With Ra1
Set CellFound = .Find(What:=YourNumber, _
After:=Ra1(Ra1.Count), _
MatchCase:=False, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
LookAt:=xlPart, _
LookIn:=xlValues)
If Not CellFound Is Nothing Then
FirstAddress = CellFound.Address
Do
j = j + 1
CellFound.Select
MsgBox "Found: " & Cells(CellFound.Row,
1).Value
Set CellFound = .FindNext(CellFound)
Loop While CellFound.Address <> FirstAddress
End If
End With

End Sub
 
Top