Vlookup
Solved/Closed
DG83
Posts
38
Registration date
Monday January 2, 2012
Status
Member
Last seen
April 21, 2018
-
Sep 20, 2012 at 04:41 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Sep 21, 2012 at 11:48 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Sep 21, 2012 at 11:48 AM
2 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Sep 20, 2012 at 04:50 AM
Sep 20, 2012 at 04:50 AM
with vlookup that would not be possible. You would need to create a custom formula to to that
DG83
Posts
38
Registration date
Monday January 2, 2012
Status
Member
Last seen
April 21, 2018
Sep 20, 2012 at 06:03 AM
Sep 20, 2012 at 06:03 AM
Any idea what would do the job? I realise that I would need another cell to insert another formula that would exclude the already displayed result from the vlookup. So let's say I have 3 peters in the list and the first one is found, what could be the solution to have the remaining two displayed under the first one.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Sep 21, 2012 at 11:48 AM
Sep 21, 2012 at 11:48 AM
You can use search to do that
here is some thing that can do that
Sample call
=FindAll("a",Sheet3!A:B,2)
here is some thing that can do that
Sample call
=FindAll("a",Sheet3!A:B,2)
Public Function FindAll(searchFor As String, rngSearchAndFindRange As Range, offsetCol As Integer) As String
Dim Cell As Range
Dim firstFoundCell As Range
Dim returnValue As String
Dim searchRange As Range
If (rngSearchAndFindRange Is Nothing) Then
FindAll = vbNullString
ElseIf (rngSearchAndFindRange.Columns.Count < offsetCol) Then
FindAll = vbNullString
Else
With rngSearchAndFindRange
Set searchRange = Range(.Cells(1, 1), .Cells(.Rows.Count, 1))
End With
With searchRange
Set Cell = .Find(What:=searchFor, _
After:=.Cells(.Rows.Count, .Columns.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False _
)
If Not Cell Is Nothing Then
Set firstFoundCell = Cell
returnValue = Cell.Offset(0, offsetCol - 1).Value
Do While True
Set Cell = .Find(What:=searchFor, _
After:=.Range(Cell.Address), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False _
)
If Not Cell Is Nothing Then
If firstFoundCell.Address = Cell.Address Then Exit Do
returnValue = returnValue & ", " & Cell.Offset(0, offsetCol - 1).Value
Else
Exit Do
End If
Loop
FindAll = returnValue
Else
FindAll = vbNullString
End If
End With
End If
Set Cell = Nothing
Set firstFoundCell = Nothing
Set searchRange = Nothing
End Function
