# How to display only data that meets certain criteria in Excel

I have a list of names, with one column further on with numerical data within, for argument's sake like shown below:

``````Dave    1
Bob     2
Frank   1
Dennis  3
``````

I would like to have another table on a different sheet that selects and displays the names depending on the number next to the name.

So for example the column `1` would have the names Dave and Frank listed below

Is this possible?

• Welcome to Super User! Just to clarify the request, you want a separate table with column headers `1`, `2`, and `3`, and you want to a way to fill out that table with the names from the given table? – Excellll Oct 7 '15 at 14:35
• Hey there. My situation is, at my company we have a list of names of people on training, and how many weeks into it. I'd like to be able to have a different sheet with a table with headers of the weeks, so 1 2 3 and 4, and below those headers which people are on week 1,2 etc – David H Oct 7 '15 at 14:37

You can do this with an array formula. Do the following:

1. Set up your table on a new sheet. Create column headers `1`, `2`, `3`, etc.
2. Under header `1` (in `A2`), type the following.

=IFERROR(INDEX(Sheet1!\$A\$1:\$A\$5,SMALL(IF(Sheet1!\$B\$1:\$B\$5=Sheet2!A\$1,ROW(Sheet1!\$B\$1:\$B\$5)),ROWS(B\$2:B2))),"")

where `Sheet1` is the sheet with the original data, `A1:A5` is the list of names in the original data (including column header), and `B1:B5` is the list of numbers in the original data (including column header).

3. After typing the formula, place the cursor in the formula bar and press Ctrl+Shift+Enter. This will enter the formula as an array formula. The formula will show up in the formula bar surrounded by curly braces.

4. Now fill this formula over to all the columns in your new table. Then fill the formula down as far as you need to get all the names to appear.

Explanation of formula:

The `SMALL(IF(Sheet1!\$B\$1:\$B\$5=Sheet2!A\$1,ROW(Sheet1!\$B\$1:\$B\$5)),ROWS(B\$2:B2))` part of the formula looks through the original data for records that match the number specified by the column header. If a match is found, the row number of each match is returned as this is filled down.

The `INDEX(Sheet1!\$A\$1:\$A\$5, ...)` part of the formula returns the name from the row number found.

The `IFERROR(..., "")` part checks what is returned by `INDEX` for an error. If an error is found, the formula returns nothing.

Here is a solution using VBA:

``````Public Sub columns()
Dim wks, wks1 As Worksheet
Set wks = ThisWorkbook.Sheets(1)
Set wks1 = ThisWorkbook.Sheets(2)
firstrowsource = 1
wks1.Application.ScreenUpdating = False
wks1.Cells.Clear
Last = wks.Cells(Rows.Count, "A").End(xlUp).Row
For i = firstrowsource To Last
Name = wks.Cells(i, 1)
Position = wks.Cells(i, 2)
j = 1
looking = True
While looking
If wks1.Cells(j, Position) = "" Then
If j <> 1 Then
wks1.Cells(j, Position) = Name
Else
wks1.Cells(j, Position) = Position
wks1.Cells(j + 1, Position) = Name
End If
looking = False
Else
j = j + 1
End If
Wend
Next i
wks1.Application.ScreenUpdating = True
Final = MsgBox("Finished", vbInformation)
End Sub
``````

You have to fo to Macros / Visual Basic, under ThisWorkbbok insert a new module, and paste the code on the right side.

It assumes the source worksheet is Sheet1 and the destination is Sheet2. If your source worksheet have titles then modify the value of variable `firstrowsource`.