Returning a list of names from a matrix table

chris_horn

New Member
Joined
Sep 30, 2014
Messages
3
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.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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

New Member
Joined
Sep 30, 2014
Messages
3
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

Board Regular
Joined
Sep 17, 2014
Messages
221
Here's some vba-
Code:
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

Well-known Member
Joined
Jun 4, 2013
Messages
555

ADVERTISEMENT

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

New Member
Joined
Sep 30, 2014
Messages
3
You guys are good!! :cool:

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

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,752
Members
414,171
Latest member
12Rev79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top