Solved Use a drop-down list to search a matrix and return values

January 25, 2018 at 05:58:15
Specs: Windows 10
This is a training exercise I set myself but which I've failed miserably so am keen to know the answer.

The Goal
The goal is to create a skills log where you can use a drop down list to select a skill. When you make your selection, a search will be carried out to find all the people who have that skill and list them next to the drop down list.

Data Structure
Say Sheet 1 has a drop down list of the available skills in cell B2. Sheet 2 would have all the skills found in the drop-down list such as "Excel", "Interviewing" etc in column headers B1:I1. The people that hold skills are listed in cells A2:A5. For each skill a person holds they enter a yes in the relevant cell.

So let's say John is in A2 and Sarah is in A3. Also let's say Excel is in B1 and Interviewing is in C1. If John is good at Excel but doesn't interview he'll have "Yes" in cell B2 and C2 will be blank. If Sarah has the opposite skills she'd be blank B3 and have "Yes" in C3.

Thoughts on a Solution
I was thinking I'd need to create a variable called "Skill" for the drop-down list selection on Sheet 1. The selection would trigger a find on Sheet 2 for "Skill" . I'd then loop through each row to see where the interaction with the column that has the same header as "Skill" to see if that cell = "Yes". If it does, copy the corresponding value from column A and paste it on Sheet 1 starting from cell D2 and working downwards for each subsequent person.

I've poured through lots of forums trying to work out how to realize that strategy but have just got myself tied up in knots.

If anyone has some time and would care to set me in the right direction I'd be very grateful. I've learnt more in the last few weeks than in the last few years. It's great fun! :-)

Thanks

message edited by ScottV


See More: Use a drop-down list to search a matrix and return values

Report •

✔ Best Answer
April 16, 2018 at 11:16:35
The answer to "is there any point in removing the hard coding for the search range" would depend on how elegant/efficient you want to be.

As written, you have a Data Validation list using a hard coded range that goes out to Column O.

The first issue: If you don't have data all the way out to O1, you will have blank lines at the bottom your Drop-Down list. That's not very elegant and is dangerous because a user could select a blank item.

The second issue: If you ever decide to add more items to the B1:O1 list (beyond Column O) then you'd need to edit both the data validation rule as well as the code. (Even if you never plan to do that for this workbook, it's good to understand the "fix" outlined below.)

Both of those issues could be resolved by using a Dynamic Named Range.

1 - Use the Name Manager to create a Dynamic Named Range
--- On the Formulas ribbon, click Name Manager
--- Click New...
--- In the Name: field enter a Name for the range e.g. Skills
--- In the Refers To: field enter: =OFFSET(Sheet1!$B$1,0,0,1,COUNTA(Sheet1!$1:$1)-1)

This will create a Named Range (Skills) that will expand and contract based how many pieces of data (Skills) you have in B1:XFD1. Who knows, you might need Mentors for 16,383 Skills some day. ;-)

2 - For your Data Validation Drop Down, in the Source: field, enter =Skills
--- Your drop down will now expand and contract based on the Skills listed in B1:XFD1. Just make sure that you don't have any blank cells within the list of Skills. The Skills list must be a contiguous list starting in B1.

3 - In your code, use this, which will always refer to the Dynamic Named Range

'Find "Mentor Data" Column with Drop Down choice
   With Data.Range("Skills")
     Set S = .Find(Target, lookat:=xlWhole)
   End With

Now you can add or delete "Skills" with no need to edit the Data Validation source field or your code. The Data Validation drop down will always be nice and neat.

One last point: The range name (Skills) will not appear in the Name Box above Column A. You can select the range by typing Skills into that field, but you won't be able to choose it from the list of Names that would normally appear there. That's just how it is with Dynamic Named Ranges.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code



#1
January 25, 2018 at 09:51:07
I started with this in Sheet2:

	
        A         B             C              ...       I
1               Excel	   Interviewing
2   John	 Yes	
3   Sarah		       Yes
4   Bob	         Yes	       Yes

I put this code in the Sheet1 module:


Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if Drop Down was used
 If Target.Address = "$B$2" Then

'Find Sheet2 Column with Drop Down choice
   With Sheets(2).Range("$B$1:I$1")
     Set s = .Find(Target, lookat:=xlWhole)
   End With

'Initiate Destination Column counter (D)
   nxtCol = 4
  
'Loop through Skill column, looking for Yes
   With Sheets(2).Columns(s.Column)
     Set y = .Find("Yes", LookIn:=xlValues, lookat:=xlWhole, after:=Cells(Rows.Count, s.Column))
    
'If yes, copy Name to Sheet 1
       If Not y Is Nothing Then
          firstAddress = y.Address
            Do
              Sheets(2).Cells(y.Row, 1).Copy (Cells(Target.Row, nxtCol))

'Increment Destination Column counter then Fnd next Yes
               nxtCol = nxtCol + 1
               Set y = .FindNext(y)
            Loop While Not y Is Nothing And y.Address <> firstAddress
       End If
   End With
 End If
End Sub

When I choose Excel from the Sheet1!B2 Drop Down, I get this:

	
        A        B        C       D      D     
1
2              Excel	        John    Bob 

Let me know if that is what you are looking for.

BTW...You said:

"For each skill a person holds they enter a yes in the relevant cell."

I would not let the user enter yes. I would use a Drop Down and have them choose Yes. Leading/Trailing spaces, a typo (yees), an enthusiastic user (YES!) can all cause the .Find function to fail. Forcing the user to choose Yes from Drop Down, even if it's the only choice, ensures that the exact string that the code is looking for is in the cell.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
January 26, 2018 at 04:03:58
Hi DerbyDad03

That's great. I take your point about restricting user's data entry. If I use this in the way I'm thinking, I'll survey people and input the survey results myself so it shouldn't be an issue.

You've achieved what I was thinking except that I intended to paste the results vertically into column D starting from D2.

I've played around with the code and done some further reading and debugging etc and managed to achieve everything! I've changed the column counter to a row counter; (finally) managed to get the data to paste into D2 - it was going into D22 initially!

I also realised that I'd need to clear the pasted returns if a new selection was made and took care of that - after initially not seeing any results and realising it was clearing contents at the end of each loop.

Anyway, I'm so pleased at what I've achieved, but couldn't have done any of that without your 'leg up'.

Thank you again. Here's the code I ended up with:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim SearchScr As Worksheet
Dim Data As Worksheet

Set SearchScr = Worksheets("Search Screen")
Set Data = Worksheets("Data")


'Determine if Drop Down was used and if so _
 Clear contents of column D
 If Target.Address = "$B$2" Then
 Worksheets("Search Screen").Columns(4).ClearContents

'Find "Data" Column with Drop Down choice
   With Data.Range("$B$1:I$1")
     Set s = .Find(Target, lookat:=xlWhole)
   End With

'Initiate Destination Row counter
   nxtRow = 2
  
'Loop through Skill column, looking for Yes
   With Data.Columns(s.Column)
     Set y = .Find("Yes", LookIn:=xlValues, lookat:=xlWhole, after:=Cells(Rows.Count, s.Column))
    
'If yes, copy Name to "Search Screen"
       If Not y Is Nothing Then
          firstAddress = y.Address
            Do
              Data.Cells(y.Row, 1).Copy SearchScr.Range("D" & nxtRow)

'Increment Destination Row counter then Fnd next Yes
               nxtRow = nxtRow + 1
               Set y = .FindNext(y)
            Loop While Not y Is Nothing And y.Address <> firstAddress
       End If
   End With
 End If
End Sub


Report •

#3
January 26, 2018 at 06:45:03

Report •

Related Solutions

#4
April 10, 2018 at 07:30:01
Hi DerbyDad03

I've just found a bug with my code and can't work out how to fix it. I've cleared the best answer on this thread so it comes to your attention. Apologies if that's not the proper way to do things. I wasn't sure if I was supposed to ask a new question with a link to this thread or not?

Anyway, what I've found is that if I cycle through the values in the drop down list in turn, the previous pasted returns are cleared properly before the next selection's values are pasted. However, if I jump around in the drop down list clicking on different skills randomly then the previous returns are not cleared completely each time. I should add that this manifests when going from a larger number of returns to a smaller number, e.g. If I go from Excel which has 4 mentors to interviewing which has 2. What happens is the two mentors for interviewing will still be displayed, but one or more names from the Excel returns aren't cleared and remain in the list.

This is what it should look like:

When Excel is selected

John
Bob
Freda
Susan

When Interviewing is selected

Bob
Freda

When the problem occurs though, what I tend to get for the next randomly selected skill with fewer names is something like

Bob
Freda
Susan

So Susan is appearing, when her name should have been cleared by the bit of the code

If Target.Address = "$B$2" Then
 Worksheets("Search Screen").Columns(4).ClearContents

I hope that makes sense? Any ideas?


Report •

#5
April 10, 2018 at 08:32:40
Have you Single Stepped (F8) though the code and looked at the spreadsheet as each instruction is executed?

If the Worksheets("Search Screen").Columns(4).ClearContents instruction is being executed and Column D is empty before the code moves on, then something else is causing the extraneous names to be put back into Column D.

I would Single Step through the code and use the Watch Window where appropriate to keep an eye on the relevant variables to see what is happening.

If you haven't reviewed the Debugging techniques described in the following tutorial, perhaps that would help.

https://www.computing.net/howtos/sh...

message edited by DerbyDad03


Report •

#6
April 11, 2018 at 03:30:20
I have tried single stepping through - though not sure I'm doing this effectively. While watching the worksheet change as I step through, the names don't get fully cleared after the code

If Target.Address = "$B$2" Then
 Worksheets("Search Screen").Columns(4).ClearContents

The strange thing I've found is there are two separate, but presumably related issues:

One - is that all along I had four test names in column A on the "Data" worksheet and everything worked perfectly. However, as soon as I increase the number of names in that column, the problem manifests, whether I add one extra name, or ten etc. I can't see anything in the code above that would cause that.

Two - if I delete some of the "Yes" values entered on the "Data" worksheet in columns B:I it causes similar issues (it's fine if I add "Yes" values to what was stored originally though).


Report •

#7
April 11, 2018 at 04:01:44
Since I don't have a copy of the workbook to test your code against, it's difficult for me to determine where the problem lies.

First off, I don't see how the .ClearContents instruction isn't fully clearing Column 4. It's a simple instruction, unrelated to anything else, so to not clear the entire column makes no sense.

In addition, the example of the problem that you posted is confusing. In the output for Excel, Susan is in the 4th position. (I am assume that means D4, but since you didn't include Column letters or Row numbers, I can't be sure)

In the example output for Interviewing, Susan is in the 3rd position.

Even if the .ClearContents instruction wasn't clearing Susan from Column D, I don't see how Susan moved up one position. My initial sought, based only on the output examples that you posted, is that the column was cleared and the code subsequently placed Susan in the 3rd position. That's why I suggested Single Stepping so that you could see what caused Susan to end up in the output for Interviewing.

Without a copy of your workbook, I don't know what further assistance I can provide.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#8
April 11, 2018 at 04:08:06
Ok. That's understandable. Sorry for being a bit dumb but what's the best way of getting a copy to you? I can't see any obvious means on the site?

I'll keep testing as well to see if I can find the issue.


Report •

#9
April 11, 2018 at 07:39:45
re: "I have tried single stepping through - though not sure I'm doing this effectively."

The way I would perform the Single Step process is as follows:

1 - Open the VBA editor to the code window
2 - Click anywhere within the instruction Worksheets("Search Screen").Columns(4).ClearContents
3 - Insert a Break Point by pressing F9 or clicking in the grey bar next to the instruction

When you make a change to B2, the VBA editor will pop up, that instruction will be highlighted and the code will wait for you to take action.

4 - Set up your windows so that you can see the VBA editor and your spreadsheet side-by-side.
5 - Press F8 to single-step through the code. The first time you press F8, Column D on Sheet1 (Search Screen) should be cleared.
6 - Continuing to press F8 should execute each instruction one at a time.

If you think it will help, or perhaps just as a learning experience, highlight a variable, e.g. s, and drag it down to the Watch window. When the Set s = .Find(Target, lookat:=xlWhole) instruction is executed, you should see the value of s appear in the Watch window,

Single Stepping while watching both the workbook as well as your variables can often reveal where the problems with the code lie.

Check your Private Message Inbox for more info.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#10
April 12, 2018 at 02:56:02
Thanks for all your help DerbyDad03!

Following all your help and advice I misunderstood what xlDown meant.
I thought that the line

SearchScr.Range("C14").End(xlDown).Clear

would clear everything from C14 to the bottom of the range, but you've educated me
that it just clears the bottom-most value.

Anyway, for the benefit of anyone else that might find this code useful, here it is in its
corrected entirety. Keen eyes may note that I've added some lines to cope with
locking cells and protecting the worksheet from the fumblings of people who don't
(or should) know better:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim SearchScr As Worksheet
Dim Data As Worksheet

Set SearchScr = Worksheets("Mentor Search")
Set Data = Worksheets("Mentor Data")

'Determine if Drop Down was used and if so _
 Unlock the sheet and clear skill holders from any previous search
 If Target.Address = "$C$10" Then
 SearchScr.Unprotect Password:="xx"
 SearchScr.Range("C14:C59").Locked = False
 SearchScr.Range("C14:" & Range("C14").End(xlDown).Address).ClearContents


'Find "Mentor Data" Column with Drop Down choice
   With Data.Range("$B$1:O$1")
     Set S = .Find(Target, lookat:=xlWhole)
   End With

'Initiate Destination Row counter to paste from row 14 onwards
   nxtRow = 14
  
'Loop through Skill column, looking for all "Yes" entries
   With Data.Columns(S.Column)
     Set y = .Find("Yes", LookIn:=xlValues, lookat:=xlWhole, after:=Cells(Rows.Count, S.Column))
    
'If "Yes", copy person's Name to "Mentor Search"
       If Not y Is Nothing Then
          firstAddress = y.Address
            Do
              Data.Cells(y.Row, 1).Copy SearchScr.Range("C" & nxtRow)
              

              
'Increment Destination Row counter then Find next "Yes"
               nxtRow = nxtRow + 1
               Set y = .FindNext(y)
            Loop While Not y Is Nothing And y.Address <> firstAddress
            
'After pasting all names re-lock the sheet
            SearchScr.Range("C14:C59").Locked = True
            SearchScr.Protect Password:="xx"
            
       End If
   End With
 End If
End Sub

Thanks a million!


Report •

#11
April 12, 2018 at 08:19:16
I'm glad that you got it working.

A couple of comments:

1 - Regarding the locking/unlocking of the cells within VBA

SearchScr.Unprotect Password:="xx"
SearchScr.Range("C14:C59").Locked = False 'Not Needed
...
...
SearchScr.Range("C14:C59").Locked = True 'Not Needed
SearchScr.Protect Password:="xx"

Cells only become Locked when the sheet is Protected. That means that you can Lock the cells once (i.e. manually within Excel) and then just use the Unprotect - Protect instructions in VBA. There is no need to Lock or Unlock the cells with VBA. Once the Unprotect instruction is executed, the cells are no longer Locked. They are "formatted" as Locked, but since they are not Protected, they are not actually Locked.

2 - Depending on your trust in your user base, you should consider password protecting your VBA code and hiding it. If you put the Protection password in the code and leave the code viewable, then users will know the password. They can Unprotect the workbook as well as mess with the code. See here for more info:

https://www.youtube.com/watch?v=9dy...

message edited by DerbyDad03


Report •

#12
April 12, 2018 at 08:32:26
One more item:

I noticed that you hard-coded some Ranges into the code: e.g. SearchScr.Range("C14:C59")

I understand the desire to leave room for more data at a later date, but hard-coding range addresses can cause issues later on since you may want to use those cells for something else or may want to expand that range for more data.

I prefer to let VBA determine the range to work on whenever possible, similar to how you are using VBA to determine which cells to clear: SearchScr.Range("C14:" & Range("C14").End(xlDown)

Named Ranges also work, especially if you use a Dynamic Named Range, which automatically changes it's size based on how much data is in the range:

https://support.microsoft.com/en-us...

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#13
April 16, 2018 at 08:26:00
Thanks a lot. Again.... :-)

I've replaced the hard coded ranges in my code so that VBA now determines where the end addresses are.

Well, all except, I was thinking to do the same for the code that searches for the drop down choice in the Mentor Data sheet. The issue with that though is that the drop down list is populated using data validation whereby I'm expressly stating where all the drop down values are located, so is there any point in removing the hard coding for the search range below?

'Find "Mentor Data" Column with Drop Down choice
   With Data.Range("$B$1:O$1")
     Set S = .Find(Target, lookat:=xlWhole)
   End With


Report •

#14
April 16, 2018 at 11:16:35
✔ Best Answer
The answer to "is there any point in removing the hard coding for the search range" would depend on how elegant/efficient you want to be.

As written, you have a Data Validation list using a hard coded range that goes out to Column O.

The first issue: If you don't have data all the way out to O1, you will have blank lines at the bottom your Drop-Down list. That's not very elegant and is dangerous because a user could select a blank item.

The second issue: If you ever decide to add more items to the B1:O1 list (beyond Column O) then you'd need to edit both the data validation rule as well as the code. (Even if you never plan to do that for this workbook, it's good to understand the "fix" outlined below.)

Both of those issues could be resolved by using a Dynamic Named Range.

1 - Use the Name Manager to create a Dynamic Named Range
--- On the Formulas ribbon, click Name Manager
--- Click New...
--- In the Name: field enter a Name for the range e.g. Skills
--- In the Refers To: field enter: =OFFSET(Sheet1!$B$1,0,0,1,COUNTA(Sheet1!$1:$1)-1)

This will create a Named Range (Skills) that will expand and contract based how many pieces of data (Skills) you have in B1:XFD1. Who knows, you might need Mentors for 16,383 Skills some day. ;-)

2 - For your Data Validation Drop Down, in the Source: field, enter =Skills
--- Your drop down will now expand and contract based on the Skills listed in B1:XFD1. Just make sure that you don't have any blank cells within the list of Skills. The Skills list must be a contiguous list starting in B1.

3 - In your code, use this, which will always refer to the Dynamic Named Range

'Find "Mentor Data" Column with Drop Down choice
   With Data.Range("Skills")
     Set S = .Find(Target, lookat:=xlWhole)
   End With

Now you can add or delete "Skills" with no need to edit the Data Validation source field or your code. The Data Validation drop down will always be nice and neat.

One last point: The range name (Skills) will not appear in the Name Box above Column A. You can select the range by typing Skills into that field, but you won't be able to choose it from the list of Names that would normally appear there. That's just how it is with Dynamic Named Ranges.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#15
April 17, 2018 at 01:12:17
:-D You know what's funny? I used to consider myself quite good with Excel until I met you! HAHA :-)

It works like a charm. I had some questions, but played around with the formula and answered them all.

I'm changing the best answer to this last one because, well, I love it!

Thank you


Report •

Ask Question