Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup in MATRIX
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 -- Forumchanin ------------------------------------------------------------------------ Forumchanin's Profile: http://www.excelforum.com/member.php...o&userid=29600 View this thread: http://www.excelforum.com/showthread...hreadid=492998 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup in MATRIX
"Forumchanin"
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? 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup in MATRIX
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number Lookup in Matrix | Excel Worksheet Functions | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Lookup in Matrix | Excel Worksheet Functions | |||
Matrix Query Part II - lookup value | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |