Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search Woes (97 SR2)

    My worksheet has the following entries:

    John Smith
    Carey Johnson
    John Smith
    John Smith
    Robert St. Paul-Johns III
    Albert Johnington
    Jimmy Jacob-Johnston
    Rashikwa Kumanjohnah

    I have a userform that my users can use to search for a given 'entity name' each of the above in the list is an entity. Now, if the user searches for 'Smith', it will sort the records, identify the first smith, and the last smith, and populate a list box with everything in between, resulting in a list of only 'smith' names. (in this case, all contacts for John Smith would be displayed)

    The problem is, when the user searches for 'John', the first and last occurance of that string may contain items from many different names.

    I would like to write code that will search a specific column (in this case column C) and create a list in a sheet, (we'll call it listbuild) which contains items that contain 'John'. I intend to use the resulting list to more clearly define the search criteria to show contacts from one specific individual, in the form of a selectable list box.

    I'm having serious issues with the logic here, however, and my coding ability is limited... has anyone done anything like this before? Is there an easy way to export a singular list of unique items to another location if given specific criteria?

    Please let me know if this is hazy or unclear, it's been rough explaining it!

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  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: Search Woes (97 SR2)

    Try the following file.
    i have names for the searchname (A1)

    Col A The names (I added some more without John) =A4-A18
    Col B :A column of number (need from 1 - end of data)
    Col C copied C4=IF(ISERROR(SEARCH(SearchName,Names)),"",B4) named RowsMatching
    Col D copied D4=INDEX(Names,SMALL(RowsMatching,B4)), sorted list of unique names
    I have a range CountMatches =COUNT(RowsMatching)
    And the one you want a dynamic range UniqueList =OFFSET(RowsMatching,0,1,CountMatches,1)

    Type in text in the A1(searchname) and UniqueList is a unique list of names containing
    Steve
    Attached Files Attached Files

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Woes (97 SR2)

    Brilliant!!!

    It's not quite as clean as I'd like it to be, but I think I can adapt a method of assigning the same formulas to a sheet from VBA to construct the same type of feed.

    Thanks again, and <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>!!!!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    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: Search Woes (97 SR2)

    Sometimes the NON-clean ways work easier than doing a lot of coding.
    I use the technique to do searches throughout a database to get a range for a list box.
    Instead looking at just the "name" field you can concatenate as many fields as you want for the "search" to find ANY "rows" that contain the text.
    You can also feed the SearchName from a textbox, so that as you add letter by letter, the list in the listbox contents decreases and then you can select from the listbox a particular record, without having to scroll through perhaps hundreds of records.
    This is easier on the user because he/she does not have to worry about what field to search in: it searches in them all: in one search you can get text matching last names, company names, states, cities, zips or whatever.

Posting Permissions

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