Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find the remainder of a list (Excel 2000)

    All,
    I have three cells with text entries and a list containing these entries and others. i would like to be able to put the remaining entries in the list in a fourth cell. Can anyone help ? I have attached a file which probably shows this better than I can explain it..
    Cheers,

    Paul
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Find the remainder of a list (Excel 2000)

    Try the following function.
    =restoflist(F12:F17)
    It uses the range given as the Full-list and gives the items not in the cell above, the cell to the left and the cell to the above left.

    Steve

    <pre>Function RestOfList(rng As Range)
    Dim cell As Range
    Dim sAboveLeft As String
    Dim sAbove As String
    Dim sLeft As String

    sAboveLeft = Range(Application.Caller.Address).Offset(-1, -1).Value
    sAbove = Range(Application.Caller.Address).Offset(-1, 0).Value
    sLeft = Range(Application.Caller.Address).Offset(0, -1).Value
    RestOfList = ""
    For Each cell In rng
    If cell.Value <> sAboveLeft And cell.Value <> sAbove And cell.Value <> sLeft Then
    RestOfList = RestOfList & cell.Value & ", "
    End If
    Next
    RestOfList = Left(RestOfList, Len(RestOfList) - 2)
    End Function
    </pre>


  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find the remainder of a list (Excel 2000)

    Many thanks!! Works a treat.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •