lookup in MATRIX

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Forumchanin - 13 Dec 2005 11:49 GMT

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

Reply to this Message

Bruno Campanini - 13 Dec 2005 12:18 GMT

> Hi everybody.
> Recently have come to the following task:
[quoted text clipped - 14 lines]
>
> The question is - how to lookup the value in the whole matrix?

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

Reply to this Message

topola - 13 Dec 2005 13:29 GMT

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
--
topola, http://vba.blog.onet.pl

Reply to this Message