Need help

J

John21

Hi,

I’m looking for a way to search information in a column and then retur
the text found in another column that has related information. Th
problem is that the names in the column appear more than one time an
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,46
 
B

Biff

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
 
O

Otto Moehrbach

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
 
J

John21

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

Otto Moehrbach

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
 
J

John21

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
 
O

Otto Moehrbach

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
 
Top