Need a VBA code to identify suitable Solution which based on Multiple Inputs from User

[JimCorb]

Hello Champions!
Thanks for being there. I've learnt a lot by reading your forums earlier.

Now I got a situation & I couldn't come across similar thread on this. By using VBA I've to capture User requirements using a form where I plan to use combo boxes etc. There may be around 15-20 criteria / requirements (some may have further sub-attributes) for user to provide their inputs. On my side I've different product offerings (example building names) which could be 20-30 choices as of now but could expand later.

Once user submits the form there should be a Recommendation for the Building Name (from the available options) which matches the user's multiple choices / criteria.
Wherever none of the available solution matches the available criteria I can prompt the user "sorry no match found"

For illustration I've listed down the Criteria or attributes in Column A. While the available Optionsor Building names as headers in columns B onwards..

I'm not too expert in VBA and my problem is how to use multiple choices to shortlist suitable building by using either.. I tried to think about using Vlookup OR Nested If, Elseif, Select option but then I'm not an expert in programming and am new to VBA ... so I couldn't think of suitable loop structure.

I'll be grateful for any suggestions or help please..



Excel 2010

	A	B	C	D	E	F	G	H	I
1	Building Offer / Criteria	Building1	Building2	Building3	Building4	Building5	Building6	Building…	Building20
2	Minimum Price (US$)	$2,000	$3,000	$4,000	$5,000	$1,500	$2,500	$3,500	$4,000
3	Maximum Price (US$)	$3,800	$4,800	$5,800	$6,800	$3,300	$4,300	$5,300	$5,800
4	Currency (USD, GBP, EUR, CNY)	USD	GBP	USD	EUR	CNY	USD	USD	USD
5	Risk Appetite (Lo/Med/Hi/NA)
6	Inclusion {Multiple Selections }( eg.. High Floor, Sea Facing, Security, Private Lawn, NA)	High Floor, Sea Facing	Sea Facing, Security	Security, Private Lawn	High Floor, Sea Facing	Sea Facing, Security		Security, Private Lawn	High Floor, Sea Facing
7	Exclusion {Multiple Selections } ( eg..Mountain Facing, No Lift, Security)	Mountain Facing	No Lift, Security	Mountain Facing	No Lift, Security	No Lift, Security	Mountain Facing	Mountain Facing	No Lift, Security
8	Sr Citizen Discount (Y/N)	Y	Y	Y	N	N	N	N	N
9	Protected Rental (Y/N)	Y	N	N	Y	Y	N	N	N
10	Minimum Contract (1,2,3,4 Yrs)	1	2	3	3	4	2	1	1
11	Security Deposit (20%, 30%, 40% depending on No. of Contract years)	20%	40%	30%	20%	30%	30%	30%	30%
12	Distance from Mall	1	2	NA	1	NA	3	NA	2
13	Station Proximity (Y/N)	Y	N	N	Y	Y	N	N	Y
14	Bus Stop Proximity	N	Y	Y	Y	N	Y	N	N
15	School Proximity	Y	N	Y	Y	Y	N	Y	N
16	Cinema Proximity (Y/N/NA)	NA	Y	N	NA	NA	N	N	N
17	Hospital (<1 Km, 1-3 KM, >5KM)	1-3 KM	1 KM	>5KM	1 KM	>5KM	1-3 KM	1 KM	>5KM

<tbody>
</tbody>

Sheet1​

 

[NdNoviceHlp]

Welcome to the Board! U show 17 different criteria, the chance of any single building matching all of the 17 preferences are slim/none. Do U really want to match up with the building that matches the most criteria? Are U going to use a userform for user input? How do new buildings get added to the list? Good luck. Dave

 

[JimCorb]

Welcome to the Board! U show 17 different criteria, the chance of any single building matching all of the 17 preferences are slim/none. Do U really want to match up with the building that matches the most criteria? Are U going to use a userform for user input? How do new buildings get added to the list? Good luck. Dave

Hi Dave! Thanks for your comments. Yes , I plan to use user form with mostly all known parameters through dropbox selections (trying to avoid free text inputs). I was assuming that there may be multiple buildings emerge through recommendations.
If not the perfect match then is it possible to show top 3-5 recommendations which matches most of the criteria ? For example recommendation 1 with say 15 matches, recommendation 2 with 12 matches and so on.
All the buildings are not visible to users as the excel I posted will act as our master data sheet. I can add the new building as it becomes available in the master sheet . VBA code is suppose to parse all the columns till last filled column.
That's my thought structure to manage it. If there's some better approach I'll be glad to know as it's still the beginning.
Thanks again! JC

 

[NdNoviceHlp]

Hi JC. U can trial something like this. Using your data setup, create a userform and put 3 listboxes and a command button on it. Place this code in the userform code. HTH. Dave

Option Explicit
Private Sub UserForm_Initialize()
'load all listboxes from "B" to last column
'*load value only once
Dim LastCol As Double, LastRow As Double
Dim Cnt1 As Integer, Cnt2 As Integer
With Sheets("Sheet1")
LastCol = .Cells(1, .Cells.Columns.Count).End(xlToLeft).Column()
LastRow = .Cells(.Cells.Rows.Count, "A").End(xlUp).Row()
End With
'load listbox1 with row 2 values
For Cnt1 = 2 To LastCol
For Cnt2 = (Cnt1 + 1) To LastCol
If Sheets("Sheet1").Cells(2, Cnt2).Value = _
          Sheets("Sheet1").Cells(2, Cnt1).Value Then
GoTo bart
End If
Next Cnt2
UserForm1.ListBox1.AddItem Sheets("Sheet1").Cells(2, Cnt1).Value
bart:
Next Cnt1
'load listbox2 with row 3 values
For Cnt1 = 2 To LastCol
For Cnt2 = (Cnt1 + 1) To LastCol
If Sheets("Sheet1").Cells(3, Cnt2).Value = _
         Sheets("Sheet1").Cells(3, Cnt1).Value Then
GoTo bart2
End If
Next Cnt2
UserForm1.ListBox2.AddItem Sheets("Sheet1").Cells(3, Cnt1).Value
bart2:
Next Cnt1
'load listbox3 with row 4 values
For Cnt1 = 2 To LastCol
For Cnt2 = (Cnt1 + 1) To LastCol
If Sheets("Sheet1").Cells(4, Cnt2).Value = _
         Sheets("Sheet1").Cells(4, Cnt1).Value Then
GoTo bart3
End If
Next Cnt2
UserForm1.ListBox3.AddItem Sheets("Sheet1").Cells(4, Cnt1).Value
bart3:
Next Cnt1
'load remaining listboxes etc...
'clear previous entries
For Cnt1 = 2 To LastCol
For Cnt2 = 2 To LastRow
Sheets("Sheet1").Cells(Cnt2, Cnt1).Interior.Color = vbWhite 'white
Sheets("Sheet1").Cells(Cnt2, Cnt1).Borders.LineStyle = xlContinuous
Next Cnt2
Next Cnt1
End Sub

Private Sub ListBox1_Click()
Dim LastCol As Double, Cnt As Integer
With Sheets("Sheet1")
LastCol = .Cells(1, .Cells.Columns.Count).End(xlToLeft).Column()
For Cnt = 2 To LastCol
If CStr(.Cells(2, Cnt)) = _
       UserForm1.ListBox1.List(UserForm1.ListBox1.ListIndex) Then
.Cells(2, Cnt).Interior.Color = vbCyan 'blue
Else
.Cells(2, Cnt).Interior.Color = vbWhite 'white
End If
.Cells(2, Cnt).Borders.LineStyle = xlContinuous
Next Cnt
End With
End Sub

Private Sub ListBox2_Click()
Dim LastCol As Double, Cnt As Integer
With Sheets("Sheet1")
LastCol = .Cells(1, .Cells.Columns.Count).End(xlToLeft).Column()
For Cnt = 2 To LastCol
If CStr(.Cells(3, Cnt)) = _
       UserForm1.ListBox2.List(UserForm1.ListBox2.ListIndex) Then
.Cells(3, Cnt).Interior.Color = vbCyan 'blue
Else
.Cells(3, Cnt).Interior.Color = vbWhite 'white
End If
.Cells(3, Cnt).Borders.LineStyle = xlContinuous
Next Cnt
End With
End Sub

Private Sub ListBox3_Click()
Dim LastCol As Double, Cnt As Integer
With Sheets("Sheet1")
LastCol = .Cells(1, .Cells.Columns.Count).End(xlToLeft).Column()
For Cnt = 2 To LastCol
If CStr(.Cells(4, Cnt)) = _
       UserForm1.ListBox3.List(UserForm1.ListBox3.ListIndex) Then
.Cells(4, Cnt).Interior.Color = vbCyan 'blue
Else
.Cells(4, Cnt).Interior.Color = vbWhite 'white
End If
.Cells(4, Cnt).Borders.LineStyle = xlContinuous
Next Cnt
End With
End Sub

Private Sub CommandButton1_Click()
'output answer
Dim LastRow As Double, LastCol As Double, Total As Double
Dim Cnt1 As Integer, Cnt2 As Integer
With Sheets("Sheet1")
LastRow = .Cells(.Cells.Rows.Count, "A").End(xlUp).Row()
LastCol = .Cells(1, .Cells.Columns.Count).End(xlToLeft).Column()
End With
For Cnt1 = 2 To LastCol
Total = 0
For Cnt2 = 2 To LastRow
If Sheets("Sheet1").Cells(Cnt2, Cnt1).Interior.Color = vbCyan Then 'blue
Total = Total + 1
End If
Next Cnt2
'use this output to determine Top 3
MsgBox Sheets("Sheet1").Cells(1, Cnt1).Value & _
                        " has " & Total & " criteria met."
Next Cnt1
End Sub

ps. Of course U will have to create more listboxes and code for all of the rest of your criteria. The output can be adjusted to just display the top 3.

 

[JimCorb]

This is really interesting Dave. Thanks for highlighting another great perspective to approach this problem.

Your method also looks good for populating various attributes or features of different buildings. Hope I'm getting it right. Earlier I thought of defining them as named range in master sheet which will remain hidden and will contain all available buildings along with attribute details.

I've a further query in my mind. How do I manage multiple choices within one attribute? For example under 'Inclusion' preference (row6), user can request for combination of features like Sea Facing + Security and/or High Floor etc...
Thanks again! BR, JC

 

[NdNoviceHlp]

You could just change the listbox to a multiselect instead of single select. The listbox click code is a bit different but the rest of the code should work the same. Changing the cell colors is just 1 way to identify the selections made so that they can later be tallied for your total criteria met... it's not necessary to display the sheet. U should really have mentioned that multiple choices could be made... it's better to know the whole picture before coding starts. Dave