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.


    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
    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
                        wks1.Cells(j, Position) = Position
                        wks1.Cells(j + 1, Position) = Name
                    End If
                    looking = False
                    j = j + 1
                End If
    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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.