Returning a list of names from a matrix table

[chris_horn]

Hi all,

My excel ispretty rusty and I need your help...
I have a table as below; I need a formula or vba code that will return thenames into an array or rows on a sheet when given the country as an input and an X appears for them. I have been playing around with INDEX, MATCH and LOOKUP but can't quite seem to get my head around it.

I will then use the names to lookup the corresponding email address and send an email. I think I can still do the last bit.

	Australia	Hong Kong	Singapore	Taiwan
Name1	X
Name2		X	X
Name3				X
Name4				X

<tbody>
</tbody>

Thanks in advance,
Christian.

 

[hiker95]

chris_horn,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


So that we can get it right the first time, can we have another screenshot (manually formatted by you) for the results you are looking for.

 

[chris_horn]

Hello hiker95,

Thanks!

Ok, I'm running Excel 2010 on Wondows 7 64bit.

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1		Australia	Hong Kong	Singapore	Taiwan
2	Name1	X					Enter Country:	Taiwan	Result:	Name3
3	Name2		X	X						Name4
4	Name3				X
5	Name4				X

<tbody>
</tbody>

I hope this is a little clearer, soin this example I would enter Taiwan in H2 and the result would be placed incolumn J. So if I entered Australia the Result would be just Name1 in J2.
I can then use the result to set an email to range.


Cheers,

 

[ztodd]

Here's some vba-

Function getNames(cntry) As String()
    Set c = Rows(1).Find(cntry)
    If c Is Nothing Then Exit Function
    Set c = c.EntireColumn.Find("X")
    If c Is Nothing Then Exit Function
    firstAddress = c.Address
    Dim NamesArray() As String
    n = 1
    Do
        ReDim Preserve NamesArray(n) As String
        NamesArray(n) = c.EntireRow.Cells(1, 1)
        n = n + 1
        Set c = c.EntireColumn.FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstAddress
    getNames = NamesArray
End Function

I probly wouldn't bother putting the names into an array though- just stick your code right in there (or call another sub or function) to lookup the email and send it right then, instead of sticking it into an array.

 

[azumi]

AS given table in ranges A1:E5 include table header, and your criteria in cell H2, put formula in J2

=IFERROR(INDEX($A$2:$A$5,SMALL(IF(INDEX($B$2:$E$5,0,MATCH($H$2,$B$1:$E$1,0))="X",ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($A$1:A1))),"-")

this array formula, when ENTER you need topress CTRL-SHIFT-ENTER button together, and then copied down until blank results

Cheers

 

[chris_horn]

You guys are good!!

I quickly tried the formual and it works great. ztodd I'll try yours tomorrow when I get my code together.

Big thank you!

 

[hiker95]

chris_horn,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


You could use the Worksheet_Change Event in your worksheet that would be activated each time you change cell H2.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 10/01/2014, ME808892
If Intersect(Target, Range("H2")) Is Nothing Then Exit Sub
Dim lr As Long, co As Range, c As Range, nr As Long
If Target = "" Then
  lr = Me.Cells(Rows.Count, "J").End(xlUp).Row
  If lr > 1 Then Me.Range("J2:J" & lr).ClearContents
  Exit Sub
End If
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  lr = .Cells(Rows.Count, "J").End(xlUp).Row
  If lr > 1 Then Me.Range("J2:J" & lr).ClearContents
  Set co = .Rows(1).Find(.Range("H2").Value, LookAt:=xlWhole)
  If co Is Nothing Then
    .ScreenUpdating = True
    MsgBox ("The country '" & Me.Range("H2") & "' was not found in row 1 - macro terminated!")
    .Range("H2").ClearContents
    GoTo NotFoundExit
  ElseIf Not co Is Nothing Then
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    nr = 1
    For Each c In .Range(Me.Cells(2, co.Column), .Cells(lr, co.Column))
      If c = "X" Then
        nr = nr + 1
        Me.Range("J" & nr) = Me.Cells(c.Row, 1)
      End If
    Next c
  End If
NotFoundExit:
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

Then make changes to cell H2.