Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-05-2020, 08:28 PM
Karen615 Karen615 is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 7 64bit Finding Duplicates & Unique Values in More Than One Sheet Office 2010 64bit
Advanced Beginner
Finding Duplicates & Unique Values in More Than One Sheet
 
Join Date: Jun 2011
Location: Chicago
Posts: 69
Karen615 is on a distinguished road
Default Finding Duplicates & Unique Values in More Than One Sheet

Using Excel 365



Please see attached.

I would like to know how to set up a formula/function in Sheet2, cell B2 that will search column A in Sheet1. If a company in Sheet1 matches a company in Sheet2, it will return TRUE or FALSE.

For Example-Sheet2:
Cell B2: FALSE
Cell B3: TRUE
Cell B4: FALSE

Your help is greatly appreciated.

Karen
Attached Files
File Type: xlsx Excel Sample.xlsx (10.4 KB, 4 views)
Reply With Quote
  #2  
Old 03-05-2020, 09:50 PM
Marcia's Avatar
Marcia Marcia is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 7 32bit Finding Duplicates & Unique Values in More Than One Sheet Office 2013
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 274
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

I am using my cellphone soI can't work on your attached sample. Try this in B2
=IF(COUNTIF(Sheet1!A$2:$A$14,A2)>0,"TRUE","FALSE")
Copy down.
Reply With Quote
  #3  
Old 03-06-2020, 12:13 AM
ArviLaanemets ArviLaanemets is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 8 Finding Duplicates & Unique Values in More Than One Sheet Office 2016
Expert
 
Join Date: May 2017
Posts: 564
ArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really nice
Default

Quote:
Originally Posted by Marcia View Post
=IF(COUNTIF(Sheet1!A$2:$A$14,A2)>0,"TRUE","FALSE")
IF() is not needed.
Code:
=COUNTIF(Sheet1!A$2:$A$14,$A2)>0
will do fine.
Reply With Quote
  #4  
Old 03-06-2020, 12:22 AM
Marcia's Avatar
Marcia Marcia is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 7 32bit Finding Duplicates & Unique Values in More Than One Sheet Office 2013
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 274
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Right Arvi. Thank you.
Reply With Quote
  #5  
Old 03-06-2020, 09:53 PM
Karen615 Karen615 is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 7 64bit Finding Duplicates & Unique Values in More Than One Sheet Office 2010 64bit
Advanced Beginner
Finding Duplicates & Unique Values in More Than One Sheet
 
Join Date: Jun 2011
Location: Chicago
Posts: 69
Karen615 is on a distinguished road
Default

Thank you both for your help!


Karen
Reply With Quote
  #6  
Old 03-06-2020, 10:28 PM
Karen615 Karen615 is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 7 64bit Finding Duplicates & Unique Values in More Than One Sheet Office 2010 64bit
Advanced Beginner
Finding Duplicates & Unique Values in More Than One Sheet
 
Join Date: Jun 2011
Location: Chicago
Posts: 69
Karen615 is on a distinguished road
Default

I was wondering if this would be possible.

I modified the file and re-uploaded a new file.

I added a "Yes / No" column in column F. If "Yes" is typed into cell F2, cell range A2:F2 gets highlighted with (let's say) a yellow fill.

I'm very curious to see if this is possible.

Your help is greatly appreciated.

Karen
Reply With Quote
  #7  
Old 03-06-2020, 11:10 PM
Marcia's Avatar
Marcia Marcia is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 7 32bit Finding Duplicates & Unique Values in More Than One Sheet Office 2013
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 274
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Yes Karen. A2:F2 could be highlighted with yellow through conditional formatting. Select A2:F2, in the ribbon of the Home tab, open Conditonal Formatting>Formula>type this formula in the box:
=($F2,"Yes")
Click Format>Fill>select yellow color>ok
Again, this is a cellphone so I am not sure if the formula works okay.
Edit: The formula should be:
=($F2="Yes")

Last edited by Marcia; 03-07-2020 at 07:00 AM.
Reply With Quote
  #8  
Old 03-07-2020, 10:57 AM
Karen615 Karen615 is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 7 64bit Finding Duplicates & Unique Values in More Than One Sheet Office 2010 64bit
Advanced Beginner
Finding Duplicates & Unique Values in More Than One Sheet
 
Join Date: Jun 2011
Location: Chicago
Posts: 69
Karen615 is on a distinguished road
Default

Thank you so much Marcia! This worked great!


So if I want to apply this conditional formatting to each subsequent row in column F if a user types in "Yes" to F2:F15, how would I apply it?


Thank you again for your help.


Karen
Reply With Quote
  #9  
Old 03-07-2020, 11:13 AM
Marcia's Avatar
Marcia Marcia is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 7 32bit Finding Duplicates & Unique Values in More Than One Sheet Office 2013
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 274
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Click conditional formatting>manage rules>in the apply to, change F2 to F15. Or, select A2:F15, then repeat the conditional formatting process.
Reply With Quote
  #10  
Old 03-07-2020, 05:47 PM
Karen615 Karen615 is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 7 64bit Finding Duplicates & Unique Values in More Than One Sheet Office 2010 64bit
Advanced Beginner
Finding Duplicates & Unique Values in More Than One Sheet
 
Join Date: Jun 2011
Location: Chicago
Posts: 69
Karen615 is on a distinguished road
Default

Thank you so much Marcia!
Reply With Quote
  #11  
Old 03-08-2020, 08:15 PM
Karen615 Karen615 is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 7 64bit Finding Duplicates & Unique Values in More Than One Sheet Office 2010 64bit
Advanced Beginner
Finding Duplicates & Unique Values in More Than One Sheet
 
Join Date: Jun 2011
Location: Chicago
Posts: 69
Karen615 is on a distinguished road
Default

How exactly does this formula work?
=COUNTIF(Sheet1!A$2:$A$14,$A2)>0
Reply With Quote
  #12  
Old 03-08-2020, 09:57 PM
Marcia's Avatar
Marcia Marcia is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 7 32bit Finding Duplicates & Unique Values in More Than One Sheet Office 2013
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 274
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
Originally Posted by Karen615 View Post
How exactly does this formula work?
=COUNTIF(Sheet1!A$2:$A$14,$A2)>0
Karen, google COUNTIF and you'll find excellent websites providing detailed explanations. I go direct to Exceljet.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding Duplicates & Unique Values in More Than One Sheet Loop through cells and return only unique values trevorc Excel Programming 4 10-11-2018 03:49 PM
Finding Duplicates & Unique Values in More Than One Sheet Sorting cells with duplicates values allex011 Excel 4 12-27-2017 12:31 PM
Finding Duplicates & Unique Values in More Than One Sheet Finding Duplicates with Multiple Criteria balajigade Excel 2 12-06-2015 10:16 PM
Finding Duplicates & Unique Values in More Than One Sheet Combining Records of two sheets with Loop till Unique ID abhilashv Mail Merge 1 01-22-2014 04:02 AM
If two geographical data match in two sheets, copy unique id/code found in one sheet alliage Excel 1 09-01-2011 05:23 AM

Other Forums: Access Forums - Senior Forums

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


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