How can I compare the data from one worksheet and extract True/False results ????

[Coletw]

Hello,

I have two large workbooks, I need to compare both workbooks and if the names and dates match say, "True" and if the names and dates don't match say "False". Do I need to transpose the names so that they match first? One workbook says, "Tom Smith" and the other workbook says, "Smith, Tom"
I need to know did the employee work when he purchased items from the cafeteria.

For example:
Book 1 show employees hrs and date worked.

Book1
Date Worked	Steamship Worker	Ship Id #	Ship name	Earning code	Total hours
8/4/2013	Tom Smith	ABC-123	New York	REG	15.00
8/4/2013	Tom Smith	ABC-123	New York	VAC	8.00
8/4/2013	Tom Smith	ABC-123	New York	SIC	8.00
8/4/2013	Bill Jones	DEF-456	Colorado	REG	10.00
8/4/2013	Robert Dell	DEF-456	Colorado	REG	12.00
8/4/2013	Robert Dell	DEF-456	Colorado	REG	11.00
8/4/2013	Steven Johns	GHI-789	Maryland	VAC	8.00
8/4/2013	Steven Johns	GHI-789	Maryland	VAC	8.00
8/4/2013	Mike Thoms	JKL-012	Georgia	SIC	10.00
8/4/2013	Mike Thoms	JKL-012	Georgia	REG	10.00

<colgroup><col style="width: 48pt;" width="64"> <col style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;" width="88"> <col style="width: 48pt;" width="64"> <col style="width: 112pt; mso-width-source: userset; mso-width-alt: 5449;" width="149"> <col style="width: 102pt; mso-width-source: userset; mso-width-alt: 4973;" width="136"> <col style="width: 98pt; mso-width-source: userset; mso-width-alt: 4790;" width="131"> <tbody>
</tbody>

Book 2:
Book show show the cafeteria purchases on different days. I want it to say, "True" if the employee worked on that day or false if the employee had a cafertia purchase but didn't work on that day.

On PTO	Data #	Data Tool #	Charged By	Cafeteria Purchas #	Lunch Amt	Transaction Date
	1234	9876	Smith, Tom	1470	20.00	8/4/13 11:21 AM
	1234	9876	Blast, Bill	2580	50.00	8/1/13 7:31 AM
	1234	6543	Jackson, Michael	3698	25.00	8/1/13 8:27 AM
	1234	6543	Dell, Robert	9632	35.00	8/4/13 10:39 AM
	1234	2100	Cher, Steven	8521	12.50	8/2/13 1:36 PM
	1234	2100	Thoms, Mike	7413	36.00	8/4/13 7:24 AM
	1234	5420	Jones, Bill	5421	11.20	8/4/13 12:24 PM

<colgroup><col style="width: 48pt;" width="64"> <col style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;" width="88"> <col style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;" width="82"> <col style="width: 112pt; mso-width-source: userset; mso-width-alt: 5449;" width="149"> <col style="width: 102pt; mso-width-source: userset; mso-width-alt: 4973;" width="136"> <col style="width: 98pt; mso-width-source: userset; mso-width-alt: 4790;" width="131"> <col style="width: 88pt; mso-width-source: userset; mso-width-alt: 4278;" width="117"> <tbody>
</tbody>

<colgroup><col style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;" width="88"> <tbody>
</tbody>

 

[cyrilbrd]

Given in Book1 A1:

Date Worked	Steamship Worker	Ship Id #	Ship name	Earning code	Total hours	Helper_Column
8/4/13	Tom Smith	ABC-123	New York	REG	15	Smith, Tom
8/4/13	Tom Smith	ABC-123	New York	VAC	8	Smith, Tom
8/4/13	Tom Smith	ABC-123	New York	SIC	8	Smith, Tom
8/4/13	Bill Jones	DEF-456	Colorado	REG	10	Jones, Bill
8/4/13	Robert Dell	DEF-456	Colorado	REG	12	Dell, Robert
8/4/13	Robert Dell	DEF-456	Colorado	REG	11	Dell, Robert
8/4/13	Steven Johns	GHI-789	Maryland	VAC	8	Johns, Steven
8/4/13	Steven Johns	GHI-789	Maryland	VAC	8	Johns, Steven
8/4/13	Mike Thoms	JKL-012	Georgia	SIC	10	Thoms, Mike
8/4/13	Mike Thoms	JKL-012	Georgia	REG	10	Thoms, Mike

<colgroup><col span="6"><col></colgroup><tbody>
</tbody>

Add one Helper Column in G with the following formula: =TRIM(MID(SUBSTITUTE($B2," ",REPT(" ",999)),COLUMN(B2)*999-998,999))&", "&TRIM(MID(SUBSTITUTE($B2," ",REPT(" ",999)),COLUMN(A2)*999-998,999

Then Book2 A1

On PTO	Data #	Data Tool #	Charged By	Cafeteria Purchas #	Lunch Amt	Transaction Date	Validity
	1234	9876	Smith, Tom	1470	20	8/4/13 11:21	TRUE
	1234	9876	Blast, Bill	2580	50	8/1/13 7:31	FALSE
	1234	6543	Jackson, Michael	3698	25	8/1/13 8:27	FALSE
	1234	6543	Dell, Robert	9632	35	8/4/13 10:39	TRUE
	1234	2100	Cher, Steven	8521	12.5	8/2/13 13:36	FALSE
	1234	2100	Thoms, Mike	7413	36	8/4/13 7:24	TRUE
	1234	5420	Jones, Bill	5421	11.2	8/4/13 12:24	TRUE

<colgroup><col span="6"><col><col></colgroup><tbody>
</tbody>

Formula in Column H would be =ISNUMBER(IFERROR(MATCH(D20,$G$2:$G$11,0),"FALSE"))

Adjust name of file to fit your query.

Would that work for you?