Hi,

I’m looking for a way to search information in a column and then return
the text found in another column that has related information. The
problem is that the names in the column appear more than one time and
the names appear randomly.
exmaple:

John lmj,45
Maria HI45,kil
Jose
John U15,U78
John M60,lem
Angel lktr
Jenny
Angel nono,468
Maria


John return: lmj,45,U15,U78,M60,lem
Angel return: lktr,nono,468


--
John21
------------------------------------------------------------------------
John21's Profile: http://www.excelforum.com/member.php...o&userid=36983
View this thread: http://www.excelforum.com/showthread...hreadid=569097

Hi!

Try this:

You can't get all the related info into a single cell. You'll have to return
it to individual cells.

Assume your table is in the range A1:B9

A15 = John

Enter this formula as an array in B15 using the key combination of
CTRL,SHIFT,ENTER:

=IF(COLUMNS($A:A)<=COUNTIF($A$1:$A$9,$A15),INDEX($ B$1:$B$9,SMALL(IF($A$1:$A$9=$A15,ROW(A$1:A$9)-ROW(A$1)+1),COLUMNS($A:A))),"")

Copy across until you bet blanks meaning all related info has been returned.

How to enter an array formula:

http://cpearson.com/excel/array.htm

Biff

"John21" wrote in
message ...

Hi,

I'm looking for a way to search information in a column and then return
the text found in another column that has related information. The
problem is that the names in the column appear more than one time and
the names appear randomly.
exmaple:

John lmj,45
Maria HI45,kil
Jose
John U15,U78
John M60,lem
Angel lktr
Jenny
Angel nono,468
Maria


John return: lmj,45,U15,U78,M60,lem
Angel return: lktr,nono,468


--
John21
------------------------------------------------------------------------
John21's Profile:
http://www.excelforum.com/member.php...o&userid=36983
View this thread: http://www.excelforum.com/showthread...hreadid=569097

This macro should help. I assumed your name data is in Column A starting in
A1 and the other information is in Column B, and the name you're searching
for is in C1. HTH Otto
Sub GetInfo()
Dim RngColA As Range
Dim i As Range
Dim Info As String
Set RngColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
Info = ""
For Each i In RngColA
If i.Value = Range("C1").Value Then
If Info = "" Then
Info = i.Offset(, 1).Value
Else
Info = Info & "," & i.Offset(, 1).Value
End If
End If
Next i
MsgBox Info
Info = ""
End Sub
"John21" wrote in
message ...

Hi,

I'm looking for a way to search information in a column and then return
the text found in another column that has related information. The
problem is that the names in the column appear more than one time and
the names appear randomly.
exmaple:

John lmj,45
Maria HI45,kil
Jose
John U15,U78
John M60,lem
Angel lktr
Jenny
Angel nono,468
Maria


John return: lmj,45,U15,U78,M60,lem
Angel return: lktr,nono,468


--
John21
------------------------------------------------------------------------
John21's Profile:
http://www.excelforum.com/member.php...o&userid=36983
View this thread: http://www.excelforum.com/showthread...hreadid=569097

The macro you send me works fine but I want the information to be post
in a cell because later I would make a hyperlink to send the
information related to the names to another sheet.


--
John21
------------------------------------------------------------------------
John21's Profile: http://www.excelforum.com/member.php...o&userid=36983
View this thread: http://www.excelforum.com/showthread...hreadid=569097

Sub GetInfo()
Dim RngColA As Range
Dim i As Range
Dim Info As String
Set RngColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
Info = ""
For Each i In RngColA
If i.Value = Range("C1").Value Then
If Info = "" Then
Info = i.Offset(, 1).Value
Else
Info = Info & "," & i.Offset(, 1).Value
End If
End If
Next i
Range("D1")=Info 'I changed this line only.
Info = ""
End Sub

"Otto Moehrbach" wrote in message
...
This macro should help. I assumed your name data is in Column A starting
in A1 and the other information is in Column B, and the name you're
searching for is in C1. HTH Otto
Sub GetInfo()
Dim RngColA As Range
Dim i As Range
Dim Info As String
Set RngColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
Info = ""
For Each i In RngColA
If i.Value = Range("C1").Value Then
If Info = "" Then
Info = i.Offset(, 1).Value
Else
Info = Info & "," & i.Offset(, 1).Value
End If
End If
Next i
MsgBox Info
Info = ""
End Sub
"John21" wrote in
message ...

Hi,

I'm looking for a way to search information in a column and then return
the text found in another column that has related information. The
problem is that the names in the column appear more than one time and
the names appear randomly.
exmaple:

John lmj,45
Maria HI45,kil
Jose
John U15,U78
John M60,lem
Angel lktr
Jenny
Angel nono,468
Maria


John return: lmj,45,U15,U78,M60,lem
Angel return: lktr,nono,468


--
John21
------------------------------------------------------------------------
John21's Profile:
http://www.excelforum.com/member.php...o&userid=36983
View this thread:
http://www.excelforum.com/showthread...hreadid=569097

Many thanks for the macro it works perfectly but I need one last thing.
Can you please put some coments on the macro to understand how it works
because I'm new to VB.

MANY, MANY, MANY, MANY thanks


--
John21
------------------------------------------------------------------------
John21's Profile: http://www.excelforum.com/member.php...o&userid=36983
View this thread: http://www.excelforum.com/showthread...hreadid=569097

Sub GetInfo()

'The 3 Dim statements declare the variables that this macro
uses.

Dim RngColA As Range

Dim i As Range

Dim Info As String

'The following statement set the range of Column A from A1 to
the last occupied cell in Column A, to the variable RngColA

Set RngColA = Range("A1", Range("A" & Rows.Count).End(xlUp))

Info = ""

'The name that you want to search for is in C1.

'The following is a "For" loop. It loops though all the cells
in RngColA and compares the value in those cells with the value in C1. When
it finds a cell that has the C1 value, it concatenates the value in Column B
to the end of "Info", with commas between each such concatenation. The
"Info="" at the end of the macro is superfluous. Delete it.

For Each i In RngColA

If i.Value = Range("C1").Value Then

If Info = "" Then

Info = i.Offset(, 1).Value

Else

Info = Info & "," & i.Offset(, 1).Value

End If

End If

Next i

Range("D1")=Info 'I changed this line only.

Info = ""

End Sub

"John21" wrote in
message ...

Many thanks for the macro it works perfectly but I need one last thing.
Can you please put some coments on the macro to understand how it works
because I'm new to VB.

MANY, MANY, MANY, MANY thanks


--
John21
------------------------------------------------------------------------
John21's Profile:
http://www.excelforum.com/member.php...o&userid=36983
View this thread: http://www.excelforum.com/showthread...hreadid=569097