Sleeper: VLOOKUP?

[austenr]

I have an EXCEL spreadsheet that contains the following:

Rep Name

Rep Phone number

Customers (Multiples)

Is there a way that a user can enter the GROUP name in an input box then it will list all the accounts for that rep name? Could VLOOKUP be used?

I need this fairly soon so any help would be greatly appreciated.

I have attached the zip file example below.

Thanks in advance for your help.

[NateO]

Hello,

I'm having a hard time following your sheet in conjuction with your post.

Try selecting your column, click Data->Filter..->Autofilter. Pick your name of choice in the drop down.

[Zack Barresse]

I'd go w/ Nate's suggestion. I'd also add a set of Header rows to your spreadsheet. This will work better when using autofilter. You'd also need to fill in all the names in the blank cells. You could do this in a macro easy enough. That may look something like this ...

Option Explicit

Sub fillInNames()
    Dim lastrow As Long, row2 As Long, val2 As Range, cel As Range, rng As Range, tmp As String
    lastrow = Range("A65536").End(xlUp).Row
    row2 = Range("F65536").End(xlUp).Row
    Set rng = Range("A1:A" & lastrow - 1)
    For Each cel In rng
        If cel.Row > lastrow - 1 Then Exit For
        If cel.Value <> "" Then
            tmp = cel.Value
            Range(cel.Address, cel.End(xlDown).Offset(-1)).Value = tmp
        End If
    Next cel
    Set val2 = Range("A65536").End(xlUp)
    Range(val2, Range("A" & row2)).Value = val2.Value
    MsgBox "Done!"
End Sub

It's not the most efficient, but it's a quickie to get you started.

[austenr]

I would like some sort of macro. Maybe where the individual would put in the name of the person and then list all of the accounts. Any suggestions. The macro above does not do anything. Sorry.

[Zack Barresse]

Well, then maybe something like this ...

Option Explicit

Sub SortForAusten()
    Dim ans As String
    ans = InputBox("Please enter the name to filter for:", "Enter Name")
    With Cells
        .AutoFilter
        'Field is column, criteria is your name
        .AutoFilter Field:=1, Criteria1:=ans
    End With
End Sub

Is that more what you were looking for? (example attached)