Excel: Search with multiple criteria with multiple results

[hebbebe]

I think the header says the most. But i'm kind of new in excel and need a bit of help. I'm trying to make a search-function for my projectlibary. I have a multiple cells(A10:H10) that will be the criterias: Projectnumber, Engineer... Material, House type, building part.. and so on. See picture 1.

And i have a sheet(Sheet1) where my projectlibary is(A22:H8000). And you add projects to the libary with the same criteras. What i want is that, if i search for a Material: Steel, i will get all the projects that contain steel. And if i search for both Material: Steel and Buildingpart: Roof, i will get projects that contain both of them... and so on.

Maybe a bad explanation, and i will answer questions quickly as i can.

 

[vcoolio]

Hello Hebbebe,

Advanced Filter should do the task for you:-

Sub Test()

    If WorksheetFunction.CountA(Sheet2.Range("A2:H2")) = 0 Then Exit Sub

    Sheet2.Range("A3", Range("H" & Rows.Count).End(xlUp)).ClearContents
    Sheet1.Range("A1", Sheet1.Range("H" & Sheet1.Rows.Count).End(xlUp)).AdvancedFilter 2, Sheet2.[A1:H2], [A3]
    
End Sub

I've attached a sample workbook just to show you how it would work. The sample is based on data starting in sheet1,Row2 with headings in Row1.
The search is done on Sheet2 which has drop down lists in A2, B2, C2 and D2 from which a selection is made. You can of course use more than one criteria to search on.

Sample Workbook

I hope that this helps.

Cheerio,
vcoolio.

 

[hebbebe]

Hello Hebbebe,

Advanced Filter should do the task for you:-

Sub Test()

    If WorksheetFunction.CountA(Sheet2.Range("A2:H2")) = 0 Then Exit Sub

    Sheet2.Range("A3", Range("H" & Rows.Count).End(xlUp)).ClearContents
    Sheet1.Range("A1", Sheet1.Range("H" & Sheet1.Rows.Count).End(xlUp)).AdvancedFilter 2, Sheet2.[A1:H2], [A3]
   
End Sub

I've attached a sample workbook just to show you how it would work. The sample is based on data starting in sheet1,Row2 with headings in Row1.
The search is done on Sheet2 which has drop down lists in A2, B2, C2 and D2 from which a selection is made. You can of course use more than one criteria to search on.

Sample Workbook

I hope that this helps.

Cheerio,
vcoolio.

That worked perfectly! Thank you.

 

[vcoolio]

You're welcome Hebbebe. I'm glad to have been able to assist.

Cheerio,
vcoolio.