Search and results of information in Excel Spreadsheet

[RaviJobs]

Hi

I have a spreadsheet with property information - cell A5 = "date"; cell B5 = "Address"; cell C5 = "Details" and cell D5 = "Value"

I would be really grateful if anyone can tell me how I can set up a search function that will enable me to list all rows containing the criteria I set - e.g. in the "Address" sell, I will have property postcodes such as HA0 3DH, HA0 4EE; HA9 3YD; HA3 5PU; UB1 2QW; NW8 7NG; W1X 3BY and so on... I want to be able to find all the properties with postcode starting "HA" and result to be listed.

The other thing I want to have is the result to be listed in date order - most recent at the top..

If there is anyone who can help mw with this, I will be eternally grateful.

Ravi

 

[DanteAmor]

Where do you want the result?
It would have to be done with a macro to perform the filter and sort the data automatically.

 

[RaviJobs]

Where do you want the result?
It would have to be done with a macro to perform the filter and sort the data automatically.

Hi Dante,

Thanks so much for your interest and support!

The result can be anywhere convenient; same spreadsheet or another sheet on the workbook etc.

Ravi

 

[DanteAmor]

Put your data from cell A6-D6 down. Put the criteria in cell L6.
Execute the macro, the result will be in columns F to I, as shown:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:49.43px;" /><col style="width:81.74px;" /><col style="width:53.23px;" /><col style="width:47.52px;" /><col style="width:18.06px;" /><col style="width:65.58px;" /><col style="width:88.4px;" /><col style="width:53.23px;" /><col style="width:57.03px;" /><col style="width:26.61px;" /><col style="width:37.07px;" /><col style="width:69.39px;" /><col style="width:53.23px;" /><col style="width:44.67px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#ffff00; ">DATE</td><td style="background-color:#ffff00; ">ADDRESS</td><td style="background-color:#ffff00; ">DETAILS</td><td style="background-color:#ffff00; ">VALUE</td><td > </td><td style="background-color:#95b3d7; ">DATE</td><td style="background-color:#95b3d7; ">ADDRESS</td><td style="background-color:#95b3d7; ">DETAILS</td><td style="background-color:#95b3d7; ">VALUE</td><td > </td><td >DATE</td><td style="background-color:#92d050; ">ADDRESS</td><td >DETAILS</td><td >VALUE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">25-may</td><td >HA0 3DH</td><td >det1</td><td style="text-align:right; ">123</td><td > </td><td style="text-align:right; ">25-may</td><td >HA0 3DH</td><td >det1</td><td style="text-align:right; ">123</td><td > </td><td > </td><td >HA</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">26-may</td><td >HA0 4EE</td><td >det2</td><td style="text-align:right; ">124</td><td > </td><td style="text-align:right; ">26-may</td><td >HA0 4EE</td><td >det2</td><td style="text-align:right; ">124</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">27-may</td><td >HA9 3YD</td><td >det3</td><td style="text-align:right; ">125</td><td > </td><td style="text-align:right; ">27-may</td><td >HA9 3YD</td><td >det3</td><td style="text-align:right; ">125</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">28-may</td><td >HA3 5PU</td><td >det4</td><td style="text-align:right; ">126</td><td > </td><td style="text-align:right; ">28-may</td><td >HA3 5PU</td><td >det4</td><td style="text-align:right; ">126</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">29-may</td><td >UB1 2QW</td><td >det5</td><td style="text-align:right; ">127</td><td > </td><td style="text-align:right; ">01-jun</td><td >HA0 3DH</td><td >det8</td><td style="text-align:right; ">130</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">30-may</td><td >NW8 7NG</td><td >det6</td><td style="text-align:right; ">128</td><td > </td><td style="text-align:right; ">02-jun</td><td >HA0 4EE</td><td >det9</td><td style="text-align:right; ">131</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">31-may</td><td >W1X 3BY</td><td >det7</td><td style="text-align:right; ">129</td><td > </td><td style="text-align:right; ">03-jun</td><td >HA9 3YD</td><td >det10</td><td style="text-align:right; ">132</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">01-jun</td><td >HA0 3DH</td><td >det8</td><td style="text-align:right; ">130</td><td > </td><td style="text-align:right; ">04-jun</td><td >HA3 5PU</td><td >det11</td><td style="text-align:right; ">133</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">02-jun</td><td >HA0 4EE</td><td >det9</td><td style="text-align:right; ">131</td><td > </td><td style="text-align:right; ">08-jun</td><td >HA0 3DH</td><td >det15</td><td style="text-align:right; ">137</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">03-jun</td><td >HA9 3YD</td><td >det10</td><td style="text-align:right; ">132</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">04-jun</td><td >HA3 5PU</td><td >det11</td><td style="text-align:right; ">133</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">05-jun</td><td >UB1 2QW</td><td >det12</td><td style="text-align:right; ">134</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">06-jun</td><td >NW8 7NG</td><td >det13</td><td style="text-align:right; ">135</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">07-jun</td><td >W1X 3BY</td><td >det14</td><td style="text-align:right; ">136</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">08-jun</td><td >HA0 3DH</td><td >det15</td><td style="text-align:right; ">137</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table>

---

Sub Macro6()
    Dim lr As Long
    
    lr = Range("B" & Rows.Count).End(xlUp).Row
    Range("K5:N5").Value = Range("A5:D5").Value
    Range("A5:D" & lr).AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("K5:N6"), CopyToRange:=Range("F5"), Unique:=False
End Sub

The macro is executed with an advanced filter, in columns K to N you can set other criteria.
Check examples:
https://www.excel-easy.com/examples/advanced-filter.html