Microsoft Office Forums Data Compare and result as true or false in result sheet

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-29-2019, 05:40 AM
aligahk06 aligahk06 is offline Data Compare and result as true or false in result sheet Windows XP Data Compare and result as true or false in result sheet Office 2007
Advanced Beginner
Data Compare and result as true or false in result sheet
 
Join Date: Aug 2009
Posts: 52
aligahk06 is on a distinguished road
Default Data Compare and result as true or false in result sheet


Dear All,
I have a sheet Mdata and Bdata and third sheet is result sheet.
I want to cross check data in Mdata sheet and data in Bdata sheet and result in result sheet as true or false in B4 and so on.
for ex. Mdata sheet value i.e A4, B3, B4 MATCH WITH BDATA SHEET THEN MATCH TRUE OR FALSE VALUE IN IN RESULT SHEET B4 AND SO ON.

Please find the attached sheet for ref.
Attached Files
File Type: xlsx TestFileData.xlsx (309.8 KB, 1 views)
Reply With Quote
  #2  
Old 08-29-2019, 06:44 AM
NBVC's Avatar
NBVC NBVC is offline Data Compare and result as true or false in result sheet Windows 10 Data Compare and result as true or false in result sheet Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 206
NBVC will become famous soon enough
Default

In order to avoid array formulas, first add a helper column to BData to combine the employee ID and date.... so in H2 enter formula:


=A2&"_"&TRIM(TEXT(B2,"dd/mm/yyyy"))


copied down.


Then in Result B4 enter formula:


=IFERROR(TRIM(INDEX(Bdata!$E:$E,MATCH($A4&"_"&TEXT (B$3,"dd/mm/yyyy"),Bdata!$H:$H,0)))=Mdata!B4,"")



Copied down and across the table


I added an IFERROR() to return a blank if the match is not found. You can replace the "" with FALSE if you want the word False returned instead.



Note: Careful when entering data that you don't have leading/trailing spaces. I've had to use the TRIM() function to remove them virtually.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Compare and result as true or false in result sheet One Cell that controlls spread sheet result button to change simple fomula result RAH Excel Programming 5 03-31-2018 04:52 PM
Data Compare and result as true or false in result sheet Converting a 1 or 0 into TRUE or FALSE? ayupchap Mail Merge 1 04-25-2017 02:45 PM
Data Compare and result as true or false in result sheet How To Move Focus From VLOOKUP Result to Report Sheet swindon.expat Excel 2 04-10-2016 12:15 AM
Data Compare and result as true or false in result sheet Mail merge result in separate world files instead of one file containing all the data xdhbsh Mail Merge 3 12-24-2015 12:23 AM
Lookup multiple values and compare different scenarios to get a specific result mws Excel 5 05-24-2014 04:52 AM


All times are GMT -7. The time now is 03:18 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft