Create a query/filter in excel

[losdamianos]

Hi there!

Im quite new to some of more advanced excel techniques.
What Im trying to accomplish is to create a query table which will combine 2 columns from all 7 previous worksheets and show only fields where there is something ( I don’t want all NULL columns to be shown).And I want to place this new column in TOTAL worksheet

Basically I have weekly Workbooks which have 7 days and totals worksheets.
On each day I have list of about 20 employers, next column represent how many hours they should do each day and we have few other columns which represent specific tasks like Holiday/Sick/Pick/Replenish/FLT etc I also have column called OTHER this is for some unusual tasks and then I have COMMENT column next to it which is showing what exactly this person have been doing if was putted into OTHER column.

If it would be acces I would just create a query from all 7 days containing OTHER and COMMENT column and then in criteria I would put “<> NULL” is there any way to do it in Excell ?

\1

I will apriciate any help even if its just showing which formula and expression to use.

Thanks

[simjambra]

Hi there, you will probably get a better reply as I tend to do things the long way but maybe an advanced filter?

Set up a criteria table with <> as your criteria for both fields as shown on the example attached.

Go to your TOTALS sheet

Go to data > filter > advanced filter

List range is your main data table, criteria is your small table on the right. Make sure Copy to another location is selected and click into the top left most cell in your TOTALS sheet.

Run the filter.

Any good?

[MarvinP]

Many times these problems are much easier if all the data is on the same worksheet. If you were to copy the data to a worksheet named Totals and insert a column of Date (if needed) it might make your problem a lot more doable.

[losdamianos]

Thanks guys for the reply
I've copied/linked cells from all 5 days in TOTAL worksheet and created a table with 2 columns, Comment and HOURS, then I've applied custom filter (does not contain zero) and It worked ! Now its much faster to see what other things pple been doing over the week. There is also option to filter for specific task which is working like a charm.
Problem solved, now Im going deeper into PIVOT TABLES