+ Reply to Thread
Results 1 to 12 of 12

7 column array unique list of alphabetized names ignoring blanks with highlighting

  1. #1
    Registered User
    Join Date
    04-28-2019
    Location
    Jenks, Oklahoma
    MS-Off Ver
    2010
    Posts
    5

    7 column array unique list of alphabetized names ignoring blanks with highlighting

    I would like some help with using a 7 column array A2 through G25. Show a unique list of alphabetized names, ignoring blanks, in column J starting on row 1 and when you click on one of the names in Column J all matches are highlighted in the array. I'm just guessing that something like this would have to be a vba script???
    Thanks in Advanced

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: 7 column array unique list of alphabetized names ignoring blanks with highlighting

    Hi dblDennis and welcome to the forum,

    I put your request on a double click of any name in Column J. Here is the code and file.
    Please Login or Register  to view this content.
    VBA to Color Name in Col J Double Click.xlsm
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: 7 column array unique list of alphabetized names ignoring blanks with highlighting

    As I see it, you will need two VBA procedures... a macro to create the list of unique alphabetized names and an event code procedure to color the names as they are selected in Column J. Here is the macro (it goes in a standard Module)...
    Please Login or Register  to view this content.
    and here is the event code procedure (it goes in the worksheet's code module... right click the worksheet's tab and select "View Code" from the popup list that appears)...
    Please Login or Register  to view this content.
    Last edited by Rick Rothstein; 04-05-2020 at 02:32 AM.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: 7 column array unique list of alphabetized names ignoring blanks with highlighting

    dlbDennis

    Assuming the sheet name that you want this to happen would be "Sheet1"

    1) To Sheet1 code module
    Please Login or Register  to view this content.
    2) To ThisWorkbook code module
    Please Login or Register  to view this content.
    3) Save the workbook and reopen.

  5. #5
    Registered User
    Join Date
    04-28-2019
    Location
    Jenks, Oklahoma
    MS-Off Ver
    2010
    Posts
    5

    Re: 7 column array unique list of alphabetized names ignoring blanks with highlighting

    Works great thanks again...
    I'm just wondering if when you run the macro to make the list, is there a way to modify the script to also copy the text and background colors?
    Again I want to thank you for a great script.
    Last edited by dlbDennis; 04-05-2020 at 09:34 AM.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: 7 column array unique list of alphabetized names ignoring blanks with highlighting

    Not sure who you are talking to.
    my code
    Replace "Private Sub Worksheet_Activate()" procedure with
    Please Login or Register  to view this content.
    And the rest should remain the same.

  7. #7
    Registered User
    Join Date
    04-28-2019
    Location
    Jenks, Oklahoma
    MS-Off Ver
    2010
    Posts
    5

    Re: 7 column array unique list of alphabetized names ignoring blanks with highlighting

    Your code doesn't seem to do anything. Rick Rothstein code above works but doesn't copy the colors..
    Thanks

  8. #8
    Registered User
    Join Date
    04-28-2019
    Location
    Jenks, Oklahoma
    MS-Off Ver
    2010
    Posts
    5

    Re: 7 column array unique list of alphabetized names ignoring blanks with highlighting

    Sorry Jindon, your code does work but when I click on a cell from the vba created list it hightlight the row and not just the cell.

  9. #9
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: 7 column array unique list of alphabetized names ignoring blanks with highlighting

    Quote Originally Posted by dlbDennis View Post
    Rick Rothstein code above works but doesn't copy the colors..
    I don't remember you mentioning colors in your original message. Given the cells and text in range A2:G25 are colored, I have a question... how are the cells that match the selection from Column J supposed to be highlighted? I mean, what color is to be used that will stand out against the existing conglomeration of colors in range A2:G25 (I am guessing that a yellow background might not be sufficient)?

  10. #10
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: 7 column array unique list of alphabetized names ignoring blanks with highlighting

    Hi dblDennis,

    My code from above allows you to double click on a name in column J and it will show that name in your range.
    DoubleClickExample.gif
    File is in the attached below so you can see it work and read the VBA code.
    VBA to Color Name in Col J Double Click.xlsm
    Last edited by MarvinP; 04-05-2020 at 01:38 PM.

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: 7 column array unique list of alphabetized names ignoring blanks with highlighting

    Quote Originally Posted by dlbDennis View Post
    I would like some help with using a 7 column array A2 through G25. Show a unique list of alphabetized names, ignoring blanks, in column J starting on row 1 and when you click on one of the names in Column J all matches are highlighted in the array.
    ................
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    04-28-2019
    Location
    Jenks, Oklahoma
    MS-Off Ver
    2010
    Posts
    5

    Re: 7 column array unique list of alphabetized names ignoring blanks with highlighting

    Thanks everyone for everything.

+ 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. Array formula to list top performers ignoring blanks
    By Shellybelly in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-05-2015, 10:27 AM
  2. Create a list that contains duplicates and unique names and no blanks
    By Whard42 in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 06-24-2015, 08:43 AM
  3. [SOLVED] Combine Two Columns of Names and Create Third Alphabetized Column
    By CWatsonJr in forum Excel Formulas & Functions
    Replies: 28
    Last Post: 05-05-2015, 05:49 PM
  4. [SOLVED] Formula for unique list alphabetized
    By NS4Excel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-03-2014, 06:30 PM
  5. [SOLVED] Looking to create a list of unique names that match criteria without using an array
    By john dalton in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 03-28-2014, 04:28 AM
  6. [SOLVED] Count number of unique names in a filtered list (non array)
    By IN_FOR_SIN in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 01-31-2014, 04:07 AM
  7. [SOLVED] Array formula, returning unique list of names w/ vlookup
    By BROWN_RY in forum Excel General
    Replies: 0
    Last Post: 03-29-2012, 04:31 PM

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