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.[TABLE="width: 474"]
<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>[TR]
[TD="width: 64, bgcolor: transparent"]Book1[/TD]
[TD="width: 88, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 149, bgcolor: transparent"][/TD]
[TD="width: 136, bgcolor: transparent"][/TD]
[TD="width: 131, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Date Worked[/TD]
[TD="class: xl64, bgcolor: transparent"]Steamship Worker[/TD]
[TD="class: xl64, bgcolor: transparent"]Ship Id #[/TD]
[TD="class: xl64, bgcolor: transparent"]Ship name[/TD]
[TD="class: xl64, bgcolor: transparent"]Earning code[/TD]
[TD="class: xl65, bgcolor: transparent"]Total hours[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]8/4/2013[/TD]
[TD="class: xl64, bgcolor: transparent"]Tom Smith[/TD]
[TD="class: xl64, bgcolor: transparent"]ABC-123[/TD]
[TD="class: xl64, bgcolor: transparent"]New York[/TD]
[TD="class: xl64, bgcolor: transparent"]REG[/TD]
[TD="class: xl65, bgcolor: transparent"]15.00[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]8/4/2013[/TD]
[TD="class: xl64, bgcolor: transparent"]Tom Smith[/TD]
[TD="class: xl64, bgcolor: transparent"]ABC-123[/TD]
[TD="class: xl64, bgcolor: transparent"]New York[/TD]
[TD="class: xl64, bgcolor: transparent"]VAC[/TD]
[TD="class: xl65, bgcolor: transparent"]8.00[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]8/4/2013[/TD]
[TD="class: xl64, bgcolor: transparent"]Tom Smith[/TD]
[TD="class: xl64, bgcolor: transparent"]ABC-123[/TD]
[TD="class: xl64, bgcolor: transparent"]New York[/TD]
[TD="class: xl64, bgcolor: transparent"]SIC[/TD]
[TD="class: xl65, bgcolor: transparent"]8.00[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]8/4/2013[/TD]
[TD="class: xl64, bgcolor: transparent"]Bill Jones[/TD]
[TD="class: xl64, bgcolor: transparent"]DEF-456[/TD]
[TD="class: xl64, bgcolor: transparent"]Colorado[/TD]
[TD="class: xl64, bgcolor: transparent"]REG[/TD]
[TD="class: xl65, bgcolor: transparent"]10.00[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]8/4/2013[/TD]
[TD="class: xl64, bgcolor: transparent"]Robert Dell[/TD]
[TD="class: xl64, bgcolor: transparent"]DEF-456[/TD]
[TD="class: xl64, bgcolor: transparent"]Colorado[/TD]
[TD="class: xl64, bgcolor: transparent"]REG[/TD]
[TD="class: xl65, bgcolor: transparent"]12.00[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]8/4/2013[/TD]
[TD="class: xl64, bgcolor: transparent"]Robert Dell[/TD]
[TD="class: xl64, bgcolor: transparent"]DEF-456[/TD]
[TD="class: xl64, bgcolor: transparent"]Colorado[/TD]
[TD="class: xl64, bgcolor: transparent"]REG[/TD]
[TD="class: xl65, bgcolor: transparent"]11.00[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]8/4/2013[/TD]
[TD="class: xl64, bgcolor: transparent"]Steven Johns[/TD]
[TD="class: xl64, bgcolor: transparent"]GHI-789[/TD]
[TD="class: xl64, bgcolor: transparent"]Maryland[/TD]
[TD="class: xl64, bgcolor: transparent"]VAC[/TD]
[TD="class: xl65, bgcolor: transparent"]8.00[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]8/4/2013[/TD]
[TD="class: xl64, bgcolor: transparent"]Steven Johns[/TD]
[TD="class: xl64, bgcolor: transparent"]GHI-789[/TD]
[TD="class: xl64, bgcolor: transparent"]Maryland[/TD]
[TD="class: xl64, bgcolor: transparent"]VAC[/TD]
[TD="class: xl65, bgcolor: transparent"]8.00[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]8/4/2013[/TD]
[TD="class: xl64, bgcolor: transparent"]Mike Thoms[/TD]
[TD="class: xl64, bgcolor: transparent"]JKL-012[/TD]
[TD="class: xl64, bgcolor: transparent"]Georgia[/TD]
[TD="class: xl64, bgcolor: transparent"]SIC[/TD]
[TD="class: xl65, bgcolor: transparent"]10.00[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]8/4/2013[/TD]
[TD="class: xl64, bgcolor: transparent"]Mike Thoms[/TD]
[TD="class: xl64, bgcolor: transparent"]JKL-012[/TD]
[TD="class: xl64, bgcolor: transparent"]Georgia[/TD]
[TD="class: xl64, bgcolor: transparent"]REG[/TD]
[TD="class: xl65, bgcolor: transparent"]10.00[/TD]
[/TR]
</tbody>[/TABLE]


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.


[TABLE="width: 576"]
<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>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]On PTO[/TD]
[TD="class: xl67, width: 88, bgcolor: transparent"]Data #[/TD]
[TD="class: xl67, width: 82, bgcolor: transparent"]Data Tool #[/TD]
[TD="class: xl67, width: 149, bgcolor: transparent"]Charged By[/TD]
[TD="class: xl67, width: 136, bgcolor: transparent"]Cafeteria Purchas #[/TD]
[TD="class: xl67, width: 131, bgcolor: transparent"]Lunch Amt[/TD]
[TD="class: xl68, width: 117, bgcolor: transparent"]Transaction Date[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]1234[/TD]
[TD="class: xl65, bgcolor: transparent"]9876[/TD]
[TD="bgcolor: transparent"]Smith, Tom[/TD]
[TD="bgcolor: transparent, align: right"]1470[/TD]
[TD="class: xl70, bgcolor: transparent"] 20.00 [/TD]
[TD="class: xl69, width: 117, bgcolor: transparent"]8/4/13 11:21 AM[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]1234[/TD]
[TD="class: xl65, bgcolor: transparent"]9876[/TD]
[TD="bgcolor: transparent"]Blast, Bill[/TD]
[TD="bgcolor: transparent, align: right"]2580[/TD]
[TD="class: xl70, bgcolor: transparent"] 50.00 [/TD]
[TD="class: xl69, width: 117, bgcolor: transparent"]8/1/13 7:31 AM[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]1234[/TD]
[TD="class: xl65, bgcolor: transparent"]6543[/TD]
[TD="bgcolor: transparent"]Jackson, Michael[/TD]
[TD="bgcolor: transparent, align: right"]3698[/TD]
[TD="class: xl70, bgcolor: transparent"] 25.00 [/TD]
[TD="class: xl69, width: 117, bgcolor: transparent"]8/1/13 8:27 AM[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]1234[/TD]
[TD="class: xl65, bgcolor: transparent"]6543[/TD]
[TD="bgcolor: transparent"]Dell, Robert[/TD]
[TD="bgcolor: transparent, align: right"]9632[/TD]
[TD="class: xl70, bgcolor: transparent"] 35.00 [/TD]
[TD="class: xl69, width: 117, bgcolor: transparent"]8/4/13 10:39 AM[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]1234[/TD]
[TD="class: xl65, bgcolor: transparent"]2100[/TD]
[TD="bgcolor: transparent"]Cher, Steven[/TD]
[TD="bgcolor: transparent, align: right"]8521[/TD]
[TD="class: xl70, bgcolor: transparent"] 12.50 [/TD]
[TD="class: xl69, width: 117, bgcolor: transparent"]8/2/13 1:36 PM[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]1234[/TD]
[TD="class: xl65, bgcolor: transparent"]2100[/TD]
[TD="bgcolor: transparent"]Thoms, Mike[/TD]
[TD="bgcolor: transparent, align: right"]7413[/TD]
[TD="class: xl70, bgcolor: transparent"] 36.00 [/TD]
[TD="class: xl69, width: 117, bgcolor: transparent"]8/4/13 7:24 AM[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]1234[/TD]
[TD="class: xl65, bgcolor: transparent"]5420[/TD]
[TD="bgcolor: transparent"]Jones, Bill[/TD]
[TD="bgcolor: transparent, align: right"]5421[/TD]
[TD="class: xl70, bgcolor: transparent"] 11.20 [/TD]
[TD="class: xl69, width: 117, bgcolor: transparent"]8/4/13 12:24 PM[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 66"]
<colgroup><col style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;" width="88"> <tbody>[TR]
[TD="width: 88, bgcolor: transparent, align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[cyrilbrd]

Given in Book1 A1:
[TABLE="width: 645"]
<colgroup><col span="6"><col></colgroup><tbody>[TR]
[TD]Date Worked[/TD]
[TD]Steamship Worker[/TD]
[TD]Ship Id #[/TD]
[TD]Ship name[/TD]
[TD]Earning code[/TD]
[TD]Total hours[/TD]
[TD]Helper_Column[/TD]
[/TR]
[TR]
[TD]8/4/13[/TD]
[TD]Tom Smith[/TD]
[TD]ABC-123[/TD]
[TD]New York[/TD]
[TD]REG[/TD]
[TD]15[/TD]
[TD]Smith, Tom[/TD]
[/TR]
[TR]
[TD]8/4/13[/TD]
[TD]Tom Smith[/TD]
[TD]ABC-123[/TD]
[TD]New York[/TD]
[TD]VAC[/TD]
[TD]8[/TD]
[TD]Smith, Tom[/TD]
[/TR]
[TR]
[TD]8/4/13[/TD]
[TD]Tom Smith[/TD]
[TD]ABC-123[/TD]
[TD]New York[/TD]
[TD]SIC[/TD]
[TD]8[/TD]
[TD]Smith, Tom[/TD]
[/TR]
[TR]
[TD]8/4/13[/TD]
[TD]Bill Jones[/TD]
[TD]DEF-456[/TD]
[TD]Colorado[/TD]
[TD]REG[/TD]
[TD]10[/TD]
[TD]Jones, Bill[/TD]
[/TR]
[TR]
[TD]8/4/13[/TD]
[TD]Robert Dell[/TD]
[TD]DEF-456[/TD]
[TD]Colorado[/TD]
[TD]REG[/TD]
[TD]12[/TD]
[TD]Dell, Robert[/TD]
[/TR]
[TR]
[TD]8/4/13[/TD]
[TD]Robert Dell[/TD]
[TD]DEF-456[/TD]
[TD]Colorado[/TD]
[TD]REG[/TD]
[TD]11[/TD]
[TD]Dell, Robert[/TD]
[/TR]
[TR]
[TD]8/4/13[/TD]
[TD]Steven Johns[/TD]
[TD]GHI-789[/TD]
[TD]Maryland[/TD]
[TD]VAC[/TD]
[TD]8[/TD]
[TD]Johns, Steven[/TD]
[/TR]
[TR]
[TD]8/4/13[/TD]
[TD]Steven Johns[/TD]
[TD]GHI-789[/TD]
[TD]Maryland[/TD]
[TD]VAC[/TD]
[TD]8[/TD]
[TD]Johns, Steven[/TD]
[/TR]
[TR]
[TD]8/4/13[/TD]
[TD]Mike Thoms[/TD]
[TD]JKL-012[/TD]
[TD]Georgia[/TD]
[TD]SIC[/TD]
[TD]10[/TD]
[TD]Thoms, Mike[/TD]
[/TR]
[TR]
[TD]8/4/13[/TD]
[TD]Mike Thoms[/TD]
[TD]JKL-012[/TD]
[TD]Georgia[/TD]
[TD]REG[/TD]
[TD]10[/TD]
[TD]Thoms, Mike[/TD]
[/TR]
</tbody>[/TABLE]

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
[TABLE="width: 732"]
<colgroup><col span="6"><col><col></colgroup><tbody>[TR]
[TD]On PTO[/TD]
[TD]Data #[/TD]
[TD]Data Tool #[/TD]
[TD]Charged By[/TD]
[TD]Cafeteria Purchas #[/TD]
[TD]Lunch Amt[/TD]
[TD]Transaction Date[/TD]
[TD]Validity[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1234[/TD]
[TD]9876[/TD]
[TD]Smith, Tom[/TD]
[TD]1470[/TD]
[TD]20[/TD]
[TD]8/4/13 11:21[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1234[/TD]
[TD]9876[/TD]
[TD]Blast, Bill[/TD]
[TD]2580[/TD]
[TD]50[/TD]
[TD]8/1/13 7:31[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1234[/TD]
[TD]6543[/TD]
[TD]Jackson, Michael[/TD]
[TD]3698[/TD]
[TD]25[/TD]
[TD]8/1/13 8:27[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1234[/TD]
[TD]6543[/TD]
[TD]Dell, Robert[/TD]
[TD]9632[/TD]
[TD]35[/TD]
[TD]8/4/13 10:39[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1234[/TD]
[TD]2100[/TD]
[TD]Cher, Steven[/TD]
[TD]8521[/TD]
[TD]12.5[/TD]
[TD]8/2/13 13:36[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1234[/TD]
[TD]2100[/TD]
[TD]Thoms, Mike[/TD]
[TD]7413[/TD]
[TD]36[/TD]
[TD]8/4/13 7:24[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1234[/TD]
[TD]5420[/TD]
[TD]Jones, Bill[/TD]
[TD]5421[/TD]
[TD]11.2[/TD]
[TD]8/4/13 12:24[/TD]
[TD]TRUE[/TD]
[/TR]
</tbody>[/TABLE]

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?