Need help

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
> message news:[email protected]...
>>
>> 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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>