Vlookup 2

[mosmas]

I have a solution to this one but I want to know if there is a better solution:

I have a spreadsheet with this format:

Codes Amount
1004 10
1005 20
1005 30
1006 10
1006 15
1008 15
1009 20

As you can see the are duplicates. In onther sheet, I have a series of codes like 1004,1005,1006 and so on in one colunm. I want to pull up both amounts is there duplicates. Here is a sample of what I want to look like:

1004 10
1005 20 30
1006 10 15


My solution is like this:
=OFFSET(INDIRECT(ADDRESS(MATCH(A6,Sheet1!$A$1:$A$9,0),2,,1,"Sheet1&quot),1,0)

Any ideas will be appreciated. Thanks a lot....

Moses.

 

[Zathras]

If you grok VBA and User Defined Functions, you can use the following UDF:
[blue]

Function VLOOKUPNEXT(lookup_value, table_array As Range, _
           col_index_num As Integer, last_value)

[green]

' Extension to VLOOKUP function.  Allows for finding
' the "next" item that matches the lookup value.

[/color]

Dim nRow As Long
Dim bFound As Boolean
  VLOOKUPNEXT = ""
  With table_array
    For nRow = 1 To .Rows.Count
      If .Cells(nRow, 1).Value = lookup_value Then
        If bFound = True Then
          VLOOKUPNEXT = .Cells(nRow, col_index_num).Value
          Exit Function
        Else
          If .Cells(nRow, col_index_num).Value = last_value Then
            bFound = True
          End If
        End If
      End If
    Next nRow
  End With
End Function

[/color]

Then set up Sheet2 this way:
[blue]

A1: 1004
A2: 1005
A3: 1006
B1: =VLOOKUP($A1,Sheet1!$A$1:$B$7,2,0)
   (copy B1 into B2:B3)
B3: =VLOOKUP($A3,Sheet1!$A$1:$B$7,2,0)
C1: =VLOOKUPNEXT($A1,Sheet1!$A$1:$B$7,2,B1)
   (copy C1 into C1:E3)

[/color]
If you do it correctly you should see the results you want.

To create a UDF, type Alt-F11 to bring up the VBA editor and create a code module (Insert/Module from the menu). Then paste the code into the module.

 

[mosmas]

Thanks Zathras. I tried it but I couldn't get it to work. I will try it again. In any case, thanks a lot. You deserve a start, so I gave you one.

Moses