+ Reply to Thread
Results 1 to 24 of 24

Filter names with first 4 letters - expanded version

  1. #1
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Filter names with first 4 letters - expanded version

    dear friends,

    on 26.05.2015 i posted this inquiry where NAVEED RAZA assisted with VB coding and solved my requirement. attached is the exel file that NAVEED RAZA produced.

    now i would like to do the following and hope you can assist me plse... thank you.

    1. in the ACTUAL LIST sheet once a name is typed and entered the result is produced by the name and passport no. i would like to expand this to include the nationality as well. therefore once search is entered the result should consist of name, nationality and passport no.

    2. once the above is performed (search), we then select the name and press ENTER. once this is done the appropriate name is listed in the ACTUAL LIST sheet. at this moment, what i also want is that this particular name is highlighted in the NAMES sheet as well. for example, if i select and enter say 5 names in the ACTUAL LIST sheet and when i press the cursor at the first NAME column, this name with details should be highlighted in the NAMES sheet. then i press the 4th name in ACTUAL LIST sheet and in the NAMES sheet this particular name row should be highlighted.

    look forward to hear from you and thank you very much

    best regards
    rizvi

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Filter names with first 4 letters - expanded version

    Copy the below code and do right click on Actual List sheet tab and select view code and replace your current code with the below.

    Close the VBA window (Alt+Q to close VBA window) and return to that sheet and check.

    Please Login or Register  to view this content.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    879

    Thumbs up Re: Filter names with first 4 letters - expanded version

    Hi Rizvi,

    Please see the attached.. & give it a try...
    Regards
    Parth

    I appreciate your feedback. Hit * if u Like.
    Rules - http://www.excelforum.com/forum-rule...rum-rules.html

  4. #4
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Re: Filter names with first 4 letters - expanded version

    Hello PARTH007,
    thanks yours, works nicely BUT the highlighted NAME cell stays ON even when another name is selected. only one name should be highlighted in the NAMES sheet when a name is selected in the ACTUAL SHEET.

    SIXTH SENSE - i will check yours and revert - thank you

    regards
    rizvi

  5. #5
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Re: Filter names with first 4 letters - expanded version

    hello SIXTHSENSE

    i have just now worked your coding which works nicely BUT when part of name is typed in the ACTUAL SHEET and ENTERED is pressed, the full contents of that name does not fill in. what i require is that the sheets be as it is BUT when a name cell in the ACTUAL NAMES sheet is selected, that name in the NAMES sheet should be highlighted. when no selection of a name cell is made in the ACTUAL NAMES sheet then nothing should be highlighted in the NAMES sheet.

    regards
    rizvi

  6. #6
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Re: Filter names with first 4 letters - expanded version

    could we make the highlighting colour to be light GREY? this will assist to read the contents of the highlighted row.

  7. #7
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Re: Filter names with first 4 letters - expanded version

    AND, could the combo box be a little bigger as well so that the full name could appear ???? the country could go more closer to the passport number.

  8. #8
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    879

    Re: Filter names with first 4 letters - expanded version

    Hi Rizvi,

    The color coding changed to Grey... The columns are welll strechted so that data could be fit in..
    Where is Combo Box

    please see atached

  9. #9
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Re: Filter names with first 4 letters - expanded version

    Hi PARTH007,

    works perfectly, thank you. BUT as requested, could you also expand the search to show NAME, NATIONALITY & PASSPORT NO instead of NAME & PASSPORT NO.???
    look forward to hear from you

    regards
    rizvi

  10. #10
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    879

    Re: Filter names with first 4 letters - expanded version

    Here you go .....

  11. #11
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Re: Filter names with first 4 letters - expanded version

    hi PARTH007

    the USERFORM1 appears blank. awaiting new file from you

    regards
    rizvi

  12. #12
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    879

    Re: Filter names with first 4 letters - expanded version

    hi Rizvi,

    i am able to see the userform with data loaded in it...

  13. #13
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Re: Filter names with first 4 letters - expanded version

    hi mate,

    noted yours. i downloaded the excel file just now again and tried but the userform is blank. the NO DATA FOUND window appears ok. but the userform against found data appears blank.

    appreciate if you could check again comparing the last form and the new one.

    many thanks
    regards / Rizvi

  14. #14
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    879

    Re: Filter names with first 4 letters - expanded version

    Puzzled as i am not getting any error.. infact i am able to see NAME, NATIONALITY & PASSPORT once i input data in Actual list & press enter in column "Atual List Names"
    ..

    Check this out.jpg
    Last edited by Parth007; 07-30-2015 at 04:20 AM.

  15. #15
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Re: Filter names with first 4 letters - expanded version

    Hmmmmmm........ what could go wrong here? i cannot view at all.....

  16. #16
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Re: Filter names with first 4 letters - expanded version

    hi PARTH007,

    i have managed to download and run it successfully. may be something went wrong in the previous downloads. however i observe that the selected name in the USERFORM does not appear in the ACTUAL LIST. in fact some other name appears. i selected about 4 names one at a time and all different names appeared in the ACTUAL LIST.

    hope to hear from you

    regards
    rizvi

  17. #17
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    879

    Re: Filter names with first 4 letters - expanded version

    4 names at a time
    the sheet i was working.. i am only able to select one at a time & it shows correct data......

  18. #18
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Re: Filter names with first 4 letters - expanded version

    not 4 at a time but one at a time.... okay could we do this, using the original excel sheet that is attached with my initial message first i do the search and enter a name in the ACTUAL NAME sheet by double clicking the searched item then when i select a name cell in the ACTUAL NAME sheet, the process will start all over again and the name cell appearing in the NAMES sheet will be highlighted. if any duplicate names in the NAMES sheet, still all of them will be highlighted in the NAME sheet. so when ever i select a name cell in the ACTUAL NAME sheet, the relevant name cell or cells in the NAME sheet should be highlighted. there could be the same names but with different nationality and passport number. so although only the name cell is selected in the ACTUAL NAME sheet, the search should be done against name, nationality and passport no.

    hope you can assist

    regards
    rizvi

  19. #19
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Re: Filter names with first 4 letters - expanded version

    hi PARTH007

    hello mate,
    sorry for troubling you on this matter but i just want to conclude it soon. getting back to where we were, please note the following;

    1. on 07-30-2015, 01:47 PM, you pasted a screen shot. could you see in your screen shot that one name is missing when you typed MR as a search word? in my sample document there are total 8 names beginning with MR but your screen shot shows only 7 names. it seems like the search has started by omitting the first name on the list. i think, this is the reason that i complained saying the search names do not appear but instead some other names appear. you will observe that the first name is MR MICHAEL PATRICK KELLY, but this name does not appear when searched. for example, if you type KELLY and search the result gives a blank list box.

    2. under the ACTUAL LIST and NAMES sheets, i have added an extra column under AX named "VISA EXP DATE". so after making the search and double clicking on the result, all data in that row should fill in including column AX

    look forward to your assistance please

    regards
    rizvi

  20. #20
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    879

    Thumbs up Re: Filter names with first 4 letters - expanded version

    Hi Rizvi,

    See this attached.. with all records present in Listbox & added VISA column to get populated..

  21. #21
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Re: Filter names with first 4 letters - expanded version

    buddy,
    works perfectly.. many thanks. i already gave you the star sometime back.. so i shall close this thread. thank you once again
    regards
    rizvi

  22. #22
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    879

    Re: Filter names with first 4 letters - expanded version

    Lolz :D

    I am happy that its done... i know little but a grt RnD helps sometime :D

  23. #23
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Re: Filter names with first 4 letters - expanded version

    appreciate mate, thank you and good luck to you as well

  24. #24
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Re: Filter names with first 4 letters - expanded version

    Hi parth007,

    first i apologies if i am not permitted to continue the dialogue in a thread which is marked as solved but i do have a small issue.
    When we search and enter in the "actual list" sheet, the names are greyed in the "names" sheet. For example, we do 4 names search and enter in the "actual list" sheet and accordingly, those 4 names are greyed in the "names" sheet. Now when i delete these 4 names and enter new names, search and press enter, these new names are also highlighted in the "names" sheet but the previous names are also appear to be highlighted. Incase the same name is searched and entered in the "actual list" sheet, the name is greyed in the "actual list" sheet as well.

    Therefore, could it be done for the name in the "names" sheet to be greyed only if the name/s appear in the "actual list" sheet and if no names appear in the actual list sheet then there should not be any greyed names in the name sheet.

    I got about 6 thousand plus names, so difficult when each and every name continues to be greyed even though different searches are performed

    thank you

    regards
    rizvi

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Filter names with first 4 letters
    By RIZVI in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-27-2015, 06:40 AM
  2. [SOLVED] Changing names to all Capital letters
    By Canoga56 in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 12-15-2014, 01:06 AM
  3. VBA Get all the names same the 3 starting letters
    By mcmunoz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-22-2013, 10:02 PM
  4. [SOLVED] How to filter the names from a list of names without duplicates
    By jayait2000 in forum Excel General
    Replies: 6
    Last Post: 10-11-2012, 04:24 AM
  5. Changing column names to letters
    By esa in forum Excel - New Users/Basics
    Replies: 8
    Last Post: 05-02-2010, 11:48 PM
  6. Extracting letters from names
    By christopherp in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2007, 02:47 AM
  7. Replies: 0
    Last Post: 12-08-2005, 06:55 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1