Monday, August 19, 2013

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


( 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.


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:
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: 


No comments:

Post a Comment