Creating complex search function

geperi

Board Regular
Joined
Apr 19, 2017
Messages
78
Hi,

I have the following data in a sheet, and I am trying to create an easy to use search box in Excel that can give me the output I need.

The reason I need a search function is that I have more than 50 people doing tasks, and the excel has more than 50 columns. A nice search function would make things much easier.

Input: the tasks (there are no duplicates)
Output 1: Date
Output 2: Person

Is there a nice way to build a search function in Excel, where I can input the name of the task (maybe from a drop-down) and then I get the output 1 and 2 to appear automatically on separate cells?

Thank you!
Person 1Person 2 P3P4P5Etc..
Date 1Date 2date 3Date 4Date 5
Task 1Task AAAABCF
Task 2Task BBBBACG
Task 3Task CCCCAGH
Task 4DDAKL
Task 5EEA

<tbody>
</tbody>
 

NickRed18

Board Regular
Joined
May 25, 2017
Messages
76
The tasks you are looking up (Input), could you be looking up Task 1, Task 2, Task 3, Task 4, Task 5, Task A, Task B, Task C, etc.... just every task you listed? Or would you be looking up say Task 1, and then for the Person # you want it will output the first task for that person?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,099
Office Version
2013
Platform
Windows
This is event code. It assumes your data matrix begins in row 3, column A. It also assumes cell A1 as the input cell and cells B1:B2 as the output cells. To use the code once the data has been configured as above, right click the sheet name tab, then click 'View Code' in the pop up menu. Copy and paste the code into the large code pane of the vb editor. Close the editor and save the workbook as a macro enabled workbook to preserve the code. The code should now run when you make an entry into range A1 of the sheet and should return data from rows 3 and 4 of the data matrix for the column where a match is found for the value in cell A1.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo Hndl:
Dim fn As Range, rng As Range, lr As Long, lc As Long
If Not Intersect(Target, Range("A1")) Is Nothing Then
    With ActiveSheet
        lr = .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
        lc = .Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious).Column
        Set rng = .Range("A3", .Cells(lr, lc))
        Set fn = rng.Find(Range("A1").Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                .Range("B1") = .Cells(3, fn.Column).Value
                .Range("B2") = .Cells(4, fn.Column).Value
            End If
    End With
End If
Hndl:
Application.EnableEvents = True
If Err.Number <> 0 Then
    MsgBox Err.Number & ":  " & Err.Description
    On Error GoTo 0
End If
End Sub
 
Last edited:

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
Here's one option:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Person 1</td><td style=";">Person 2</td><td style=";">Person 3</td><td style=";">Person 4</td><td style=";">Person 5</td><td style="text-align: right;;"></td><td style=";">Task (Input)</td><td style=";">Task BB</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Date 1</td><td style=";">Date 2</td><td style=";">Date 3</td><td style=";">Date 4</td><td style=";">Date 5</td><td style="text-align: right;;"></td><td style=";">Date (Output)</td><td style=";">Date 3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Task 1</td><td style=";">Task A</td><td style=";">Task AA</td><td style=";">Task AB</td><td style=";">Task CF</td><td style="text-align: right;;"></td><td style=";">Person (Output)</td><td style=";">Person 3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Task 2</td><td style=";">Task B</td><td style=";">Task BB</td><td style=";">Task BA</td><td style=";">Task CG</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Task 3</td><td style=";">Task C</td><td style=";">Task CC</td><td style=";">Task CA</td><td style=";">Task GH</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Task 4</td><td style=";">Task D</td><td style="text-align: right;;"></td><td style=";">Task DA</td><td style=";">Task KL</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Task 5</td><td style=";">Task E</td><td style="text-align: right;;"></td><td style=";">Task EA</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H2</th><td style="text-align:left">{=INDEX(<font color="Blue">A2:E2,SUMPRODUCT(<font color="Red">MAX(<font color="Green">(<font color="Purple">A3:E7=H1</font>)*(<font color="Purple">COLUMN(<font color="Teal">A3:E7</font>)</font>)</font>)</font>)-COLUMN(<font color="Red">A3</font>)+1</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H3</th><td style="text-align:left">{=INDEX(<font color="Blue">A1:E1,SUMPRODUCT(<font color="Red">MAX(<font color="Green">(<font color="Purple">A3:E7=H1</font>)*(<font color="Purple">COLUMN(<font color="Teal">A3:E7</font>)</font>)</font>)</font>)-COLUMN(<font color="Red">A3</font>)+1</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

geperi

Board Regular
Joined
Apr 19, 2017
Messages
78
This is event code. It assumes your data matrix begins in row 3, column A. It also assumes cell A1 as the input cell and cells B1:B2 as the output cells. To use the code once the data has been configured as above, right click the sheet name tab, then click 'View Code' in the pop up menu. Copy and paste the code into the large code pane of the vb editor. Close the editor and save the workbook as a macro enabled workbook to preserve the code. The code should now run when you make an entry into range A1 of the sheet and should return data from rows 3 and 4 of the data matrix for the column where a match is found for the value in cell A1.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo Hndl:
Dim fn As Range, rng As Range, lr As Long, lc As Long
If Not Intersect(Target, Range("A1")) Is Nothing Then
    With ActiveSheet
        lr = .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
        lc = .Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious).Column
        Set rng = .Range("A3", .Cells(lr, lc))
        Set fn = rng.Find(Range("A1").Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                .Range("B1") = .Cells(3, fn.Column).Value
                .Range("B2") = .Cells(4, fn.Column).Value
            End If
    End With
End If
Hndl:
Application.EnableEvents = True
If Err.Number <> 0 Then
    MsgBox Err.Number & ":  " & Err.Description
    On Error GoTo 0
End If
End Sub
Hi,

I used the exact code and set up the data in the page starting with A3, and tried to copy paste one task in A1 but nothing at all showed up in B1:B2..
 

geperi

Board Regular
Joined
Apr 19, 2017
Messages
78
Hi all,

I thought about this a bit more, and I think it makes most sense if I go for a drop down functionality. Do you think this is do-able?

Input: Drop down 1: List of all dates
Input: Drop down 2: Based on the date chosen it will only show the Tasks associated with that specific date
Output: Text box: Person responsible
 

geperi

Board Regular
Joined
Apr 19, 2017
Messages
78
Hi all,

I thought about this a bit more, and I think it makes most sense if I go for a drop down functionality. Do you think this is do-able?

Input: Drop down 1: List of all dates
Input: Drop down 2: Based on the date chosen it will only show the Tasks associated with that specific date
Output: Text box: Person responsible
Hi friends,

Is anyone able to help with a solution for my previous set-up above?

Would greatly appreciate it!
 

Forum statistics

Threads
1,078,134
Messages
5,338,430
Members
399,232
Latest member
stevenmoritz

Some videos you may like

This Week's Hot Topics

Top