Filter multiple sheets based on 3 criteria and return results into single cell

Hi All,

I'm wondering if someone might be able to help me with a VBA script to populate the 'yellow' cells in the attached document.
Basically the script would search through the 5 sheets in the workbook and return all results in a single cell based on 3 criteria without duplicates.
To further complicate the matter, although the criteria is consistent, the columns are not. All gray cells are hard coded including the gray cells in the 'Result' tab.
Any help here would really be appreciated. I've had some success formulating this in excel, but it appears to be so slow it almost hangs up which is not practical. Each sheet will contain approximately 1,000 lines.
Thank you in advance.

See if this is how you wanted.

Please Login or Register  to view this content.

Thank you so much for this Jindon, you certainly are a wizard!

The only thing is that Columns A, B and C are hard coded on the results sheet and the VBA should only populate columns D and E if a match is found. If no match is found then the cell would just be blank.
Everything else is 100% perfect.

Thank you for your help

OK, that would be much simpler.

Im out at the momdnt, so I will post when I come back in about 3,4 hrs.

Thanks so much Jindon,
Also I did just notice that the column headers in the results sheet don't align with the data, but that issue should go away I imagine when you repost based on the hard coding of columns A, B and C. Column headers not required to be generated.

Thank you

Try this one.

Please Login or Register  to view this content.

I'm just go out again, so next reply will be late if needed.

Everything works perfect Jindon. Thanks for all your help.

Hi All,

Sorry for reopening the thread here, but I noticed that I lacked some detail in the source file that i provided and now the script requires some editing.

After running the above script, i noticed that the the VBA is clearing the gray formulated reference cells in the 'results sheet' (columns A, B and C) each time i run it. Can I retain these formulas?
Also there are two result columns that are populated by the macro in the results sheet. This script will only populate a column when both columns have a value. There will be times where only one field will have a result. Is it possible to make it so that the two columns are not interdependent of each other?

Thank you

Originally Posted by matt2877

After running the above script, i noticed that the the VBA is clearing the gray formulated reference cells in the 'results sheet' (columns A, B and C) each time i run it. Can I retain these formulas?

Please Login or Register  to view this content.

I don't understand the rest.
It should update col.C & D regardless of they are filled or blank.

So good, perfect every time. Thanks Jindon, this thread is now solved.

Sorry, I have one more query here that i'd like to ask.
After the script has been run, it changes my hard coded column headings on 'row 1' in the 'results' worksheet from 'Operator' and 'Location' to 'Date' and 'Shift'.
Thanks.

Try change to

Please Login or Register  to view this content.

It works as intended, thank you.