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>
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

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,656
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,089,558
Messages
5,408,943
Members
403,245
Latest member
Nanda Kishore

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top