lookup in MATRIX

[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 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?
Applying vlookup 255 times will not do

thanx in advance


One solution: Concatenate all values for one person like this in one cell

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

then make =VLOOKUP("*"&<value>&" ,"&"*";<array>;<column>;FALSE)

With this solution the question is - how to concatenate 250 values by a simple formula, applying some separator between these values

[Bruno Campanini]

"Forumchanin" <[email protected]>
wrote in message
news:[email protected]...

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

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

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