Google Spreadsheet Comparing multiple columns and cells for a true or false output

Question:

( by Damon Hainline )

I have a document that is populated with data from a online form. My employees use it to report daily numbers. 

Their names are in Column B and the date is in Column C of Sheet 1. 

I have created a table on Sheet 2 with dates in Row 1 and Names of my employees in Column A.

I want a formula that will display TRUE in the cell if the employee filed a report on the corresponding day.

For example, Cell B2 in Sheet 2 would be "True" if there was a match for A2 in Column B of Sheet 1 and B1 in Column C in the same row of Sheet 1.

Solution:

Have a look at the following screenshot of "Sheet1":

Have a look at the following screenshot of "2013 Summary":

in the above sheet, I have the following formulas in Cell A1:

=arrayformula(regexreplace(text(query(if({1,0,0};1;'Sheet1'!A:C);"select Col2,count(Col1) where Col2<>'' group by Col2 pivot Col3";1);"");"^[0-9]{1,}$";"TRUE"))

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive